表AID 列A 列B 列C
1 AAA BBB CCC表BID 列D 列E 列F
1 AA BB CC
1 DD EE FFselect a.列A,a.列B,a.列C,b.列D,b.列E,b.列F from A a left join B b on a.ID = b.ID
结果为:
列A 列B 列C 列D 列E 列F
AAA BBB CCC AA BB CC
AAA BBB CCC DD EE FF但我想要的结果是左边表只显示一行,右边有多少行显示多少行(如下):
列A 列B 列C 列D 列E 列F
AAA BBB CCC AA BB CC
DD EE FF请各位帮忙,谢谢。
1 AAA BBB CCC表BID 列D 列E 列F
1 AA BB CC
1 DD EE FFselect a.列A,a.列B,a.列C,b.列D,b.列E,b.列F from A a left join B b on a.ID = b.ID
结果为:
列A 列B 列C 列D 列E 列F
AAA BBB CCC AA BB CC
AAA BBB CCC DD EE FF但我想要的结果是左边表只显示一行,右边有多少行显示多少行(如下):
列A 列B 列C 列D 列E 列F
AAA BBB CCC AA BB CC
DD EE FF请各位帮忙,谢谢。
1 AA BB CC
1 DD EE FFid 都是1?
;with cte as
(select a.列A,a.列B,a.列C,b.列D,b.列E,b.列F,idd=row_number()over(partition by a.列A order by a.列A ) from A a left join B b on a.ID = b.ID)select 列A= case when idd=1 then 列A else '' END ,
列B= case when idd=1 then 列B else '' END ,
列C= case when idd=1 then 列A else '' END ,
列D,列E,列F FROM cte
(select a.列A,a.列B,a.列C,b.列D,b.列E,b.列F,idd=row_number()over(partition by a.列A order by a.列A ) from A a left join B b on a.ID = b.ID)select 列A= case when idd=1 then 列A else '' END ,
列B= case when idd=1 then 列B else '' END ,
列C= case when idd=1 then 列C else '' END ,
列D,列E,列F FROM cte
(
select a.* ,b.d,b.e,b.f from a left join b on a.id=b.id
)
select
case when id0=1 then id0 else '' end ,*
from
(
select
id0=row_number()over(partition by id order by getdate()),*
from
f
)t