34,837
社区成员
发帖
与我相关
我的任务
分享求一SQL
如下有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 丙
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 丙
...全文
请发表友善的回复…
发表回复
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
- 打赏
- 举报
对的
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 大神帮忙看看
