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
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
|