表A
ID Name
1 ABC表B
ID,BID,Num
1 1 2
2 1 3
3 1 4SQL语句
select a.Name,b.Num from a join b on a.ID=b.BID
得出是
ABC,2
ABC,3
ABC,4
现求一语句能得出以下显示
ABC,2
,3
,4
ID Name
1 ABC表B
ID,BID,Num
1 1 2
2 1 3
3 1 4SQL语句
select a.Name,b.Num from a join b on a.ID=b.BID
得出是
ABC,2
ABC,3
ABC,4
现求一语句能得出以下显示
ABC,2
,3
,4
参照这个帖子!
with tmp(show_order,name,num)as
(
show_order = row_number() over(partition by a.id order by a.id) ,a.Name,b.Num from a join b on a.ID=b.BID
)
select name,num from rmp where show_order = 1
union
select null,num from tmp where show_order <>1
a.Name,b.Num into #tb from a join b on a.ID=b.BIDselect name=(case when no=1 then name else null end),Num from #tb
drop table #tb
(
id int,
name varchar(10)
)
insert into a values(1,'ABC')
create table b
(
id int,
bid int,
num int
)
insert into b values(1,1,2)
insert into b values(2,1,3)
insert into b values(3,1,4)with tmp(show_order,name,num)as
(
select show_order = row_number() over(partition by a.id order by a.id) ,a.Name,b.Num from a join b on a.ID=b.BID
)
select name,num from tmp where show_order = 1
union
select '',num from tmp where show_order <>1
order by num
/*
name,num
ABC,2
,3
,4(3 行受影响)
create table a
(
id int,
name varchar(10)
)
insert into a values(1,'ABC')
insert into a values(2,'xxxx')
create table b
(
id int,
bid int,
num int
)
insert into b values(1,1,2)
insert into b values(2,1,3)
insert into b values(3,1,4)
insert into b values(4,2,2)
insert into b values(5,2,3)
insert into b values(6,2,4)
select
(case when b.id=(select min(id) from b t where t.bid=b.bid) then a.name end) name,
b.num
from b ,a where b.bid=a.id
(
select a.Name,b.Num from a join b on a.ID=b.BID
)select
case id when 1 then name else '' end as name,num
from
(select row_number()over(partition by name order by getdate()) as id,* from f)t
b.Num from a join b on a.ID=b.BID