有表SC,内容如下:
SId | CId | score |
---|---|---|
01 | 01 | 80 |
01 | 02 | 90 |
01 | 03 | 99 |
02 | 01 | 70 |
02 | 02 | 60 |
02 | 03 | 80 |
03 | 01 | 80 |
03 | 02 | 80 |
03 | 03 | 80 |
04 | 01 | 50 |
04 | 02 | 30 |
04 | 03 | 20 |
05 | 01 | 76 |
05 | 02 | 87 |
06 | 01 | 31 |
06 | 02 | 34 |
07 | 01 | 89 |
07 | 02 | 98 |
有表Student,内容如下:
SId | Sname | Sage | Ssex |
---|---|---|---|
01 | 赵雷 | 1990-01-01 | 男 |
02 | 钱电 | 1990-12-21 | 男 |
03 | 孙风 | 1990-12-20 | 男 |
04 | 李云 | 1990-12-06 | 男 |
05 | 周梅 | 1990-12-01 | 女 |
06 | 吴兰 | 1990-01-01 | 女 |
07 | 郑竹 | 1989-01-01 | 女 |
题目:查询至少有一门课与学号为“01”的同学所学相同的同学信息
执行语句:
SELECT * FROM Student
WHERE SId IN (SELECT SId FROM SC WHERE CId IN (SELECT CId FROM SC WHERE SId = '01'))
AND SId != '01';
或:
SELECT DISTINCT st.*
FROM SC s1 JOIN SC s2 ON s1.SId = '01' AND s1.CId = s2.CId
JOIN Student st ON s2.SId = st.SId
WHERE st.SId != '01';
得到:
面试题:
1、生成表A
执行语句:
CREATE TABLE A(Member_ID varchar(255),Log_time datetime,URL varchar(255))
2、查询出每个用户访问的页面类型为“理财”的第一个URL(按时间)的记录,以及统计每个用户记录数形成字段Count
SELECT A.URL,
(SELECT COUNT(1) FROM A JOIN B ON A.URL = B.URL WHERE Member_ID = a_out.Member_ID AND Log_class = '理财') AS 'Count'
FROM A a_out JOIN B ON a_out.URL = B.URL
WHERE B.Log_class = '理财'
AND Log_time =
(SELECT MIN(Log_time) FROM A WHERE Member_ID = a_out.Member_ID AND Log_class = '理财');
假设A表内容如下:
MI | DA | URL |
---|---|---|
1 | 3 | h |
1 | 4 | h |
1 | 5 | f |
2 | 2 | f |
2 | 3 | h |
2 | 4 | h |
3 | 1 | h |
3 | 2 | h |
假设B表内容如下:
URL | TY |
---|---|
h | 理财 |
f | 游戏 |
执行语句:
FROM A a_out JOIN B ON a_out.URL = B.URL
WHERE a_out.URL = 'h';
得到:
FROM A a_out JOIN B ON a_out.URL = B.URL
WHERE a_out.URL = 'h'
AND a_out.DA = (SELECT MIN(DA) FROM A WHERE A.MI = a_out.ID AND A.URL = 'h');
得到:
3、将在2018-05-01 00:00:00到2018-05-01 00:23:59内浏览了“购物”类型网络的用户ID更新为“0002567543”
执行语句:
#UPDATE如何做JOIN
UPDATE A JOIN B
ON A.URL = B.URL SET Member_ID = '0002567543'
WHERE Log_time BETWEEN '2018-05-01 00:00:00' AND '2018-05-01 00:23:59'
AND B.Log_class = '购物';