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 并没有任何的不同。