SQL - 子项必须包含所有指定的值 [英] SQL - child must contain all specified values

问题描述

我有两个包含以下数据的表:

I have two tables with following data:

Test_parent:

parent_id   title
------------------
1   Parent1
2   Parent2
3   Parent3
4   Parent4

Test_child:

child_id    parent_id   property
------------------------------------
1   1   A
2   2   A
3   2   B
4   3   A
5   3   C
6   4   A

我想从表 test_parent 中选择所有行，其中 parent 包含具有(BOTH)属性 A 和 B 的子项(因此这将记录为 parent_id=2)

I want to select all rows from table test_parent where parent contains children with (BOTH) properties A and B (so this would be record with parent_id=2)

这是我目前写的最好的解决方案:

This is the best solution I wrote so far:

select * 
from test_parent p
where (select COUNT(property) 
       from test_child c 
       where p.parent_id = c.parent_id and c.property in ('A', 'B')) = 2

还有更正确"的方法吗?

Is there any more "correct" way?

非常感谢！

这是对象的完整脚本:

CREATE TABLE [dbo].[test_parent](
    [parent_id] [int] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_test_parent] PRIMARY KEY CLUSTERED 
([parent_id]))
GO

CREATE TABLE [dbo].[test_child](
    [child_id] [int] IDENTITY(1,1) NOT NULL,
    [parent_id] [int] NOT NULL,
    [property] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_test_child] PRIMARY KEY CLUSTERED 
([child_id]))
GO

ALTER TABLE [dbo].[test_child]  WITH CHECK ADD  CONSTRAINT [FK_test_child_test_child] FOREIGN KEY([parent_id])
REFERENCES [dbo].[test_parent] ([parent_id])
GO

ALTER TABLE [dbo].[test_child] CHECK CONSTRAINT [FK_test_child_test_child]
GO

SET IDENTITY_INSERT [dbo].[test_parent] ON;
INSERT INTO [dbo].[test_parent]([parent_id], [title])
SELECT 1, N'Parent1' UNION ALL
SELECT 2, N'Parent2' UNION ALL
SELECT 3, N'Parent3' UNION ALL
SELECT 4, N'Parent4'

SET IDENTITY_INSERT [dbo].[test_parent] OFF;
GO

SET IDENTITY_INSERT [dbo].[test_child] ON;

INSERT INTO [dbo].[test_child]([child_id], [parent_id], [property])
SELECT 1, 1, N'A' UNION ALL
SELECT 2, 2, N'A' UNION ALL
SELECT 3, 2, N'B' UNION ALL
SELECT 4, 3, N'A' UNION ALL
SELECT 5, 3, N'C' UNION ALL
SELECT 6, 4, N'A'
GO

SET IDENTITY_INSERT [dbo].[test_child] OFF;

推荐答案

我不确定更正确"，但是一个简单的 JOIN 与 GROUP BY/HAVING 可以在没有子查询的情况下完成；

I'm not sure about "more correct", but a simple JOIN with GROUP BY/HAVING will do it without a subquery;

SELECT test_parent.parent_id, test_parent.title 
FROM test_parent
JOIN test_child ON test_child.parent_id=test_parent.parent_id
 AND test_child.property IN ('A','B')
GROUP BY test_parent.parent_id, test_parent.title
HAVING COUNT(DISTINCT test_child.property)=2

用于测试的 SQLfiddle.

它基本上会将父级与属性等于 'A' 或 'B' 的任何子级连接起来，按父行分组并计算子级上 property 的不同值.如果它等于 2('A' 和 'B' 是两个可能的值)，则返回父级.

It will basically join the parent with any child that has a property equal to 'A' or 'B', group by the parent row and count the distinct values of property on the child. If it's equal to 2 ('A' and 'B' being the two possible values), return the parent.

这篇关于SQL - 子项必须包含所有指定的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！