数据库小结（二）
运行以下代码：
drop table OrderProduts2014;
drop table OrderProduts2015;
drop table Produts;
drop table MasterUser;
create table Produts ( id int identity(1,1) primary key, ProdutName nvarchar(100), UnitPrice money, ) ;
insert into Produts select N'安钹维',120
union all select N'波利维',90
union all select N'金维他',50
union all select N'令特适',46
union all select N'易善复',200;
create table MasterUser ( id int identity(1,1) primary key, UserName nvarchar(100), Sex bit, Hospital nvarchar(100) );
insert into MasterUser select N'张山',1,N'北京医大'
union all select N'李娜',0,N'武汉医大'
union all select N'刘翔',1,N'上海医大'
union all select N'韩德军',1,N'大连医大';
create table OrderProduts2014 ( id int identity(1,1) primary key, UserID int, ProductId int, Amout int, OrderDate datetime );
insert into OrderProduts2014 select 1,2,5,'2014-05-06'
union all select 2,3,4,'2014-06-06'
union all select 3,4,10,'2015-06-06';
create table OrderProduts2015 ( id int identity(1,1) primary key, UserID int, ProductId int, Amout int, OrderDate datetime );
insert into OrderProduts2015 select 2,3,6,'2015-05-06'
union all select 3,3,7,'2015-06-06'
union all select 4,5,8,'2015-10-23' ;
--查询2014出年和2015年都购买了产品的医生，并且最大一笔消费超过300的医生所在的医院
1、select max(o14.amout*p.UnitPrice),max(o15.amout*p.UnitPrice),
mu.username, mu.hospital from MasterUser mu
inner join OrderProduts2014 o14 on mu.id = o14.userid
inner join OrderProduts2015 o15 on mu.id = o15.userid
inner join Produts p on o14.ProductId= p.id
inner join Produts p1 on o15.ProductId= p1.id
where o14.userid =o15.userid
group by mu.username,mu.hospital,o14.OrderDate,o15.OrderDate
having max(o14.amout*p.UnitPrice)>300 or max(o15.amout*p.UnitPrice)>300
注：group by与having的使用方式
inner join与left join的使用：在大量重复或为空的数据中，取不为空的数据应用inner join
2、 select distinct mu.username, mu.hospital from MasterUser mu
inner join OrderProduts2014 o14 on mu.id = o14.userid
inner join OrderProduts2015 o15 on mu.id = o15.userid
inner join Produts p on o14.ProductId= p.id
inner join Produts p1 on o15.ProductId= p1.id
where o14.userid =o15.userid
and( (o14.amout*p.UnitPrice)>300 or (o15.amout*p.UnitPrice)>300 )
注：当使用join时，会出现大量的重复数据，若此时去重复，只能使用distinct
3、select hospital from MasterUser where id in(
select userid from OrderProduts2014 o14
inner join Produts p
on o14.ProductId= p.id
where o14.amout*p.UnitPrice>300
intersect
select userid from OrderProduts2015 o15
inner join Produts p1
on o15.ProductId= p1.id
where o15.amout*p1.UnitPrice>300)
注：此时按题目要求，不输出重复的值可使用intersect函数，取其交集再进行输出
4、注意：当14表与15表和Products表进行连接时，注意条件的连接，当分别相关时当join两次Products表
若只取一次，则又进行了14与15表的关联！
inner join Produts p on o14.ProductId= p.id and o15.ProductId= p.id
cf :
inner join Produts p on o14.ProductId= p.id inner join Produts p1 on o15.ProductId= p1.id
