数据分组与汇总.sql

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

use northwind
select * from orders
select top 220 with ties orderid,employeeid,customerid from orders     
order by employeeid 
select * from employees
select count(*) from employees
select * from products
select count(*) from products
select count(productname) from products

select 
'num productid'=count(*),
'max supplierid'=max(supplierid),
'min supplierid'=min(supplierid),
'avg unitprice'=avg(unitprice)
from products

select unitprice from products

select 
count(*) as 'num productid',
max(supplierid) as 'max supplierid',
min(supplierid) as 'min supplierid',
avg(unitprice) as 'avg unitprice' 
from products

select * from [Order Details]USE Northwind

SELECT OrderID AS ProdID,SUM(Quantity)AS AmountSold
FROM [Order Details] 
where OrderID in(10248,10249,10251,10255)
GROUP BY OrderIDs

select * from [Order Details]

select orderid,sum(quantity)as [sum quantity] from  [Order Details]
group by orderid
having orderid in(10248,10249,10250) and sum(quantity)>27

select * from [Order Details]
where orderid in (11077,11076)
order by orderid,productid

select productid,sum(quantity) as total from [Order Details]
group by productid
having sum(quantity)>1100

select orderid,quantity,sum(unitprice)as total from [Order Details]
where orderid in (11077,11076)
group by orderid,quantity
with rollup
order by orderid,quantity

select orderid,quantity,sum(unitprice)as total from [Order Details]
where orderid in (11077,11076)
group by orderid,quantity
with cube
order by orderid,quantity

select orderid,quantity,unitprice from [Order Details]
where orderid in (11077,11076)
order by orderid,quantity
compute sum(unitprice) by  orderid