求一SQL

2016-03-22 08:57:10 294 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

一篇文章将三极管讲透：三极管从原理到应用，从参数到特性，从入门到精通晶体三极管（Transistor）是一种半导体电子器件，也是电子工程中最基本的元件之一。它有三个区域，分别是P型半导体，N型半导体和P型半导体，从而形成PNP型晶体三极管或者NPN型晶体三极管。阅读详情

如下有3个表
tUser表
userId userName
1 甲
2 乙
3 丙
4 丁
5 子

tSession表
sessionId userId
1 1
2 2
3 3
4 4
5 5
6 4
7 5

tTask表
id sessionId 字段1 字段2
1 1 1 0
2 1 1 1
3 2 0 0
4 3 1 0
5 3 1 0
6 3 1 0
7 4 2 0
8 4 1 1
9 5 1 1
10 5 1 1
11 6 1 0
13 7 0 0
14 7 0 0
3表外键连接现求一SQL查找到tTask表同组sessionId中字段1全部为1 并且字段2为0的记录返回tUser表的userId和userName字段（如果同组sessionId中的字段1全部为1 并且字段2也都全部为1则不选取）

执行SQL返回的是
userId userName
1 甲
3 丙

...全文

模型优化系列——模型剪枝/量化概述模型优化目的：通过减少模型大小，提高推理速度等使模型成功部署在各个硬件中并实时有效运作。阅读详情

zx005

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

xky191982810 2016-03-24

打赏
举报

SELECT DISTINCT C.userId,C.userName FROM tTask A LEFT JOIN tSession B ON B.sessionId = A.sessionId LEFT JOIN tUser C ON C.userId = B.userId WHERE B.sessionId NOT IN ( SELECT Z.sessionId FROM tTask Z WHERE Z.zd1<>1 ) AND A.zd2 = 0

zx005 2016-03-23

打赏
举报

可能我发错地方了是在mysql下的貌似不支持with as

spiritofdragon 2016-03-23

打赏
举报

with tTask (id,   sessionId,   zd1,  zd2) as
(
select 1, 1, 1, 0 union all
select 2, 1, 1, 1 union all
select 3, 2, 0, 0 union all
select 4, 3, 1, 0 union all
select 5, 3, 1, 0 union all
select 6, 3, 1, 0 union all
select 7, 4, 2, 0 union all
select 8, 4, 1, 1 union all
select 9, 5, 1, 1 union all
select 10, 5, 1, 1 union all
select 11, 6, 1, 0 union all
select 13, 6, 1, 0 union all
select 14, 6, 0, 0
)
, tSession (sessionId, userId) as
(
select 1, 1 union all
select 2, 2 union all
select 3, 3 union all
select 4, 4 union all
select 5, 5 union all
select 6, 4 union all
select 7, 5
)
, tUser (userId, userName)as
(
select 1, 'A' union all
select 2, 'B' union all
select 3, 'C' union all
select 4, 'D' union all
select 5, 'E'
)
, data as
(
select sessionid, count(distinct zd1) as dCntZd1, count(distinct zd2) as dCntZd2, min(zd1) as minzd1d, min(zd2) as minzd2, max(zd2) as maxzd2
from tTask 
group by sessionid
)

select tuser.* from data
inner join tuser on tuser.userid = data.sessionid
where dCntZd1 = 1 and minzd1d = 1 and ((dCntZd2=1 and minzd2=0) or (dCntZd2=2 and minzd2=0 and maxzd2=1))

zx005 2016-03-23

打赏
举报

引用 4 楼 wmxcn2000 的回复:

首先要满足字段 1 全部为 1 ，再看字段 2，如果全是 1 不返回，如果全是 0 或有 0 有1 ，则返回？

对的

spiritofdragon 2016-03-23

打赏
举报

前3个子表都是测试数据表，你自己写好。就最后一个子查询，一级而已，转换下就行



select tuser.* from
(
select sessionid, count(distinct zd1) as dCntZd1, count(distinct zd2) as dCntZd2, min(zd1) as minzd1d, min(zd2) as minzd2, max(zd2) as maxzd2
from tTask 
group by sessionid
)data
inner join tuser on tuser.userid = data.sessionid
where dCntZd1 = 1 and minzd1d = 1 and ((dCntZd2=1 and minzd2=0) or (dCntZd2=2 and minzd2=0 and maxzd2=1))

卖水果的net 版主 2016-03-22

打赏
举报

首先要满足字段 1 全部为 1 ，再看字段 2，如果全是 1 不返回，如果全是 0 或有 0 有1 ，则返回？

zx005 2016-03-22

打赏
举报

字段1 不止会有1 和2

shoppo0505 2016-03-22

打赏
举报

你给出的测试数据 13, 14 行sessionId , 和你图片中的不一样, 我按照图片的数据来的. with tTask (id, sessionId, zd1, zd2) as ( select 1, 1, 1, 0 union all select 2, 1, 1, 1 union all select 3, 2, 0, 0 union all select 4, 3, 1, 0 union all select 5, 3, 1, 0 union all select 6, 3, 1, 0 union all select 7, 4, 2, 0 union all select 8, 4, 1, 1 union all select 9, 5, 1, 1 union all select 10, 5, 1, 1 union all select 11, 6, 1, 0 union all select 13, 6, 0, 0 union all select 14, 6, 0, 0 ) , tSession (sessionId, userId) as ( select 1, 1 union all select 2, 2 union all select 3, 3 union all select 4, 4 union all select 5, 5 union all select 6, 4 union all select 7, 5 ) , tUser (userId, userName)as ( select 1, 'A' union all select 2, 'B' union all select 3, 'C' union all select 4, 'D' union all select 5, 'E' ) , data as ( select sessionid, min(zd1) as minzd1, min(zd2) as minzd2 from tTask group by sessionid ) select tuser.* from data inner join tuser on tuser.userid = data.sessionid where minzd1 = 1 and minzd2 = 0 and data.sessionid not in (select sessionid from tTask where zd1 = 2)

zx005 2016-03-22

打赏
举报

@roy_88 大神帮忙看看

求一SQL

相关推荐