sql語句多表查詢

1. sql語句 同時查詢兩個表

舉例說明：（某資料庫中有3張表分別為：userinfo，dep，sex）

userinfo（用戶信息表）表中有三個欄位分別為：user_di（用戶編號）,user_name（用戶姓名）,user_dep（用戶部門） 。（關系說明：userinfo表中的user_dep欄位和dep表中的dep_id欄位為主外鍵關系，userinfo表中的user_sex欄位和sex表中的sex_id欄位為主外鍵關系）

‍

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

3. sql語句多表查詢問題！

你的表我看不太懂，哪個欄位是和用戶表中的userid連接的

select a.發帖數,b.回復數 from
(select 回復數,artid,auid from replies)a
inner join
(select 發帖數,artid,auid from article)b
on b.artid =a.artid
inner join
(select userid from users where username="XXX")c
on c.userid= a.auid

4. sql 語句多表查詢問題

--測試數據,別在正式資料庫中運行，以免刪除同名的其他表
IF NOT OBJECT_ID('會員表') IS NULL
DROP TABLE 會員表
GO
CREATE TABLE 會員表
(ID INT IDENTITY(1,1),
NAME CHAR(100),
SEX INT)
GO
INSERT INTO 會員表(NAME,SEX)
SELECT 'a',0
UNION ALL
SELECT 'b',0
UNION ALL
SELECT 'c',0
UNION ALL
SELECT'd ',1
GO
IF NOT OBJECT_ID('登陸表') IS NULL
DROP TABLE 登陸表
GO
CREATE TABLE 登陸表
(ID INT IDENTITY(1,1),
userid INT,
IP INT,
TIME DATETIME)
GO
INSERT INTO 登陸表(userid,ip,time)
SELECT '1','1','2010-05-08'
UNION ALL
SELECT '1','2','2010-05-01'
UNION ALL
SELECT '2','3','2010-05-02'
UNION ALL
SELECT '3','4','2010-05-03'
UNION ALL
SELECT '2', '5',' 2010-05-04'
GO

SELECT B.ID,CASE WHEN B.SEX=0 THEN '男'ELSE '女'END 'SEX',A.IP,A.TIME FROM 登陸表 A,會員表 B
WHERE NOT EXISTS (SELECT 1 FROM 登陸表 WHERE TIME>A.TIME AND USERID=A.USERID) AND A.USERID=B.ID

5. 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.所教學生最多的老師
其他的沒時間了，你自己去試著寫寫吧。。。

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

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

8. 兩表關聯查詢SQL語句的，要怎麼寫

1、首先打開資料庫客戶端Navicat Premium 15工具，點擊並打開一個資料庫連接。

9. sql語句多個表查詢

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

是你想要的結果么？

10. sql 語句 多表查詢問題

其實是一樣的

1> use test1
2> go
已將資料庫上下文更改為 'Test1'。
1> CREATE TABLE A (
2> sno INT,
3> sname VARCHAR(20)
4> );
5>
6> CREATE TABLE B (
7> sno INT,
8> grade INT
9> );
10> go
1> SET SHOWPLAN_TEXT ON
2> go
1> select sname,grade from A,B where a.sno = b.sno
2> go
StmtText
---------------------------------------------------
select sname,grade from A,B where a.sno = b.sno

(1 行受影響)
StmtText

--------------------------------------------------------------------------------
------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([Test1].[dbo].[B].[sno])=([Test1].[dbo].[A].[sno]), RESIDUAL:([Test1].[dbo].[B].[sno]=[Test1].[dbo].[A].[sno]))
|--Table Scan(OBJECT:([Test1].[dbo].[B]))
|--Table Scan(OBJECT:([Test1].[dbo].[A]))

(3 行受影響)
1> select sname,grade from A,B where b.sno = a.sno
2> go
StmtText
--------------------------------------------------
select sname,grade from A,B where b.sno = a.sno

(1 行受影響)
StmtText

--------------------------------------------------------------------------------
------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([Test1].[dbo].[B].[sno])=([Test1].[dbo].[A].[sno]), RESIDUAL:([Test1].[dbo].[A].[sno]=[Test1].[dbo].[B].[sno]))
|--Table Scan(OBJECT:([Test1].[dbo].[B]))
|--Table Scan(OBJECT:([Test1].[dbo].[A]))

(3 行受影響)
1> SET SHOWPLAN_TEXT OFF
2> go

通過查看 查詢計劃， a.sno = b.sno 和 b.sno = a.sno 並沒有任何的不同。