三个表关联的sql语句查询

ding_dq 2017-09-11 02:21:41
现有三张表：项目组表（group）,项目表（project）和项目专家表（project_expert）,表结构如下
项目组表group
id
status
项目表project
id
group_id
项目专家表project_expert
id
project_id
expert_id
project_status
现在要查询符合条件（条件为：每个项目组下的所有项目的状态为1）的项目组个数 。
写了好几个，查出来都不对，求各位大神指教！
...全文
1232 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
繁花尽流年 2017-09-11
  • 打赏
  • 举报
 回复
引用 11 楼 zengertao 的回复:
SELECT COUNT(*) AS [count] 
FROM [group] a
INNER JOIN (
	SELECT group_id
	FROM project 
	WHERE id NOT IN (SELECT project_id FROM project_expert WHERE project_status<>1)
) b ON a.id=b.group_id
WHERE a.[status]=1
LZ试试
SELECT COUNT(*) AS [count] 
FROM [group] a
WHERE a.[status]=1 --这个要不要你自己看需求
	AND id NOT IN (
		SELECT group_id
		FROM project 
		WHERE id IN (SELECT project_id FROM project_expert WHERE project_status<>1)
	)
	AND id IN (SELECT group_id FROM project)
这个是有项目存在并项目都是1
繁花尽流年 2017-09-11
  • 打赏
  • 举报
 回复 
SELECT COUNT(*) AS [count] 
FROM [group] a
INNER JOIN (
	SELECT group_id
	FROM project 
	WHERE id NOT IN (SELECT project_id FROM project_expert WHERE project_status<>1)
) b ON a.id=b.group_id
WHERE a.[status]=1
LZ试试
听雨停了 2017-09-11
  • 打赏
  • 举报
 回复
引用 9 楼 ding_dq 的回复:
我是要统计符合条件（每个项目组下的所有项目的状态为1）的项目组个数，如果项目没有状态就说明它不符合查询条件啊

--测试数据
CREATE TABLE #group(
id INT ,
STATUS VARCHAR(20)	
)
INSERT INTO #group 
SELECT 1,'213121'
UNION ALL
SELECT 2,'232324'
UNION ALL
SELECT 3,'232555'

CREATE TABLE #project(
id INT ,
GROUP_id int	
)
INSERT INTO #project
SELECT 1001,1
UNION ALL
SELECT 1002,1
UNION ALL
SELECT 1003,1
UNION ALL
SELECT 2001,2
UNION ALL
SELECT 2002,2
UNION ALL
SELECT 3001,3
UNION ALL
SELECT 3002,3

CREATE TABLE #project_expert(
id INT,
project_id INT,
expert_id INT,
project_status bit	
)
INSERT INTO #project_expert
SELECT 1,1001,1,1
UNION ALL
SELECT 2,1002,2,0
UNION ALL
SELECT 3,1002,3,1
UNION ALL
SELECT 4,2001,4,1
UNION ALL
SELECT 5,2002,5,1
UNION ALL
SELECT 6,3001,6,0
UNION ALL
SELECT 7,3002,7,1;
--测试数据结束
WITH cte AS (--每个项目组所有的project_status总数
SELECT b.GROUP_id,COUNT(a.project_status) AS allpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
GROUP BY b.GROUP_id
),
cte2 AS (--每个项目组所有status为1的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS succpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
WHERE a.project_status=1
GROUP BY b.GROUP_id
),
--新增的代码
cte3 AS (	--你说的在项目专家表中没有的，比对相同项目组总项目数和项目专家中相同项目组的项目数
SELECT b.GROUP_id
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
GROUP BY b.GROUP_id
HAVING COUNT(distinct a.project_id)=COUNT(b.id)
)
--新增的代码结束
SELECT COUNT(a.group_id) AS cnt FROM cte a
INNER JOIN cte2 b ON a.group_id=b.group_id AND a.allpro=b.succpro--比较以上两表的project总数一样的
WHERE a.group_id IN (select GROUP_id FROM cte3)--新增的代码

