父子关系 - TSQL

问题描述：

我那里有父母与子女产品的数据。父子关系 - TSQL

每个父母得到了一个唯一的代码（P1），并在他们的帐户中的链接代码（L1）和父母的每个孩子得到了一个单独的代码（C12），但它们共享相同的帐户没有。

我想指望家长和孩子

这里是一个样本数据集

create table #Something 
(
    Line_no int, 
    code varchar(5), 
    AccountNo char(5) 
)

一些样本数据

INSERT #Something 
select 12311, 'P1c', 'Ac115' union all 
select 12311, 'L1', 'Ac115' union all 
select 123, 'C1', 'Ac115' union all 
select 1222, 'C1', 'Ac115' union all 
select 1243, 'C1', 'Ac115' union all 
select 433, 'P1a', 'Ac111' union all 
select 433, 'L1', 'Ac111' union all 
select 4331, 'C1', 'Ac111' union all 
select 543, 'C1', 'Ac222' union all 
select 544, 'C1', 'Ac222' union all 
select 4322, 'P1b', 'Ac222' union all 
select 4322, 'L1', 'Ac222' union all 

select 8766,'P1d' , 'Ab111' union all 
select 8766,'L1' , 'Ab111' union all 
select 8767,'C1', 'Ab111' union all 
select 8789,'P1d', 'Ab119' union all 
select 8766,'L1', 'Ab119' union all 
select 876654,'C1', 'Ab119' union all 
select 876655,'C1', 'Ab119' union all 
select 876698,'P1a', 'Ab117' union all 
select 876698,'L1', 'Ab117' union all 
select 987,'C1', 'Ab117' union all 
select 555444,'P1d','Xcv' union all 
select 555444,'L1','Xcv' union all 
select 6754,'C1','Xcv' 

SELECT * from #Something 

drop table #Something

所需的输出是：

[Parent code] [Parent line Count] [Child line Count]  
    P1c    1     3 
    P1a    2     2 
    P1b    1     2 
    P1d    3     4

谢谢

你怎么告诉'P..'是父母和'C..'是一个孩子？ –

答

非常怪异模式，但此查询会给你想要的东西：

with cte as (
    select 
     max(case when code like 'P%' then code end) as [Parent code], 
     count(case when code like 'P%' then code end) as [Parent line Count], 
     count(case when code like 'C%' then code end) as [Child line Count] 
    from Something 
    group by AccountNo 
) 
select 
    [Parent code], 
    sum([Parent line Count]) as [Parent line Count], 
    sum([Child line Count]) as [Child line Count] 
from cte 
group by [Parent code]

sql fiddle demo

如果你不喜欢的公用表表达式，你可以使用子查询：

select 
    [Parent code], 
    sum([Parent line Count]) as [Parent line Count], 
    sum([Child line Count]) as [Child line Count] 
from (
    select 
     max(case when code like 'P%' then code end) as [Parent code], 
     count(case when code like 'P%' then code end) as [Parent line Count], 
     count(case when code like 'C%' then code end) as [Child line Count] 
    from Something 
    group by AccountNo 
) as A 
group by [Parent code]

父子关系 - TSQL

相关推荐