SQL server 多表查询与视图的使用

use Saijie   
create table goods1(
   商品编号  int primary key identity(1,1),--主键，自增
   商品名称  varchar(20) unique,  --唯一键
   商品价格  decimal(5,1)  check(商品价格>0),


);
drop table member 
create table member(
   会员编号   int primary key identity(100,1),
   会员姓名  varchar(20),
   性别      varchar(4) default('女')
);
drop table orders 
create table orders(
    订单编号 int primary key identity(0000000,1),
会员编号  int foreign key references  member(会员编号),
商品编号  int  foreign  key references  goods1(商品编号),
    下单时间  smalldatetime,
数量  int
);


insert into goods1 values('棒棒糖',0.5);
insert into goods1 values('奶茶',1.0);
insert into goods1 values('可口可乐',3.5);
insert into goods1 values('雪碧',3.0);
insert into goods1 values('怡宝',2.0);
insert into goods1 values('铅笔',1.0);
insert into goods1 values('酸奶',3.5);
insert into goods1 values('啤酒',3.0);
insert into goods1 values('纸巾（袋装）',1.0);
insert into goods1 values('纸巾（卷）',3.0);
insert into goods1 values('信纸',2.0);
insert into goods1 values('水性笔',3.5);
insert into goods1 values('橡皮擦',1.0);


select * from goods1


insert into member values('文雅','女');
insert into member  values('李双龙','男');
insert into member values('贺志玉','女');
insert into member  values('黄成','男');
insert into member values('蔡文鹏','男');
insert into member  values('王艳芳','女');


select * from member 


insert into orders values (100,1,getdate(),12);
insert into orders values (102,1,getdate()-1,1);
insert into orders values (100,2,getdate()+1,12);
insert into orders values (102,4,getdate()-1,1);
insert into orders values (100,7,getdate(),12);
insert into orders values (101,10,getdate(),4);
insert into orders values (103,9,getdate(),9);
insert into orders values (105,6,getdate()-1,17);


select * from orders ;
/*创建视图*/
 create view  test2 as 
SELECT  dbo.goods1.*, dbo.goods1.商品编号, dbo.goods1.商品名称, dbo.goods1.商品价格, dbo.member.会员编号, dbo.member.会员姓名, dbo.orders.订单编号, 
               dbo.orders.下单时间
FROM     dbo.goods1 INNER JOIN
               dbo.orders ON dbo.goods1.商品编号 = dbo.orders.商品编号 INNER JOIN
               dbo.member ON dbo.orders.会员编号 = dbo.member.会员编号


  select * from test2;


--查询最近三天销售额占前两位的商品编号，名称及销售额


select  top 2 商品编号,商品名称,sum(商品价格*数量) as 销售额 from test2 
group by 商品编号, 商品名称 order by 销售额 desc;






--多表查询
--1. 交叉查询：查询原理：将A表，B表中d数据相互交叉组合，一共有M*N种结合结果。
--1.1 隐式交叉查询:
select goods1.商品编号, 商品名称, 商品价格, 数量,下单时间 from goods1,orders where 会员编号=100;


  --1.2 显式交叉查询
select  goods1.商品编号,商品名称,商品价格,数量,下单时间 from goods1 cross join orders;


--2. 内链接查询:查询原理：将A表，B表中的数据根据某种条件链接起来，
--将符合条件的数据查询显示
--2.1 隐式内链接查询
select 会员编号 ,goods1.商品编号,商品名称,商品价格,数量,下单时间 from goods1,orders where goods1.商品编号=orders.商品编号
 and 会员编号=101;
  --2.2 显式内链接查询
select goods1.商品编号, 商品名称,商品价格,数量,下单时间 from goods1 inner join orders on goods1.商品编号=orders.商品编号
where 会员编号=102;



--左链接查询：查询原理：以左边的表为主，右边的表为辅，将左表和右边中的数据根据某种条件链接起来，
--将符合条件的数据查询显示。
select goods1.商品编号, 商品名称,商品价格,数量,下单时间 from goods1 left join orders on goods1.商品编号=orders.商品编号 where 会员编号=103;


--查询销售额占前两位的商品编号，名称及销售额
select top 2 goods1.商品编号,商品名称,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号
  group  by goods1.商品编号 ,商品名称  order by 消费总额 desc;
  
  --查询会员单次消费的总额
  select 会员编号,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号 
  group  by 商品名称 , 会员编号 ;


  --查询会员消费的总额
  select 会员编号,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号 
  group  by  会员编号 ;


  --查询最近两天天销售额占前两位的商品编号，名称及销售额
select top 2 goods1.商品编号,商品名称,sum(商品价格*数量) as 消费总额 from goods1 inner join orders 
on goods1.商品编号=orders.商品编号 where 下单时间 between GETDATE()-2 and GETDATE()
group by goods1.商品编号,商品名称 order by 消费总额 desc;




--查询如下信息：
/*   1.查询某人的消费总额   */
select sum(商品价格*数量) as 消费总额 from goods1 left join orders
 on goods1.商品编号=orders.商品编号 where 会员编号=101;


/* 2.查询每个人的消费总额,按照会员编号排序   */
select 会员编号,sum(商品价格*数量) as 消费总额 from goods1 inner join orders on goods1.商品编号=orders.商品编号 
  group  by  会员编号 ;


 /* 3.查询某个时间段的消费总额  */
 select  会员编号,sum(商品价格*数量) as 消费总额 from goods1 inner join orders 
on goods1.商品编号=orders.商品编号 where 下单时间 between GETDATE()-2 and GETDATE()
group by 会员编号 order by 消费总额 desc;




/* 4.查询某人购买次数最多的商品名称  */
select  商品名称, max(数量) as 数量 from goods1 left join orders  
 on goods1.商品编号=orders.商品编号    where 会员编号=101 group by 商品名称;


/* 5.查询某个商品的销售额  */
select  商品名称,sum(商品价格*数量) as 销售额 from goods1 left join orders  
 on goods1.商品编号=orders.商品编号    where goods1.商品编号=10 group by 商品名称;


/*  6.查询每个商品的销售额，按照商品销售额排序，商品编号排序  */
select  goods1.商品编号 ,商品名称, sum(商品价格*数量) as 销售额 from goods1 left join orders  
 on goods1.商品编号=orders.商品编号   group by goods1.商品编号,  商品名称 order by 销售额 desc ,goods1.商品编号 asc ;