SQL每日一题F0215，多种方法及思路讲解

1
CREATE TABLE F0215
2

3
(
4

5
StuID INT,
6

7
CID VARCHAR(10),
8

9
Course INT
10

11
)
12

13

14

15
INSERT INTO F0215 VALUES
16

17
(1,'001',67),
18

19
(1,'002',89),
20

21
(1,'003',94),
22

23
(2,'001',95),
24

25
(2,'002',88),
26

27
(2,'004',78),
28

29
(3,'001',94),
30

31
(3,'002',77),
32

33
(3,'003',90)
34

35
select * from f0215
36

37
/*
38

39
查询出既学过'001'课程，也学过'003'号课程的学生ID
40

41
*/
42

43

44

45
--错误写法
46

47
SELECT * FROM F0215
48

49
WHERE CID='001' AND CID='003'
50

51

52

53
SELECT * FROM F0215
54

55
WHERE CID='001' OR CID='003'
56

57

58

59
--思路一，取自连接符合条件的学生
60

61
SELECT T1.STUID FROM
62

63
( SELECT STUID FROM F0215 WHERE CID='001' ) T1
64

65
INNER JOIN
66

67
(SELECT STUID FROM F0215 WHERE CID='003' )  T2
68

69
ON T2.STUID=T1.STUID
70

71

72

73

74
SELECT A.STUID
75

76
FROM F0215 A,F0215 B
77

78
WHERE A.CID = '001'
79

80
AND B.CID = '003'
81

82
AND A.STUID = B.STUID
83

84

85

86
--思路二，使用交集取出同时满足条件的学生
87

88
SELECT SC.STUID
89

90
FROM F0215 SC
91

92
WHERE  SC.CID='001'
93

94
INTERSECT
95

96
SELECT SC.STUID
97

98
FROM F0215 SC
99

100
WHERE  SC.CID='003'
101

102

103

104
--思路三
105

106

107

108
SELECT StuID FROM F0215
109

110
WHERE CID IN ('001','003')
111

112
GROUP BY StuID
113

114
HAVING COUNT(StuID)=2
115

116

117

118
--思路四（思路三的变体）
119

120
SELECT STUID FROM
121

122
(
123

124
SELECT STUID FROM F0215 WHERE CID = '001'
125

126
UNION ALL
127

128
SELECT STUID FROM F0215 WHERE CID = '003'
129

130
) A
131

132
GROUP BY STUID HAVING COUNT(STUID) = 2