代码之家 › 专栏 › 技术社区 › Ross Fuhrman

获取具有子记录列表的父表记录的SQL查询

hierarchical-data sql-server-2005 sql

3

Ross Fuhrman · 技术社区 · 15 年前

我在一个MS SQL Server 2005数据库中有两个表,parent和child,其中parent可能与许多子记录相关。[child.parent_id]与[parent.id]相关。子表也有列[foo],我需要返回父表中的所有记录,其中[child.foo]与一对多参数中的每个参数匹配。例如,我希望所有父记录的[child.foo]值为“fizz”,而[child.foo]值为“buzz”。我尝试过下面的查询,但它只返回一个匹配的记录。

SELECT     Parent.ID
FROM         Parent 
INNER JOIN Child ON Parent.ID = Child.parent_id
WHERE     (Child.foo = 'fizz')
UNION ALL
SELECT     Parent_1.ID
FROM         Parent AS Parent_1 
INNER JOIN Child AS Child_1 ON Parent_1.ID = Child_1.parent_id
WHERE     (Child_1.foo = 'buzz')

4 回复 | 直到 9 年前

1

6

mjv 15 年前

这将返回所有父记录,其中[至少]有一个具有“冒泡”foo的子记录,[至少]有一个具有“buzz”foo的子记录。我认为这是问题的必要条件。

此外,虽然这个查询可能是次优的,但从某种意义上说,它将与大多数SQL实现一起工作,而不仅仅是那些更现代的实现,在这些实现中支持CTE、子查询和相关构造。

   SELECT DISTINCT Parent.ID
    FROM Parent
    JOIN Child C1 ON Parent.ID = C1.parent_Id
    JOIN Child C2 ON Parent.ID = C2.parent_id
    WHERE C1.foo = 'fizz'
      AND C2.foo = 'buzz'

编辑 :
既然JoelPotter已经在他的答案中修复了这个查询,我们可能会同意他的方法比上面列出的查询有几个优势(请给他几个+代表)。特别地:

这个结构当我们添加或删除列foo的目标值时,查询的值不会改变。
查询可能更容易优化[由服务器本身]
查询的结构允许它处理过滤器定义的变化。例如,我们可以查询所有有子代的父代,这样就表示存在5个可能的foo值中的2个。

下面是Joel在这里的查询,稍作修改,以显示如何将其扩展为2个以上的foo值。

SELECT Parent.Id
FROM Parent
INNER JOIN Child on Parent.Id = child.parent_id
WHERE Child.foo IN ('fizz', 'buzz')  -- or say, ... IN ('fizz', 'buzz', 'bang', 'dong')
GROUP BY Parent.Id
HAVING COUNT(DISTINCT Child.foo) = 2  -- or 4 ...

2

5

Joel 15 年前

我相信你想要这样的东西。

Select 
    Parent.Id
From Parent
    inner join Child on Parent.Id = child.parent_id
Where 
    Child.foo = 'fizz' or Child.foo = 'buzz'
Group By
    Parent.Id
Having
    count(distinct Child.foo) > 1

下面是测试脚本:

Create Table #parent ( id int )
Create Table #child ( parent_id int, foo varchar(32) )

insert into #parent (id) values (1)
insert into #parent (id) values (2)
insert into #parent (id) values (3)

insert into #child (parent_id, foo) values (1, 'buzz')
insert into #child (parent_id, foo) values (2, 'buzz')
insert into #child (parent_id, foo) values (3, 'buzz')
insert into #child (parent_id, foo) values (1, 'fizz')


Select 
    #parent.Id
From #parent
    inner join #child on #parent.id = #child.parent_id
Where 
    #child.foo = 'fizz' or #child.foo = 'buzz'
Group By
    #parent.Id
Having
    count(distinct #child.foo) > 1        

drop table #parent
drop table #child

仅返回ID 1。

3

0

Daniel Pratt 15 年前

这将得到您想要的结果:

SELECT p.ID
FROM Parent p
WHERE EXISTS
(
    SELECT 1 FROM Child c WHERE c.parent_id = p.ID AND c.foo IN ('fizz','buzz')
)

4

0

Jeffrey Roughgarden 9 年前

我想和大家分享一下乔尔优秀答案的简单概括。这里的想法是能够将“target”子级的任意表作为表值参数或拆分分隔字符串传递到过程中。虽然这很好,但如果有一个类似的查询匹配使用like而不是in,也会很好。

--Parents whose children contain a subset of children

--setup
create table #parent ( id int )
create table #child ( parent_id int, foo varchar(32) )

insert into #parent (id) values (1)
insert into #parent (id) values (2)
insert into #parent (id) values (3)

insert into #child (parent_id, foo) values (1, 'buzz')
insert into #child (parent_id, foo) values (1, 'buzz')
insert into #child (parent_id, foo) values (1, 'fizz')
insert into #child (parent_id, foo) values (2, 'buzz')
insert into #child (parent_id, foo) values (2, 'fizz')
insert into #child (parent_id, foo) values (2, 'bang')
insert into #child (parent_id, foo) values (3, 'buzz')

--create in calling procedure
declare @tblTargets table (strTarget varchar(10))
insert into @tblTargets (strTarget) values ('fizz')
insert into @tblTargets (strTarget) values ('buzz')

--select query to be called in procedure; 
--  pass @tblTargets in as TVP, or create from delimited string via splitter function
select #parent.id       --returns 1 and 2
    from #parent
       inner join #child on #parent.id = #child.parent_id
    where #child.foo in (select strTarget from @tblTargets)
    group by #parent.id
    having count(distinct #child.foo) = (select COUNT(*) from @tblTargets)        

--cleanup
drop table #parent
drop table #child