/*
有两张表，如下图所示
表A(仅列出部分数据作参考)
Order_id User_id Add_time
11701245001 10000 1498882474
11701245002 10001 1498882475
表B(仅列出部分数据作参考)
id Order_id goods_id price
1 11701245001 1001 10
2 11701245001 1002 20
3 11701245002 1001 10
问：用SQL查询 购买过goods_id 为1001的用户user_id
正确答案：BCD
select user_id from A where order_id = (select order_id from B where goods_id = '1001')
select a.user_id from A a,B b where a.order_id=b.order_id and b.goods_id='1001'
select user_id from A where order_id in (select order_id from B where goods_id = '1001')
select A.user_id from A left join B on A.order_id=B.order_id where B.goods_id='1001'
返回多个结果的子查询，外层where不能用"="，需要用in
B选项使用了多表查询，分别为2张表起别名
D选项使用左连接
左连接，返回左边表中所有记录和右边表中链接字段相等的记录
这道题D选项明显不严谨，on只限制了右表的哪些数据需要和左表合并
相比于内连接，D选项where语句过滤出去的数据较多
所以不如用内连接
on作为限制条件，where进行筛选过滤
*/
统计表数据忽略null值的情况
/*
表结构如下
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sno` int(11) NOT NULL,
`cno` tinyint(4) NOT NULL,
`score` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
);
以下查询语句结果一定相等的是（）
A.SELECT sum(score) / count(*) FROM score WHERE cno = 2;
B.SELECT sum(score) / count(id) FROM score WHERE cno = 2;
C.SELECT sum(score) / count(sno) FROM score WHERE cno = 2;
D.SELECT sum(score) / count(score) FROM score WHERE cno = 2;
E.SELECT sum(score) / count(1) FROM score WHERE cno = 2;
F.SELECT avg(score) FROM score WHERE cno = 2;
正确答案：
D,F 查询结果一样
A,B,C,E 查询结果一样
下面插入几条数据
id sno cno score
01 11 99 88
02 12 96 64
03 13 92 66
04 14 90 null
05 15 68 70
所有的统计函数都会忽略空值(null)。
A:统计所有学生的平均分，就算成绩为空的学生，最后计算count(*)时也作为分母基数
计算得到所有学生的平均分
B:与A一样，因为id主键非空，count(id)所得分母基数是所有学生
C:与B一样，非空属性sno，计算的结果也是所有学生的平均分
D:由于score字段的值可能是空，空值在统计时忽略
所以count(score)和sum(score)统计的只是score不为空的学生
计算得到的平均分也只是有成绩的学生的平均分，无法计算所有学生的平均分
E:count(1)与count(*)一样，不会忽略null值，本质是计算行数
F:avg(score)会忽略空值
故计算结果为有成绩的学生的平均分
*/
/*
请取出 BORROW表中日期(RDATE字段)为当天的所有记录？(RDATE字段为datetime型，包含日期与时间)。SQL语句实现正确的是：（ ）
正确答案: A
select * from BORROW where datediff(dd,RDATE,getdate())=0
select * from BORROW where RDATE=getdate()
select * from BORROW where RDATE-getdate()=0
select * from BORROW where RDATE > getdate()
*/
多表查询的复杂操作
/*
大学生春季运动会的数据库，保存了比赛信息的三个表如下：
运动员 sporter（运动员编号 sporterid，姓名name，性别 sex，所属系号 department）
项目 item（项目编号 itemid，名称 itemname，比赛地点 location）
成绩 grade（运动员编号 id，项目编号 itemid，积分 mark）
temp （项目id iid，单个项目最高分）
用SQL语句完成在“体育馆”(比赛地点)进行比赛的各项目名称及其冠军的姓名，正确的是：
SELECT i.itemname,s.name FROM grade g,
(SELECT itemid iid,MAX(mark) max FROM grade WHERE itemid IN
(SELECT itemid FROM item WHERE location='体育馆') GROUP BY itemid) temp,
item i,
sporter s
WHERE g.itemid=temp.iid AND
g.mark=temp.max AND
temp.iid=i.itemid AND
s.sporterid=g.sporterid;
注意：
积分最多的是冠军
要求查找的是各项目的冠军
1，首先找出在“体育馆”中进行的比赛项目id;
SELECT itemid
FROM item
WHERE location='体育馆'
2，然后在成绩表中根据项目id进行分组后找出单个项目最高分;
SELECT itemid iid,MAX(mark) max
FROM grade
WHERE itemid IN (SELECT itemid FROM item WHERE location='体育馆')
GROUP BY itemid
3，接下来将上面含有项目id和项目最高分信息的表与另外三张表连接;
grade g, (上面含有项目最高分信息的表) temp, item i,sporter s
WHERE g.itemid=temp.iid
AND g.mark=temp.max
AND temp.iid=i.itemid
AND s.sporterid=g.sporterid
4，最后按要求从连接后的表中选出项目名称和冠军姓名。
SELECT i.itemname,s.name
FROM (以上连接后的表)
*/
行转列输出记录
/*
错误答案C：没达到行转列的目的
select sno,
case when class='english' then score else 0 end ,
case when class='math' then score else 0 end
from sc
where class in('english','math')
结果：一个学生最终会出现两条记录（英语和数学）
每条记录都是满足当前class条件的那门课程成绩正常，其余课程成绩为0
张三 80 0
张三 0 79
王五 60 0
王五 0 88
修改为：
select sno,
sum(case when class='english' then score else 0 end ) as english,
sum(case when class='math' then score else 0 end) as math
from sc
where class in('english','math')
group by sno; -- 关键在这里
正确答案D：
select sno,
sum(if(class='english',score,0)) as english,
sum(if(class='math',score,0) ) as math
from sc
where class in('english','math')
group by sno
如果科目为English为真，English成绩+score，否则+0
结果：
张三 80 79
王五 60 88
*/