34,596
社区成员
发帖
与我相关
我的任务
分享
create table tb1
(
id int,
[name] varchar(10),
[address] varchar(100)
)
create table tb2
(
id int ,
sex varchar(2),
age int
)
insert into tb1 values(1,'jim','白宫')
insert into tb1 values(2,'tom','中南海')
insert into tb2 values(1,'男',20)
insert into tb2 values(1,'男',30)
insert into tb2 values(2,'女',20)
insert into tb2 values(2,'女',30)
create view view1
as
select a.*, b.id as b_id ,sex,age from tb1 a left join tb2 b on a.id = b.id
select * from view1
/*
id,name,address,b_id,sex,age
1,jim,白宫,1,男,20
1,jim,白宫,1,男,30
2,tom,中南海,2,女,20
2,tom,中南海,2,女,30
(4 行受影响)
/*
id,name,address,b_id,sex,age
1,jim,白宫,1,男,20
1,男,30
2,tom,中南海,2,女,20
2,女,30
(4 行受影响)
with tmp(show_order,id,name,[address], b_id,sex,age)as
(
select
show_order = row_number() over(partition by a.id,a.name,a.[address] order by a.id),a.*,b.id as b_id ,sex,age
from tb1 a left join tb2 b on a.id = b.id
)
select id,name,[address], b_id,sex,age from tmp where show_order = 1
union
select null,null,null, b_id,sex,age from tmp where show_order <> 1
select
case when id0=1 then id else '' end as id,
case when id0=1 then name else '' end as name,
case when id0=1 then address else '' end as address,
b_id,sex,age
from
(select id0=row_number()over(partition by id,name,address order by getdate()),* from tb)t
--用手把它们捂住~
开玩笑,不会,关注
create view view1
as
select id=case when rn=1 then ltrim(id) else '' end,
name=case when rn=1 then name else '' end,
address=case when rn=1 then name else '' end,
b_id,sex,age
from
(
select rn=row_number() over(partition by a.id,a.name,a.address order by getdate()),
a.*, b.id as b_id ,sex,age from tb1 a left join tb2 b on a.id = b.id
) t
go