怎样用sql查询某一列的惟一值以及其他列的数据？

Question

如图，希望从A表得到B表，而不是C表。
其中B表后两列的数据可以随便取，即A表中1、2、3三条随便取一条，4、5两条随便取一条，得到B表

Answer 1

其实有很多种方法 但是都会需要传参数才能做到动态匹配

比较笨的方法:
select * from student s where name in (select distinct(name) from student where name='tom(此处应该动态匹配)' group by name ) and rownum=1
union
select * from student s where name in (select distinct(name) from student where name='Jim(此处应该动态匹配)' group by name ) and rownum=1

希望可以帮到你

Answer 2

使用窗口函数是效率最高的方法,没有之一.

with t as
(select *, row_number()Over(partition by name order by score) as rn
from A
)
select name, subject, score from t where rn =1;

Answer 3

select name, subject, score from
(select *, row_number() over(partition by name order by newid()) as num from A) as TEMP
where num = 1

A中name相同的随机取一条

Answer 4

select * from 表 as a , (
select name,min(subject) from 表 group by name
) as b
where a.name = b.name
and a.subject = b.subject
---取最小值追问

如果同一name下，subject和score都有重复的内容，按照你的方法，需要查询两次才能得到需要的结果。怎样才能只查一次？

原始表如下：
name subject score
Tom 语文 90
Tom 语文 91
Tom 数学 90

追答

改成
select * from 表 as a , (
select name+convert(varchar,score),min(subject) from 表 group by name+convert(varchar,score)) as b
where a.name = b.name
and a.subject = b.subject

本回答被提问者和网友采纳