cnt
-----------
1

(1 行受影响)
重新插入了一些数据，有你说的项目专家表中不存在的（1003），项目专家表中重复的（1002）的这些情况
ding_dq 2017-09-11
  • 打赏
  • 举报
 回复
引用 8 楼 qq_37170555 的回复:
[quote=引用 7 楼 ding_dq 的回复:] 那如果项目不一定在项目专家表里，那它就没有项目状态了，那该怎么查呢，求指教
你的需求就是“现在要查询符合条件（条件为：每个项目组下的所有项目的状态为1）的项目组个数 ”，对吧。你现在说项目不一定在项目专家表里，那它就没有项目状态了，那你叫我去哪个表给你统计项目状态为1的项目啊，目前就你给出的资料，能统计的也就项目专家表这个表了。如果项目专家表里不存在，那根本没办法知道它的状态啊。或这说还可以统计什么别的也可以？ [/quote] 我是要统计符合条件（每个项目组下的所有项目的状态为1）的项目组个数，如果项目没有状态就说明它不符合查询条件啊
听雨停了 2017-09-11
  • 打赏
  • 举报
 回复
引用 7 楼 ding_dq 的回复:
那如果项目不一定在项目专家表里，那它就没有项目状态了，那该怎么查呢，求指教
你的需求就是“现在要查询符合条件（条件为：每个项目组下的所有项目的状态为1）的项目组个数 ”，对吧。你现在说项目不一定在项目专家表里，那它就没有项目状态了，那你叫我去哪个表给你统计项目状态为1的项目啊，目前就你给出的资料，能统计的也就项目专家表这个表了。如果项目专家表里不存在，那根本没办法知道它的状态啊。或这说还可以统计什么别的也可以？
ding_dq 2017-09-11
  • 打赏
  • 举报
 回复
引用 6 楼 qq_37170555 的回复:
[quote=引用 5 楼 ding_dq 的回复:] [quote=引用 2 楼 qq_37170555 的回复:] 


--测试数据
CREATE TABLE #group(
id INT ,
STATUS VARCHAR(20)	
)
INSERT INTO #group 
SELECT 1,'213121'
UNION ALL
SELECT 2,'232324'
UNION ALL
SELECT 3,'232555'

CREATE TABLE #project(
id INT ,
GROUP_id int	
)
INSERT INTO #project
SELECT 1001,1
UNION ALL
SELECT 1002,1
UNION ALL
SELECT 1003,1
UNION ALL
SELECT 2001,2
UNION ALL
SELECT 2002,2
UNION ALL
SELECT 3001,3
UNION ALL
SELECT 3002,3

CREATE TABLE #project_expert(
id INT,
project_id INT,
expert_id INT,
project_status bit	
)
INSERT INTO #project_expert
SELECT 1,1001,1,1
UNION ALL
SELECT 2,1002,2,0
UNION ALL
SELECT 3,1003,3,1
UNION ALL
SELECT 4,2001,4,1
UNION ALL
SELECT 5,2002,5,1
UNION ALL
SELECT 6,3001,6,0
UNION ALL
SELECT 7,3002,7,1
--测试数据结束
WITH cte AS (--每个项目组所有的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS allpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
GROUP BY b.GROUP_id
),
cte2 AS (--每个项目组所有status为1的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS succpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
WHERE a.project_status=1
GROUP BY b.GROUP_id
)
SELECT COUNT(a.group_id) AS cnt FROM cte a
INNER JOIN cte2 b ON a.group_id=b.group_id AND a.allpro=b.succpro--比较以上两表的project总数一样的

cnt
-----------
1

(1 行受影响)
项目专家表中可能存在多条project_id相同的数据，那这样拿项目组下的总项目数和项目专家表中查出来的满足条件的项目数比较会不会不准确[/quote] 如果是这样的话就改一下逻辑就好了 

