有a,b,c三张表，查询数据如下：
a1 a2 a3 b4 b5 c6 c7
1 ab 范加 df 32 bc aa
2 ac 范加 df 32 bc bb
3 ad 范加 df 32 bc bb
我如何根据C7来去掉重复数据，想要的查询结果如下：
a1 a2 a3 b4 b5 c6 c7
1 ab 范加 df 32 bc aa
2 ac 范加 df 32 bc bb
------解决思路----------------------
select max(a1),max(a2)....max(c6),c7
....
group by c7
------解决思路----------------------
-- sql 2005 + 可用。
select a1,a2,a3,b4,b5,c6,c7
from
(
select a1,a2,a3,b4,b5,c6,c7 ,
ROW_NUMBER() over(partition by c7 order by c7) as rn
) x
where rn = 1
------解决思路----------------------
a b c的关联字段呢
------解决思路----------------------
if OBJECT_ID ('tb') is not null
drop table tb
go
create table tb ( a1 varchar (10) ,a2 varchar(10) ,a3 varchar(10),a4 varchar(10) ,a5 varchar(10), a6 varchar(10) ,a7 varchar(10))
insert into tb
select '1','ab','范加','df','32','bc','aa' union all
select '2','ac','范加','df','32','bc','bb' union all
select '3','ad','范加','df','32','bc','bb'
go
select * from tb a where exists
(
select 1 from
( select min(a1) a1,min(a2) a2,min(a3) a3,min(a4) a4,min(a5) a5,min(a6) a6,a7 from tb group by a7 ) b
where a .a1 =b .a1
)
--a1 a2 a3 a4 a5 a6 a7
--1 ab 范加 df 32 bc aa
--2 ac 范加 df 32 bc bb
------解决思路----------------------
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY c7 ORDER BY a1)RN,*
FROM TB
)
SELECT * FROM CTE WHERE RN=1