1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
| CREATE TABLE F0215
(
StuID INT,
CID VARCHAR(10),
Course INT
)
INSERT INTO F0215 VALUES
(1,'001',67),
(1,'002',89),
(1,'003',94),
(2,'001',95),
(2,'002',88),
(2,'004',78),
(3,'001',94),
(3,'002',77),
(3,'003',90)
select * from f0215
/*
查询出既学过'001'课程，也学过'003'号课程的学生ID
*/
--错误写法
SELECT * FROM F0215
WHERE CID='001' AND CID='003'
SELECT * FROM F0215
WHERE CID='001' OR CID='003'
--思路一，取自连接符合条件的学生
SELECT T1.STUID FROM
( SELECT STUID FROM F0215 WHERE CID='001' ) T1
INNER JOIN
(SELECT STUID FROM F0215 WHERE CID='003' ) T2
ON T2.STUID=T1.STUID
SELECT A.STUID
FROM F0215 A,F0215 B
WHERE A.CID = '001'
AND B.CID = '003'
AND A.STUID = B.STUID
--思路二，使用交集取出同时满足条件的学生
SELECT SC.STUID
FROM F0215 SC
WHERE SC.CID='001'
INTERSECT
SELECT SC.STUID
FROM F0215 SC
WHERE SC.CID='003'
--思路三
SELECT StuID FROM F0215
WHERE CID IN ('001','003')
GROUP BY StuID
HAVING COUNT(StuID)=2
--思路四（思路三的变体）
SELECT STUID FROM
(
SELECT STUID FROM F0215 WHERE CID = '001'
UNION ALL
SELECT STUID FROM F0215 WHERE CID = '003'
) A
GROUP BY STUID HAVING COUNT(STUID) = 2
|
相关文章：SQL 12
