SQL多个记录
问题描述:
我有源表,如下所示:SQL多个记录
ID Status State_id
X Active 1
X Active 2
X Active 3
X Active 4
Y Active 2
Y Active 3
Z Active 1
Z Active 2
Z Active 3
A Active 2
A Active 3
我需要基于STATE_ID 2,3 2输出。
组1只有2,3 STATE_ID
ID Status
Y Active
A Active
组2具有2,3
ID Status
X Active
Z Active
答
/*Set 1*/
select t1.id, t1.status
from table1 t1
where t1.state_id in (2, 3) /*ids you want */
and t1.id not in ((
select distinct t2.id
from table1 t2
where t2.state_id in (1, 4) /*ids you dont want*/
))
group by t1.id, t1.status
只需翻转ID可设置2
+0
这里是小提琴:http://sqlfiddle.com/#!9/15a90c/54 – tommyO
答
附加的状态ID,我认为它应该给你预期的结果。
更新 SqlFiddle
--Set 1
SELECT id ,status
FROM table1
-- WHERE status='Active'
GROUP BY id ,status
HAVING COUNT(DISTINCT state_id) =count(DISTINCT CASE WHEN state_id IN (2,3) THEN state_id END) ;
--Set2
SELECT id ,status
FROM table1
GROUP BY id ,status
HAVING COUNT(DISTINCT state_id)>count(DISTINCT CASE WHEN state_id IN (2,3)
THEN state_id END)
AND COUNT(DISTINCT CASE WHEN state_id IN (2,3) THEN state_id END) =2;
答
你可以使用这个脚本。
集1
SELECT
ID, Status
FROM
@Tbl T
GROUP BY
ID, Status
HAVING
SUM(CASE WHEN State_id IN (2,3) THEN 1 ELSE 0 END) = 2
AND SUM(CASE WHEN State_id IN (2,3) THEN 0 ELSE 1 END) = 0
结果:
ID Status
---- ----------
A Active
Y Active
集2
SELECT
ID, Status
FROM
@Tbl T
GROUP BY
ID, Status
HAVING
SUM(CASE WHEN State_id IN (2,3) THEN 1 ELSE 0 END) = 2
AND SUM(CASE WHEN State_id IN (2,3) THEN 0 ELSE 1 END) > 0
结果:
ID Status
---- ----------
X Active
Z Active
'SELECT ID,状态从TBL WHERE STATE_ID IN(2,3)和( 'Y', 'A')'的数据库做ID你用? Set 1和Set 2有什么不同?它是活动条目的数量吗? – cSteusloff
您正在使用哪个[DBMS](https://en.wikipedia.org/wiki/DBMS)产品? Postgres的?甲骨文? “_SQL_”只是一种查询语言,而不是特定数据库产品的名称。 –