Group by + all in ...?

Question

我想知道是否所有连接值都在另一个表中

的例子:

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

最好的方法是什么?

Answer 1

SQL小提琴演示

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

Using When Exists (select…)从…)

Answer 2

如果运行这个查询

SELECT id, GROUP_CONCAT(childId) 
FROM  table 
WHERE childId NOT IN (2,3,4,5,6,7,8) 
GROUP BY id

结果中的任何想法都将是错误的。我添加了GROUP_CONCAT，这样您就可以确定哪些子did不在集合中。

Answer 3

试试这个

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

Answer 4

像这样?

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

Answer 5

这个怎么样?

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