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.idselect * 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 行受影响)如上面这个例子,我想在视图上面 把tb1 的重复数据给隐藏掉 tb2的数据无变化
下面是我想要的结果
/*
id,name,address,b_id,sex,age
1,jim,白宫,1,男,20
1,男,30
2,tom,中南海,2,女,20
2,女,30(4 行受影响)
(
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.idselect * 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 行受影响)如上面这个例子,我想在视图上面 把tb1 的重复数据给隐藏掉 tb2的数据无变化
下面是我想要的结果
/*
id,name,address,b_id,sex,age
1,jim,白宫,1,男,20
1,男,30
2,tom,中南海,2,女,20
2,女,30(4 行受影响)
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
) tgo
(
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)
gocreate view view1
as
select a.*, b.id as b_id ,sex,age,
rid=row_number() over (partition by a.id,a.name,a.address order by getdate()) --修改下视图
from tb1 a left join tb2 b on a.id = b.id
goselect (case when rid = 1 then ltrim(id) else '' end) id,
(case when rid = 1 then name else '' end) name,
(case when rid = 1 then address else '' end) address,b_id,sex,age
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 行受影响)
*/
*/
drop view view1
drop table tb1,tb2/*************
id name address b_id sex age
------------ ---------- ---------------------------------------------------------------------------------------------------- ----------- ---- -----------
1 jim 白宫 1 男 20
1 男 30
2 tom 中南海 2 女 20
2 女 30(4 行受影响)
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 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 row_number() over (partition by a.id,a.name,a.address
order by b.id,b.sex,b.age) as rid,
a.*, b.id as b_id ,sex,age from tb1 a left join tb2 b on a.id = b.idselect
id=case rid when 1 then ltrim(id) else '' end,
name=case rid when 1 then ltrim(name) else '' end,
address=case rid when 1 then ltrim(address) else '' end,
b_id,sex,age from view1
你还真是被玩到了,id是整型,这个是不行的
case when id0=1 then id else '' end as id
select id=case when rn=1 then id else '' end,
name=case when rn=1 then name else '' end,
address=case when rn=1 then address 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/*1 jim 白宫 1 男 20
0 1 男 30
2 tom 中南海 2 女 20
0 2 女 30
(
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