java做的网站php/深圳百度快照优化

啊哈第三次上机主要是DQL的进阶与关系代数的练习，对我来说可以说是难度陡增，光是第一题就花了我一上午，需要学的知识点一下子就变多了好多。


先来总结一下：
1）自然连接两个表：table1 join table2 on case
2）去除连接表以后的重复行：目前我只知道手动的方法，就是select ... from table1 join table2 on case时在select后面的选择语句里给这个列名加个表约束，比如table1.列名或者table2.列名这样子
3）解决子查询多重嵌套过于繁琐的问题：用with newtable1 as(select …)。这个真的方便
4）得到前X位的数据信息：在select和属性名字之间加一个top x即可。

助教给出的三个表的SQL定义语句为：

导入顾客信息表：

CREATE TABLE [dbo].[Customer] ([CID] float not null primary key,[Gender] nvarchar(255),[City] nvarchar(255))

导入食品信息表：

CREATE TABLE [dbo].[Food] (
[FID] float not null primary key,
[Name] nvarchar(255),
[City] nvarchar(255),
[Price] float
)

导入订单信息表：

CREATE TABLE [dbo].[Order] (
[OID] float not null primary key,
[CID] float foreign key references Customer(CID),
[FID] float foreign key references Food(FID),
[Quantity] float
)

下面是题目以及我的SQL语句：

--1. 选出3号顾客买过的小于50元的食物的数量(要求连接前先表裁剪)

select Quantity 
from (
select 
FID,Quantity  
from [Order] where CID=3
)  
as [table] join Food 
on [table]."FID"=Food."FID" 
where Food."Price"<50

from…as语句是一个表裁剪语句，得到一个裁剪后的小表，join …on是按条件连接表格，最后一个总的select…from语句是按条件查询符合的列

--2. 选出消费额之和最大的顾客ID

select cid
from(selectCID,sum(expend) as sumexpendfrom(select CID,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)as newtablegroup by CID
)
as newtable2
--这个条件语句是根据消费金额与最大消费金额之间的比较来得出消费最多的消费者的编号
where sumexpend=
(
--这个查询语句是从所有消费金额中选出最大消费金额
select
max(sumexpend)
from(--这段查询得到的是所有顾客的消费总额及其顾客号selectCID,sum(expend) as sumexpendfrom(select CID,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)as newtablegroup by CID-----------------------------------------------
)
as newtable2
)

这道题告诉我这个笨B，嵌套查询以后一定要给这个表起个别名，不然会死的

--3. 选出销售额之和最大的城市名City

select
city
from(--这段得到的是所有城市及其消费额的情况selectcity,sum(sumexpend) as sum_cityfrom(select sumexpend,Cityfrom(--这段查询得到的是所有顾客的消费总额及其顾客号selectCID,sum(expend) as sumexpendfrom(select CID,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)as newtable1group by CID-----------------------------------------------)as newtable2join Customer on newtable2.CID=Customer.CID)as newtable3group by city----------------------------------------------------------------)
as newtable4
where sum_city=(selectmax(sum_city)from(--这段得到的是所有城市及其消费额的情况selectcity,sum(sumexpend) as sum_cityfrom(select sumexpend,Cityfrom(--这段查询得到的是所有顾客的消费总额及其顾客号selectCID,sum(expend) as sumexpendfrom(select CID,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)as newtable1group by CID-----------------------------------------------)as newtable2join Customer on newtable2.CID=Customer.CID)as newtable3group by city----------------------------------------------------------------)as newtable4
)

--4. 选出个人平均消费额超过北京地区"订单平均消费"的女性顾客的ID

先插一个小知识点，查询特定行时所用列的特定值是用单引号括起来的：where city='北京'

select
CID
from(--求得所有女性消费者的消费均额selectCID,sum_expend/count_order as ave,count_order,sum_expendfrom(selectnewtable1.CID,count_order,sum_expend,Gender,Cityfrom(selectnewtable.CID,count(oid) as count_order,sum(expend) as sum_expendfrom(	--这段查询得到的是所有顾客的一次消费的消费总额及其它信息select CID,oid,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)	as newtablegroup by cid)as newtable1join Customer on newtable1.CID=Customer.CID)as newtable2where Gender='女'---------------------------------------------------
)
as newtable3
where ave>
(select*from(selectsum_expend/count_order as avefrom(selectCity,sum(count_order) as count_order,sum(sum_expend) as sum_expendfrom(--这段得到的是顾客们的订单总数和消费总额selectcid,count(oid) as count_order,sum(expend) as sum_expendfrom(	--这段查询得到的是所有顾客的一次消费的消费总额及其它信息select CID,oid,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)	as newtablegroup by cid)as newtable1join Customer on Customer.CID=newtable1.CIDgroup by City)as newtable2where City='北京')as newtable3
)

--5. 选出个人总消费额不超过北京地区“人均总消费"的男性顾客的ID

