集团由+所有在...?
问题描述:
我想知道所有连接的值是否在另一个表中。集团由+所有在...?
例子:
id childId
1 2
1 3
1 4
2 6
2 7
2 8
2 9
childIds
2
3
4
6
7
**desired result:**
id allChildrenInChildIds
1 True
2 False
什么是做到这一点的最好方法是什么?
答
select id, case when sum(TrueorFalse) = count(1) then 'true'
else 'false' end
from (
select id, case when exists (select 1 from ChildIDs where id = childid) then 1
else 0
end as TrueOrFalse
from child) A
group by A.id
使用时存在(选择... ...从)
答
如果您运行此查询
SELECT id, GROUP_CONCAT(childId)
FROM table
WHERE childId NOT IN (2,3,4,5,6,7,8)
GROUP BY id
结果中的任何想法将是错误的。我添加了GROUP_CONCAT,因此您可以确定哪个childId不在集合中。如果一个id不在结果中,那么它是真的。
+0
NOT IN(2,3,4,5,6,7,8)也可能不在(SELECT id从childIds) – miah 2013-03-27 03:55:48
答
试试这个
SELECT
ID
,MIN(allChildrenInChildIds)
FROM
(
SELECT id,
CASE WHEN childId IN (SELECT childIds FROM Table2) THEN 'TRUE' ELSE 'FALSE'
END AS allChildrenInChildIds
FROM Table1
) result
GROUP BY ID
答
像这样的事情?
select
id,
case when exists (
select c.childid
from Child as c
where c.id = ids.id
and not exists (
select *
from Childids
where c.childid = Childids.id
)) then 'No' else 'Yes' end as WereAllChildrenFound
from ids
答
这个怎么样?
select id , case when count(c.childid) <> count(ci.childid) then 'false' else 'true' end as allChildrenInChildIds
from Child c
left join ChildIds ci
on c.childid = ci.childid
group by id
这一个似乎是最容易理解和产生所需的结果... 谢谢! – Vigrond 2013-03-28 01:05:12