Question

sqlserver三表联查sql语句

select h.*,q.qxname,j.jdname from house h,qx q,jd j where h.qxid=q.qxid and q.qxid=j.qxid and fid=12 这个sql语句查询出来是三条结果集，但是我只要一条，请问我该怎么写sql语句，谢谢大家。

Answer 1

假设学生表叫student,课程表叫class,选课表叫choose
1.三层嵌套的问题
select student.name from student where student.id IN
(select choose.sid from choose where choose.cid NOT IN
(select class.id from class where class.teacher='李明'))
2.一个内连接，一个嵌套
select student.name,avg(choose.score) from
student inner join choose on student.id=choose.sid
where student.id IN
(select choose.sid from choose
where choose.score<'60'
group by choose.sid
having count(choose.sid)>=2)
gruop by student.id
3.一个联合查询，一个嵌套查询
select student.name from student
where student.id IN
(select c1.sid from choose c1 where choose.cid='1'
union
select c2.sid from choose c2 where choose.cid='2'
on c1.sid=c2.sid
)
4.其实就是自连接查询和行列交换的问题：
select student.id,
(case choose.id when '1' then choose.score end) as 1号课成绩,
(case choose.id when '2' then choose.score end) as 2号课成绩,
from student inner join choose on student.id=choose.sid sc1,
student inner join choose on student.id=choose.sid sc2
where sc1.id='1'
and sc2.id='2'
and sc1.score>sc2.score

Answer 2

select Top 1 h.*,q.qxname,j.jdname from house h,qx q,jd j where h.qxid=q.qxid and q.qxid=j.qxid and fid=12

Answer 3

最后的j.jdname不一样，你想要哪条啊？