Question

sql 查询一个字段多个值都有的

如：有表:t1
name number money month
...........................
a1 123 10 3
a1 123 5 3
a1 123 7 3
a2 258 4 4
a2 258 2 4
a3 147 1 1
a4 256 7 1
如何用sql语句查出符合如下条件的结果：
name和number相同的只取一条记录（随便一条），只取相同的，显示这条所有字段
如最后结果应为：
name number money month
...........................
a1 123 10 3
a2 258 4 4

Answer 1

DB2/Oracle /SQL SERVER 2005以上可以这样写

select name, number, money, month
from (SELECT name, number, money, month, row_number() over(partition by name, number order by money) rk from tab) t
where rk = 1

Answer 2

select name ,number ,min(money) money, min(month) month,count(1)
from t1 group by name ,number having count(1) >1

Answer 3

select name, number, min(money) money, min(month) month
from t1
group by name, number;

Answer 4

通用的：
select * from t1
where exists(select * from(select name,number,max(money) as money from t1 group by name,number) as tmp
where name=t1.name and number=t1.number
and money=t1.money)

Answer 5

去查下group by子句的用法