Question

sqlserver 数据去重问题

select distinct address,name,age from stu;
select distinct address from stu;
两sql输出结果集数量不一样
我想要以地址去重的数量，但还要三个字段的信息
sql应该怎样写
结果集只要地址不重复，其他列任意选取一条数据即可

Answer 1

可以使用row_number()函数，该函数可以将相同的数据做归类，并附加一列，作为序数列，sql如下：
select *,ROW_NUMBER() over (partition by address order by age desc) as rw
from stu;
只要在该查询结果集外再嵌套一个取出rw=1的sql语句即可，如下：
select * from (
select *,ROW_NUMBER() over (partition by address order by age desc) as rw
from stu ) as t1
where rw = 1;

Answer 2

三个字段不重复值的数量可能不一样，所以不能将它们放在同一个表里。
你可以分别用三个语句获取它们不重复的值列表。
select distinct address from stu;

select distinct name from stu;

select distinct age from stu;

Answer 3

select address,name,age from stu a
where name+age=(select top 1 name+age from stu where a.address=address group by name,age)

Answer 4

select address,name,age from stu where (address,age) in(select address,max(age) from stu group by address)

Answer 5

附加一列序号，再根据address取最大序号