select
cid
from(selectcid,sum(expend) as sum_expendfrom(selectCID,oid,expend,Genderfrom(	selectCustomer.CID,oid,expend,Gender,Cityfrom(select [Order].CID,oid,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)as newtable1join Customer on Customer.CID=newtable1.CID)as newtable2where Gender='男')as newtable3group by CID
)
as newtable4
where sum_expend<=
(selectsum(sumexpend)/count(CID) as avefrom(selectnewtable2.CID,city,sumexpendfrom(--这段查询得到的是所有顾客的消费总额及其顾客号selectCID,sum(expend) as sumexpendfrom(select [Order].CID,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)as newtable1group by CID-----------------------------------------------)as newtable2join Customer on Customer.CID=newtable2.CID)as newtable3where city='北京'group by city
)

--6. 选出消费能力最高（消费额之和最大）的顾客们来自的城市名City（注意和上一道题目的区别）

select
city
from(selectcidfrom(selectCID,sum(expend) as sumexpendfrom(select CID,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)as newtablegroup by CID)as newtable2--这个条件语句是根据消费金额与最大消费金额之间的比较来得出消费最多的消费者的编号where sumexpend=(--这个查询语句是从所有消费金额中选出最大消费金额selectmax(sumexpend)from(--这段查询得到的是所有顾客的消费总额及其顾客号selectCID,sum(expend) as sumexpendfrom(select CID,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)as newtablegroup by CID)as newtable2)
)
as newtable3
join Customer on Customer.CID=newtable3.CID

--7. 选出男性的每次订单的平均消费额

select
sum(expend)/count(oid)
from(selectCID,oid,expend,Genderfrom(	selectCustomer.CID,oid,expend,Gender,Cityfrom(select [Order].CID,oid,Quantity*Price as expend from [Order] inner join Food on [Order].FID=Food.FID)as newtable1join Customer on Customer.CID=newtable1.CID)as newtable2where Gender='男'
)
as newtable3
group by gender

--8. 选出所有订单消费额大于50的订单ID

select
oid
from(selectOID,quantity*price as expendfrom [Order]join Food on Food.FID=[Order].FID
)
as newtable
where expend>50

--9. 选出购买过“茶”类产品的顾客ID

select
cid
from(selectcid,count(cid) as huhufrom(selectcid,[name]from[Order]join food  on food.FID=[Order].FID)as newtablewhere [name] like '%茶%'group by cid
)
as newtable1

这道题其实可以直接查询，不用group by进行合并，但是不用group by的话会出现重复信息，也就是重复的cid，所以我这里先用group by 把相同cid的行给合并了。

--10. 选出既买过伊修加德的食物，又买过黄金港的食物的顾客ID

with 
t1 as
(select cid,cityfrom [Order]join food on Food.FID=[Order].FIDwhere city='黄金港'group by cid,city
),
t2 as
(select cid,cityfrom [Order]join food on Food.FID=[Order].FIDwhere city='伊修加德'group by cid,city
)
select 
cid
from t1
where cid in (select cid from t2)

--11. 选出买过重庆或者四川两地的食物的顾客ID

with 
t1 as
(select cid,cityfrom [Order]join food on Food.FID=[Order].FIDwhere city='重庆' or city='四川'group by cid,city
)
select 
cid
from t1
group by cid

--12. 选出只买过来自于重庆和四川两地的食物的顾客ID

with 
t1 as
(select cid,city as c1from [Order]join food on Food.FID=[Order].FIDwhere city='重庆' group by cid,city
),
t2 as
(select cid,city as c2from [Order]join food on Food.FID=[Order].FIDwhere city='四川' group by cid,city
)
select 
t1.cid
from t1 join t2 on t1.CID=t2.CID
where c1='重庆' and c2='四川'

--13. 选出没有购买记录的顾客ID

with 
t1 as(
select cid
from [Order]
group by cid
),
t2 as(
select cid
from Customer
)
select
*
from t2
where cid not in (select cid from t1)

--14. 选出TOP3消费量（订单金额之和）最大的食物ID

with 
t1 as(select food.fid,oid,quantity*price as expendfrom [Order]join food on [Order].FID=food.FID
),t2 as(
select
fid,
sum(expend) as sum
from t1
group by fid
)select top 3 fid from t2 order by sum desc

导入顾客信息表：
CREATE TABLE [dbo].[Customer] (
[CID] float not null primary key,
[Gender] nvarchar(255),
[City] nvarchar(255)
)

导入食品信息表：
CREATE TABLE [dbo].[Food] (
[FID] float not null primary key,
[Name] nvarchar(255),
[City] nvarchar(255),
[Price] float
)

导入订单信息表：
CREATE TABLE [dbo].[Order] (
[OID] float not null primary key,
[CID] float foreign key references Customer(CID),
[FID] float foreign key references Food(FID),
[Quantity] float
)