WITH cte AS (--每个项目组所有的project_status总数
SELECT b.GROUP_id,COUNT(a.project_status) AS allpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
GROUP BY b.GROUP_id
),
cte2 AS (--每个项目组所有status为1的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS succpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
WHERE a.project_status=1
GROUP BY b.GROUP_id
)
SELECT COUNT(a.group_id) AS cnt FROM cte a
INNER JOIN cte2 b ON a.group_id=b.group_id AND a.allpro=b.succpro--比较以上两表的project总数一样的
以上代码就改了WITH cte AS (--每个项目组所有的project_status总数 SELECT b.GROUP_id,COUNT(a.project_status) AS allpro 把原来的a.project_id改成了a.project_status。统计project_status的总数，这样就算你说的有多条project_id相同的数据也没事，我们统计project_status的总数和project_status=1的总数比较就可以了[/quote] 那如果项目不一定在项目专家表里，那它就没有项目状态了，那该怎么查呢，求指教
听雨停了 2017-09-11
  • 打赏
  • 举报
 回复
引用 5 楼 ding_dq 的回复:
[quote=引用 2 楼 qq_37170555 的回复:] 


--测试数据
CREATE TABLE #group(
id INT ,
STATUS VARCHAR(20)	
)
INSERT INTO #group 
SELECT 1,'213121'
UNION ALL
SELECT 2,'232324'
UNION ALL
SELECT 3,'232555'

CREATE TABLE #project(
id INT ,
GROUP_id int	
)
INSERT INTO #project
SELECT 1001,1
UNION ALL
SELECT 1002,1
UNION ALL
SELECT 1003,1
UNION ALL
SELECT 2001,2
UNION ALL
SELECT 2002,2
UNION ALL
SELECT 3001,3
UNION ALL
SELECT 3002,3

CREATE TABLE #project_expert(
id INT,
project_id INT,
expert_id INT,
project_status bit	
)
INSERT INTO #project_expert
SELECT 1,1001,1,1
UNION ALL
SELECT 2,1002,2,0
UNION ALL
SELECT 3,1003,3,1
UNION ALL
SELECT 4,2001,4,1
UNION ALL
SELECT 5,2002,5,1
UNION ALL
SELECT 6,3001,6,0
UNION ALL
SELECT 7,3002,7,1
--测试数据结束
WITH cte AS (--每个项目组所有的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS allpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
GROUP BY b.GROUP_id
),
cte2 AS (--每个项目组所有status为1的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS succpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
WHERE a.project_status=1
GROUP BY b.GROUP_id
)
SELECT COUNT(a.group_id) AS cnt FROM cte a
INNER JOIN cte2 b ON a.group_id=b.group_id AND a.allpro=b.succpro--比较以上两表的project总数一样的

cnt
-----------
1

(1 行受影响)
项目专家表中可能存在多条project_id相同的数据，那这样拿项目组下的总项目数和项目专家表中查出来的满足条件的项目数比较会不会不准确[/quote] 如果是这样的话就改一下逻辑就好了 

WITH cte AS (--每个项目组所有的project_status总数
SELECT b.GROUP_id,COUNT(a.project_status) AS allpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
GROUP BY b.GROUP_id
),
cte2 AS (--每个项目组所有status为1的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS succpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
WHERE a.project_status=1
GROUP BY b.GROUP_id
)
SELECT COUNT(a.group_id) AS cnt FROM cte a
INNER JOIN cte2 b ON a.group_id=b.group_id AND a.allpro=b.succpro--比较以上两表的project总数一样的
以上代码就改了WITH cte AS (--每个项目组所有的project_status总数 SELECT b.GROUP_id,COUNT(a.project_status) AS allpro 把原来的a.project_id改成了a.project_status。统计project_status的总数，这样就算你说的有多条project_id相同的数据也没事，我们统计project_status的总数和project_status=1的总数比较就可以了
ding_dq 2017-09-11
  • 打赏
  • 举报
 回复
引用 2 楼 qq_37170555 的回复:


--测试数据
CREATE TABLE #group(
id INT ,
STATUS VARCHAR(20)	
)
INSERT INTO #group 
SELECT 1,'213121'
UNION ALL
SELECT 2,'232324'
UNION ALL
SELECT 3,'232555'

