sql多表查詢語句

Ⅰ sql語句多表查詢

1、select *
from sailors where sid in (select sid from reserves where bid='105')

2、select *
from sailors where sid in (select sid from reserves) and age>18

3、select t1.sid,t1.sname
from sailors t1, reserves t2
where t1.sid=t2.sid
group by t1.sid,t1.sname
having count(*)>=2

Ⅱ SQL多表查詢語句有哪些

不明白你的意思

select * from 表1,表2,....
where

select * from 表1 join 表1 on ..
還有 left join ,right join ,full join，
不知道是不是指這個

Ⅲ sql語言多表查詢

1.查詢有不及格成績的學生姓名
select studName from T_stud
join T_select on T_stud.studNo = T_select.studNo
where score < 60
2.查詢有選課的學生姓名和選課數量
select studName,count(*) from T_stud
right join (select studNo,count(*)
from T_select where 1=1 having count(*) > 0
group by studNo ) as A
on T_stud.studNo = A.studNo
3.查詢選修了60歲以上老師所教課程的所有學生
select distinct B.studName
from ( select A.studNo,A.studName,teacherNo
from ( select studNo,studName,subNo
from T_stud join T_select
on T_stud.studNo = T_select.studNo ) as A
join T_Sub on T_Sub.subNo = A.subNo ) as B
join T_teacher on T_teacher.teacherNo = B.teacherNo
where T_teacher.teacherAge > 60
4.洪七公老師的學生中，考試不及格的人數（姓名列表）
select distinct B.studName
from ( select A.studNo,A.studName,teacherNo
from ( select studNo,studName,subNo
from T_stud join T_select
on T_stud.studNo = T_select.studNo
where T_select.score < 60 ) as A
join T_Sub on T_Sub.subNo = A.subNo ) as B
join T_teacher on T_teacher.teacherNo = B.teacherNo
where T_teacher.teacherName like '洪七公'
5.洪七公老師教的學生列表以及選修的課程
select B.studName,B.subName
from ( select A.studNo,A.studName,teacherNo,subName
from ( select studNo,studName,subNo
from T_stud join T_select
on T_stud.studNo = T_select.studNo
where T_select.score < 60 ) as A
join T_Sub on T_Sub.subNo = A.subNo ) as B
join T_teacher on T_teacher.teacherNo = B.teacherNo
where T_teacher.teacherName like '洪七公'
6.所有大於50歲老師姓名列表
select teacherName from T_teacher
where teacherAge > 50
7.郭靖的課程平均分
select avg(score) from T_stud
join T_select on T_stud.studNo = T_select.studNo
where studName like '郭靖'
8.王語嫣所選課程名稱列表
select subName
from （select subNo
from T_stud join T_select
on T_stud.studNo = T_select.studNo
where studName like '王語嫣') as A
join T_Sub on T_Sub.subNo = A.subNo
9.選修學生最多的課程(這里沒有考慮同時多門課程的選修數相等的情況)
select subName
from (select top(1) subNo,count(*)
from T_select
group by subNo order by subNo desc ) as A
join T_Sub on T_Sub.subNo = A.subNo
10.所教學生最多的老師
其他的沒時間了，你自己去試著寫寫吧。。。

Ⅳ sql語句多個表查詢

select 人員.人員id,訂單.產品id,訂單.訂單id,訂單.數量 from 人員
left join 訂單 on 人員.訂單id=訂單.id
where 人員.人員id=1

是你想要的結果么？

Ⅳ 關於SQL多表聯合查詢語句

select T2.*
from T2 ,
T1
where T2.工號=T1.工號
and T2.姓名='李剛'
and T2.部門='綜合部'

Ⅵ SQL 語句多表查詢

不知道你是要總個數還是數的總和
如果是總和select a.Username,sum(a.Number) from a jion b on a.UserName=b.UserName group by a.Username
如果是總個數selecta.Username, count(a.Number) from a jion b on a.UserName=b.UserName group by a.Username

Ⅶ 簡單的SQL多表查詢語句！

select * from table_A ta,tale_B tb where ta.id = tb.id 或者
select * from table_A ta inner join tale_B tb on ta.id = tb.id
(其中，兩張表關聯的話，至少有一個ta.id = tb.id，三張表關聯查詢的話，至少有兩個關系=，以此類推...)

Ⅷ sql如何多表查詢語句

其實多表查詢，用到的關鍵字，就是像inner join,left join,right join 之類的，根據具本情況，選擇自已適合的連接語句。

舉個例子吧

select a.studnt_sno,b.sno,c.grade from studnt as a inner join depart as b on a.sno=b.sno left join sc as c on b.cno=c.no

Ⅸ sql語句多表查詢

select A.name from A where (select count(C.CS) from C where c.cs not in (select B.CS from B where B.NO=A.NO))=0

Ⅹ SQL多表查詢語句怎麼寫

SQL多表查詢語句的步驟如下：

我們需要准備的材料分別是：電腦、sql查詢器。

1、首先，打開sql查詢器，連接上相應的資料庫表，例如m1表和m2表。