TA貢獻(xiàn)1831條經(jīng)驗(yàn) 獲得超9個贊
可以使用row_number()函數(shù)，該函數(shù)可以將相同的數(shù)據(jù)做歸類，并附加一列，作為序數(shù)列，sql如下：
select *,ROW_NUMBER() over (partition by address order by age desc) as rw
from stu;
只要在該查詢結(jié)果集外再嵌套一個取出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;
TA貢獻(xiàn)1831條經(jīng)驗(yàn) 獲得超10個贊
三個字段不重復(fù)值的數(shù)量可能不一樣，所以不能將它們放在同一個表里。
你可以分別用三個語句獲取它們不重復(fù)的值列表。
select distinct address from stu;
select distinct name from stu;
select distinct age from stu;
TA貢獻(xiàn)1802條經(jīng)驗(yàn) 獲得超4個贊
select address,max(name),,max(age) from stu group by address;
TA貢獻(xiàn)1816條經(jīng)驗(yàn) 獲得超6個贊
elect address,name,age from stu a
where name+age=(select top 1 name+age from stu where a.address=address group by name,age)
TA貢獻(xiàn)1802條經(jīng)驗(yàn) 獲得超5個贊
select address,name,age from stu where (address,age) in(select address,max(age) from stu group by address)
舉報