SELECT 查询问题
表t_student：
s_id s_name s_age s_dept
0001 lamar 23 sgdsgdg
0002 faf 32 fsdfsfds
0003 ht 14 ghgfjd
0004 oigd 26 kiajge
0005 jkhfs 25 fsdgha
表t_course：
c_id c_name c_time
001 jtyjt 20
002 gdgd 25
003 nhfg 40
004 dfg 30
005 latg 35
表：t_study
st_id st_s_id st_c_id st_sc_chengji
01 0001 001 78
02 0001 002 85
03 0001 004 80
04 0002 002 75
05 0002 003 86
06 0004 002 90
07 0004 005 85
t_student.s_id=t_study.st_s_id
t_course.c_id=t_study.st_c_id
要求查询各门成绩都在80分以上的学生ID和姓名,每个学生只要有一门成绩在80分以下就不满足条件
先到先给分，谢谢先！~~~~
[解决办法]
1.
SELECT a.s_id, a.s_name
FROM t_student a INNER JOIN
t_study b ON a.s_id = b.st_s_id
GROUP BY a.s_id, a.s_name
HAVING (COUNT(*) =
(SELECT COUNT(1)
FROM t_study
WHERE a.s_id = st_s_id AND st_sc_chengji > = 80))
2.
SELECT DISTINCT a.s_id, a.s_name
FROM t_student a INNER JOIN
t_study b ON a.s_id = b.st_s_id
WHERE (a.s_id NOT IN
(SELECT st_s_id
FROM t_study
WHERE a.s_id = st_s_id AND st_sc_chengji < 80))
[解决办法]
select st_s_id,count(1) st_s_id as jl into #aa from t_study group by st_s_id
select st_s_id,count(1) st_s_id as jl into #bb from t_study where st_sc_chengji> =80 group by st_s_id
select st_s_id from #aa a,#bb b where a.st_s_id=b.st_s_id and a.jl=b.jl
drop table #aa
drop table #bb
其他的再关联
[解决办法]
楼上的，旷考的当然不行了，旷考的应该是0分记录
也就是说，只要在表t_study中有记录的就考虑哦
按照我上面给出的数据，查询的结果应该只有：
s_id s_name
0004 oigd
---------------------------------
--也就是说，你给出的表t_course是没用的咯。我只从表t_study中找出80分以上的st_s_id：
declare @t_study table (st_id varchar(10), st_s_id varchar(10), st_c_id varchar(10), st_sc_chengji tinyint)
insert @t_study
select '01 ', '0001 ', '001 ', '78 ' union all
select '02 ', '0001 ', '002 ', '85 ' union all
select '03 ', '0001 ', '004 ', '80 ' union all
select '04 ', '0002 ', '002 ', '75 ' union all
select '05 ', '0002 ', '003 ', '86 ' union all
select '06 ', '0004 ', '002 ', '90 ' union all
select '07 ', '0004 ', '005 ', '85 '
select st_s_id from @t_study m where not exists (select top 1 * from @t_study where st_sc_chengji < 80 and st_s_id = m.st_s_id) group by st_s_id