CREATE TABLE #project(
id INT ,
GROUP_id int	
)
INSERT INTO #project
SELECT 1001,1
UNION ALL
SELECT 1002,1
UNION ALL
SELECT 1003,1
UNION ALL
SELECT 2001,2
UNION ALL
SELECT 2002,2
UNION ALL
SELECT 3001,3
UNION ALL
SELECT 3002,3

CREATE TABLE #project_expert(
id INT,
project_id INT,
expert_id INT,
project_status bit	
)
INSERT INTO #project_expert
SELECT 1,1001,1,1
UNION ALL
SELECT 2,1002,2,0
UNION ALL
SELECT 3,1003,3,1
UNION ALL
SELECT 4,2001,4,1
UNION ALL
SELECT 5,2002,5,1
UNION ALL
SELECT 6,3001,6,0
UNION ALL
SELECT 7,3002,7,1
--测试数据结束
WITH cte AS (--每个项目组所有的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS allpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
GROUP BY b.GROUP_id
),
cte2 AS (--每个项目组所有status为1的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS succpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
WHERE a.project_status=1
GROUP BY b.GROUP_id
)
SELECT COUNT(a.group_id) AS cnt FROM cte a
INNER JOIN cte2 b ON a.group_id=b.group_id AND a.allpro=b.succpro--比较以上两表的project总数一样的

cnt
-----------
1

(1 行受影响)
项目专家表中可能存在多条project_id相同的数据，那这样拿项目组下的总项目数和项目专家表中查出来的满足条件的项目数比较会不会不准确
二月十六 2017-09-11
  • 打赏
  • 举报
 回复
引用 3 楼 ding_dq 的回复:
[quote=引用 1 楼 sinat_28984567 的回复:]
每个项目组下的所有项目的状态为1

项目表project
id
group_id

项目表中没有状态列，所以项目状态为1是什么意思？项目组状态？如果是项目组状态直接这样就行了
SELECT COUNT(1) AS 个数 FROM [group] WHERE status=1


项目状态是项目专家表中的project_status[/quote]
--测试数据

if not object_id(N'Tempdb..#group') is null

	drop table #group

Go

Create table #group([id] int,[status] int)

Insert #group

select 1,1 union all

select 2,0

GO

if not object_id(N'Tempdb..#project') is null

	drop table #project

Go

Create table #project([id] int,[group_id] int)

Insert #project

select 1,1 union all

select 2,1 union all

select 3,2

GO

if not object_id(N'Tempdb..#project_expert') is null

	drop table #project_expert

Go

Create table #project_expert([id] int,[project_id] int,[expert_id] int,[project_status] int)

Insert #project_expert

select 1,1,1,0 union all

select 2,2,2,0 union all

select 3,3,3,1 union all

select 4,1,1,0

Go

--测试数据结束

SELECT  COUNT(1)AS 组数

FROM    #group

        JOIN #project ON #project.group_id = #group.id

WHERE   NOT EXISTS ( SELECT 1

                     FROM   #project_expert

                     WHERE  project_id = #project.id

                            AND project_status <> 1 )



ding_dq 2017-09-11
  • 打赏
  • 举报
 回复
引用 1 楼 sinat_28984567 的回复:
每个项目组下的所有项目的状态为1 项目表project id group_id 项目表中没有状态列，所以项目状态为1是什么意思？项目组状态？如果是项目组状态直接这样就行了 
SELECT COUNT(1) AS 个数 FROM [group] WHERE status=1
项目状态是项目专家表中的project_status
听雨停了 2017-09-11
  • 打赏
  • 举报
 回复 


