--多表查询和笛卡儿乘积
use northwind
go
select * from region
select * from territories
select regiondescription,territorydescription
from region,territories--笛卡儿乘积
select regiondescription,territorydescription
from region,territories where region.regionid = territories.regionid
--如果有N个表格出现在from的后面，那么，所定义的连接条件不得少于N-1个
select regiondescription,territorydescription
from region join territories on region.regionid = territories.regionid
--join用于连接两个不同的表格
--on用于给出这两个表格之间的连接条件
--使用表格别名
select * from suppliers
select * from products
select * from categories
select p.productname,c.categoryName,s.companyname
from products p, categories c,suppliers s
where p.supplierid = s.supplierid and p.categoryid = c.categoryid
--使用union子句
select * from employees
select * from customers
select * from suppliers
select lastname+'.'+firstname from employees union
select companyname from customers union
select companyname from suppliers
order by lastname+'.'+firstname--必须是第一个结果集
--多个不同的查询结构数据集链接在一起，组成一组数据
--每一个结果集的数据类型都必须相同
--每一个结果集中列的数量都必须相等
--union自动删除重复的行 可以使用all
--使用group by子句
select * from products
select categoryid,unitprice,sum(unitprice),
avg(unitprice),min(unitprice),max(unitprice)
from products
group by categoryid,unitprice
--group by中，不支持队列分配的别名，也不支持任何用在统计函数的列名
--除了在统计函数的列名，都必须出现在group by中
--使用having关键字来筛选结果
select categoryid,avg(unitprice)
from products
where unitprice>15
group by categoryid
having avg(unitprice)>18
--Having和where的区别:having作用于组,而where作用于表或者视图.
--Having的条件中可以使用聚合函数,而where不可以.
--使用cube关键字
create table inventory
(
item varchar(20),
color varchar(20),
quantity float
)
insert into inventory values('table','blue',124)
insert into inventory values('table','red',223)
insert into inventory values('chair','blue',101)
insert into inventory values('chair','red',210)
select item,color,sum(quantity) from inventory
group by item,color
with cube
--对查询结果进行统计，‘维’
--使用rollup关键字
select item,color,sum(quantity) from inventory
group by item,color
with rollup
--分类求和，求其总和
--使用compute和compute by子句
select categoryid,unitprice,unitsinstock,unitsonorder
from products
order by categoryid
compute sum(unitprice),sum(unitsinstock),sum(unitsonorder)
select categoryid,unitprice,unitsinstock,unitsonorder
from products
order by categoryid
compute sum(unitprice),sum(unitsinstock),sum(unitsonorder)
by categoryid
