返回父记录,其子记录等于特定值,并且给定父记录的子记录集等于特定值 [英] Return parent records with child records equaling specific values AND where total set of child records for a given parent equal a specific value
问题描述
当前,我有一个查询,该查询返回具有子表记录子集等于某些值的父记录的父数据.但是,我想缩小它的范围,以仅返回带有具有特定值的子项的那些父记录,但是其中那些是属于给定父项的唯一子记录,或者子项记录的数量不超过给定总数的子记录.
Currently I have a query that returns parent data for parent records that have a subset of child table records equaling certain values. However, I want to narrow it to only return those parent records with children having certain values, but where those are the only child records belonging to given parent or where the number of child records don't exceed a given total.
这是一个示例查询,仅使我到达需要的位置的一半:
Here's an example query that only gets me half way to where I need to be:
SELECT parent.item1, parent.item2, parent.index
FROM parent
INNER JOIN child on parent.index = child.index
WHERE child.value IN (11111111, 33333333)
GROUP BY parent.item1, parent.item2, parent.index
HAVING COUNT(child.value) = 2
不幸的是,此查询返回的任何父级数据都包含"IN"语句中包含的已标识值的子集.我只希望其子记录总数不超过一定数目(或者就我而言,不超过"IN"语句中的值数目)的父记录的父记录数据.是否有一种简单的方法可以完成此操作?
Unfortunately this query returns ANY parent's data that has a subset of the identified values included with the "IN" statement. I only want the parent's data for parent records whose total child records don't exceed a certain number (or, in my case, don't exceed the number of values in the "IN" statement. Is there an easy way to accomplish this?
推荐答案
您要查询的查询是:
SELECT parent.item1, parent.item2, parent.index
FROM parent
INNER JOIN
child
ON child.index = parent.index
GROUP BY
parent.item1, parent.item2, parent.index
HAVING SUM(CASE WHEN child.value IN (1111111, 2222222) THEN 1 ELSE 0 END) = 2
AND COUNT(*) <= 2
如果仅要确保子项与IN
列表中的所有值匹配,而没有一个不在列表中,请使用此值(在SQL Server
中)
If you just want to ensure that children match all of the values in the IN
list and none not in the list, use this (in SQL Server
)
SELECT *
FROM parent p
WHERE NOT EXISTS
(
SELECT NULL
FROM (
SELECT value
FROM child c
WHERE c.index = p.index
) c
FULL JOIN
(
SELECT 11111111 AS value
UNION ALL
SELECT 22222222
) q
ON c.value = q.value
WHERE c.value IS NULL OR q.value IS NULL
)
这篇关于返回父记录,其子记录等于特定值,并且给定父记录的子记录集等于特定值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!