sqlserver 数据去重问题

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

慕的地10843
浏览 379回答 5
5回答

临摹微笑

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

开满天机

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

千万里不及你

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

慕妹3146593

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

暮色呼如

select address,name,age from stu where (address,age) in(select address,max(age) from stu group by address)
打开App,查看更多内容
随时随地看视频慕课网APP

相关分类

SQL Server