返回父记录，其子记录等于特定值，并且给定父记录的子记录集等于特定值 [英] 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屋！