SQL - 1 个父表，2 个子表 - 为子表中的每一行返回单行

Question

表 A

• ParentID
• 名称

表 B

• BKey
• ParentID
• DESB

表 C

• CKey
• ParentID
• DESC

我需要为 B/ 中的每个组合数据行返回 1 行与父 id 匹配，并且如果其中一个子表的行数多于另一个子表，则应返回该描述的一行，其中包含空值。

例如，如果数据如下

表A

1   FirstParent
2   Second Parent

表B

1   1   BDesc1
2   1   BDesc2
3   2   P2BDesc1

表C

1   1   CDesc1
2   2   P2CDesc1
3   2   P2CDesc2

如果我基于FirstParent检索，结果应该是：

1   FirstParent   BDesc1   CDesc1
1   FirstParent   BDesc2   NULL

如果我基于 SecondParent 进行检索，结果应该是：

2   SecondParent   P2BDesc1   P2CDesc1
2   SecondParent   NULL       P2CDesc2

是否有办法在不需要联合的情况下执行此操作？

Answer 1

declare @ParentID int
set @ParentID = 1

select a.name,
       bc.descb,
       bc.descc
from   TableA as a
  cross join (select b.descb,
                     c.descc
              from   (select *,
                             row_number() over(order by b.bkey) as rn
                      from   TableB as b
                      where  b.parentid = @parentid) as b
                full outer join 
                     (select *,
                             row_number() over(order by c.ckey) as rn
                      from   TableC as c
                      where  c.parentid = @parentid) as c
                  on b.rn = c.rn) as bc
where  a.parentid = @parentid  

在这里试试：https://data.stackexchange.com/stackoverflow/qt/112538/

编辑：使用ExternalKey查询多个ParentID的版本

建议索引：

create index IX_B_ParentID on TableB(ParentID) include (DescB)
create index IX_C_ParentID on TableC(ParentID) include (DescC)

我将创建一个表变量来保存与ExternalKey匹配的ParentID，然后使用代替查询中的 TableA。

declare @ExternalKey int = 1

declare @T table(ParentID int primary key, Name varchar(20))
insert into @T (ParentID, Name)
select ParentID, NAme
from TableA
where ExternalKey = @ExternalKey


select a.name,
       bc.descb,
       bc.descc
from   @T as a
  inner join (select b.descb,
                     c.descc,
                     coalesce(b.ParentID, c.ParentID) as ParentID
              from   (select b.ParentID,
                             b.DescB,
                             row_number() over(partition by b.ParentID order by b.bkey) as rn
                      from   TableB as b
                      where  b.parentid in (select ParentID from @T)) as b
                full outer join
                     (select c.ParentID,
                             c.DescC,
                             row_number() over(partition by c.ParentID order by c.ckey) as rn
                      from   TableC as c
                      where  c.parentid in (select ParentID from @T)) as c
                  on b.rn = c.rn and
                     b.ParentID = c.ParentID) as bc
    on a.ParentID = bc.ParentID

Answer 2

我真的希望这是 MSSQL 问题

declare @a table(
ParentID int,
Name varchar(15))

declare @b table(
BKey int,
ParentID int, 
DescB varchar(10))


declare @c table(
CKey int,
ParentID int,
DescC varchar(10))

insert @a values (1,'FirstParent')
insert @a values (2,'SecondParent')

insert @b values(1, 1, 'BDesc1')
insert @b values(2, 1, 'BDesc2')
insert @b values(3, 2, 'P2BDesc1') 

insert @c values(1, 1, 'CDesc1')
insert @c values(2, 2, 'P2CDesc1')
insert @c values(3, 2, 'P2CDesc2')

;with b as
(
    select DescB, ParentID, row_number() over (partition by parentid order by DescB) rn from @b
),
c as
(
    select DescC, ParentID, row_number() over (partition by parentid order by DescC) rn from @c
), 
d as (
    select DescB, DescC, coalesce(b.parentid, c.parentid) parentid from b
    full outer join c
    on c.parentid = b.parentid and c.rn = b.rn
)
select a.ParentID, a.Name, d.DescB, d.DescC from @a a
join d
on a.parentid = d.parentid
order by 1

在这里尝试：
https://data.stackexchange.com/stackoverflow/q/112537/

Answer 3

您可以分两步实现：

1）计算每个子表中的记录数。

2）根据第一步中的记录数连接第一个或第二个表

select a.ParentId, a.Name, b.DescB, c.DescC
from (
    select ParentId, (select count(*) from b where a.ParentId = b.ParentId) as cntB,
    (select count(*) from c where a.ParentId = b.ParentId) as cntC
from a
left join b cntB >= cntC and a.ParentId = b.ParentId
left join c cntB < cntC and a.ParentId = c.ParentId