贴数据吧,以供测试 select a.* from tt a left join tt b on a.学生编号=b.学生编号 where a.课程编号='A' AND B.课程编号<>'B' WHERE B.学生编号 IS NOT NULL
test st_id st_cur 1 A 1 C2 C 2 D3 A 3 B4 A 4 B 4 C 4 D所得答案只有A楼上的兄弟方案得到的是所有有A和B的st_id
所得答案应该只有st_id=1,上面说错了,汗~~~
SELECT * FROM TTN A WHERE NOT EXISTS(SELECT * FROM TTN WHERE st_cur='B' AND st_id=A.st_id) AND A.st_cur='A'
不用EXISTS select A1.* from ttn a1 left join ( SELECT A.* FROM TTN A LEFT JOIN TTN B ON (B.st_id=A.st_id ) WHERE A.st_cur='B' ) b1 on B1.st_id=A1.st_id WHERE A1.st_cur='A' AND ISNULL(B1.st_id)
EXISTS也不能用?select A1.* from ttn a1 left join ( SELECT A.* FROM TTN A LEFT JOIN TTN B ON (B.st_id=A.st_id ) WHERE A.st_cur='B' ) b1 on B1.st_id=A1.st_id WHERE A1.st_cur='A' AND ISNULL(B1.st_id)没有用子查询来写成,只是表连接
or select a1.st_id from ( select st_id from ttn where st_cur='A' group by st_id) a1 left join (select st_id from ttn where st_cur='b' group by st_id) b1 on a1.st_id=b1.st_id where isnull(b1.st_id)
昨日思路集中在连接上了,如用MYSQL select st_id from ttn group by st_id having( instr(concat(',',group_concat(st_cur),','),',a,')>0 and instr(concat(',',group_concat(st_cur),','),',b,')=0)
select a.* from tt a left join tt b on a.学生编号=b.学生编号
where a.课程编号='A' AND B.课程编号<>'B' WHERE B.学生编号 IS NOT NULL
st_id st_cur
1 A
1 C2 C
2 D3 A
3 B4 A
4 B
4 C
4 D所得答案只有A楼上的兄弟方案得到的是所有有A和B的st_id
A.st_cur='A'
select A1.* from ttn a1 left join (
SELECT A.* FROM TTN A LEFT JOIN TTN B ON (B.st_id=A.st_id ) WHERE A.st_cur='B' ) b1
on B1.st_id=A1.st_id WHERE A1.st_cur='A'
AND ISNULL(B1.st_id)
SELECT A.* FROM TTN A LEFT JOIN TTN B ON (B.st_id=A.st_id ) WHERE A.st_cur='B' ) b1
on B1.st_id=A1.st_id WHERE A1.st_cur='A'
AND ISNULL(B1.st_id)没有用子查询来写成,只是表连接
select a1.st_id from (
select st_id from ttn where st_cur='A' group by st_id) a1
left join
(select st_id from ttn where st_cur='b' group by st_id) b1
on a1.st_id=b1.st_id
where isnull(b1.st_id)
select st_id from ttn
group by st_id
having(
instr(concat(',',group_concat(st_cur),','),',a,')>0
and
instr(concat(',',group_concat(st_cur),','),',b,')=0)
不过,来晚了,楼上已经解答出来了。
正想说呢,这个问题其实很简单。
我想说的跟楼上写的sql是一致的,就不赘述了。
其实,大家一般在工作中都不怎么用having。