数据的分组汇总.sql

来自「简单的sql语句 数据与汇总。 高手多多指点。」· SQL 代码 · 共 52 行

use northwind
go

select productid,sum(quantity) as [total_quantity] from [order details]
group by productid
order by productid
go

select * from [order details]
go

select productid,sum(quantity) as [total_quantity] from [order details]
where productid=2
group by productid
go



select productid,quantity from [order details]
where productid=2
go

select productid,sum(quantity) from [order details]
group by productid
having productid>=60
go

--group相当于where，select和where的关系相当于group by和having的关系

select productid,sum(quantity) from (select productid,quantity from  [order details]
                                     where quantity>=60) as T                       
                                                
group by productid
go

select productid,orderid,sum(quantity) as [total_quantity] from [order details]
group by productid,orderid
with rollup
go 

select productid,orderid,quantity from [order details]
order by productid,orderid  --必须有这行，不然compute by 不起作用
compute sum(quantity) by productid
compute sum(quantity)
go

select productid,sum(quantity) as [total_quantity] from [order details]
group by productid
order by productid
go