--测试数据
CREATE TABLE #group(
id INT ,
STATUS VARCHAR(20)	
)
INSERT INTO #group 
SELECT 1,'213121'
UNION ALL
SELECT 2,'232324'
UNION ALL
SELECT 3,'232555'

CREATE TABLE #project(
id INT ,
GROUP_id int	
)
INSERT INTO #project
SELECT 1001,1
UNION ALL
SELECT 1002,1
UNION ALL
SELECT 1003,1
UNION ALL
SELECT 2001,2
UNION ALL
SELECT 2002,2
UNION ALL
SELECT 3001,3
UNION ALL
SELECT 3002,3

CREATE TABLE #project_expert(
id INT,
project_id INT,
expert_id INT,
project_status bit	
)
INSERT INTO #project_expert
SELECT 1,1001,1,1
UNION ALL
SELECT 2,1002,2,0
UNION ALL
SELECT 3,1003,3,1
UNION ALL
SELECT 4,2001,4,1
UNION ALL
SELECT 5,2002,5,1
UNION ALL
SELECT 6,3001,6,0
UNION ALL
SELECT 7,3002,7,1
--测试数据结束
WITH cte AS (--每个项目组所有的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS allpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
GROUP BY b.GROUP_id
),
cte2 AS (--每个项目组所有status为1的project总数
SELECT b.GROUP_id,COUNT(a.project_id) AS succpro
  FROM #project_expert a
INNER JOIN #project b ON a.project_id=b.id
WHERE a.project_status=1
GROUP BY b.GROUP_id
)
SELECT COUNT(a.group_id) AS cnt FROM cte a
INNER JOIN cte2 b ON a.group_id=b.group_id AND a.allpro=b.succpro--比较以上两表的project总数一样的

cnt
-----------
1

(1 行受影响)
二月十六 2017-09-11
  • 打赏
  • 举报
 回复
每个项目组下的所有项目的状态为1 项目表project id group_id 项目表中没有状态列，所以项目状态为1是什么意思？项目组状态？如果是项目组状态直接这样就行了 
SELECT COUNT(1) AS 个数 FROM [group] WHERE status=1
SQL查询语句多表联查、三表关联查询
1.左连接（三表关联） 三表只查询左表有数据的值，右表没有的数据会显示出NULL值 语法 left---join---on 示例： select s.*,c.*,d.* from 表名 s left join 表名 c on c.id = s.sid left join 表名 d on d.id = s.sid select s.*,c.*,d.* from 表名 s left join 表名 c on c.id = s.sid left join 表名 d on d.id = s...
SQL语句多表关联查询语法
**sql语句多表关联查询语法** 一、外连接 1.左连接 left join 或 left outer join SQL语句：select * from student left join score on student.Num=score.Stu_id; 2.右连接 right join 或 right outer join SQL语句：select * from student right join score on student.Num=sco...
SQL语言多表关联查询
新建两张表： 表1：student 截图如下： 表2：course 截图如下： （此时这样建表只是为了演示连接SQL语句，当然实际开发中我们不会这样建表，实际开发中这两个表会有自己不同的主键。） 一、外连接 外连接可分为：左连接、右连接、完全外连接。 1、左连接 left join 或 left outer join SQL语句：select * from student left join course on student.ID=course.ID 执行结果： 左...
SQL语句详解（四）——SQL联表查询
今天我们继续给大家介绍MySQL相关知识，本文主要内容是SQL联表查询。 一、SQL联表查询简介 二、内链接 三、外链接 四、交叉链接 五、结果链接
SQL语句多表连接查询语法
总结：内连接就是两个表的交集 ,左外连接就是左边表加两表交集 ,右外连接就是右边表加两表交集 一、外连接 1.左连接 left join 或 left outer join SQL语句：select * from student left join score on student.Num=score.Stu_id; 2.右连接 right join 或 r...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章