[Up有分]高分求教：一个查询

[Lem0n]

Table1中有（ID,Name1,Name2,Value）
要查询出来的数据要求：
Name1 & Name2 组合一样的只能出现一条，并且出现那一条中的Value是最小的

例如：
ID Name1 Name2 Value
1 aaa bbb 20
2 aaa bbb 10
3 bbb ccc 50
4 bbb ccc 30
5 ccc ddd 60

最后查询结果应为：
ID Name1 Name2 Value
2 aaa bbb 10
4 bbb ccc 30
5 ccc ddd 60

谢谢~~

[Wnyu]

select Name1,Name2,min(Value) from Table1 group by Name1,Name2

[beckhambobo]

select id,name1,name2,value from
(select rank() over(partition by name1,name2 order by value ) rk,
id,name1,name2,value from table1)
where rk= 1;

[Lem0n]

select Name1,Name2,min(Value),min(rowid) --这里会把Name1&Name2一样的记录中Value最小的 值和RowID最小的值选出来吧？同一组中RowID最小的记录不一定Value也最小吧？
from a group by Name1,Name2

[beckhambobo]

方法二:
select * from table1 where (name1,name2,value) in (select name1,name2,min(value) from table1 group by name1,name2)

[onejune4450]

select id,name1,name2,value from
(select row_number() over(partition by name1||name2 order by value ) rn,
id,name1,name2,value from table1)
where rn = 1;

[jiezhi]

為了排除最小的value里面還有重復的：
select * from a where rowid in
(select rowid from
(select Name1,Name2,min(Value),min(rowid) from a group by Name1,Name2)
)

[tiangou]

SQL> select * from test;

ID NAME1 NAME2 VALUE
------ ------------------------------ ------------------------------ ------
1 aaa bbb 20
2 aaa bbb 10
3 bbb ccc 50
4 bbb ccc 30
5 ccc ddd 60

SQL>
SQL> select id,name1,name2,value from test
2 where (name1,name2,value) in
3 ( select name1,name2,min(value)
4 from test
5 group by name1,name2
6 )
7 ;

ID NAME1 NAME2 VALUE
------ ------------------------------ ------------------------------ ------
2 aaa bbb 10
4 bbb ccc 30
5 ccc ddd 60

SQL>

[tiangou]

又及， beckhambobo(beckham)的两个方法都是正确的

[Lem0n]

谢谢大家的回复，

第二种方法还没有排除最小的value里面還有重復的这种可能吧？
select * from table1 where (name1,name2,value) in (select name1,name2,min(value) from table1 group by name1,name2)
如果同一组中value相同的有两条，那么最后会把这两条都选出来的

[danny007]

留个位子，学习学习

[danny007]

select * from table1 t
where value=(select min(value) from table1
where name1=t.name1 and name2=t.name2 )

[danny007]

请问楼主，如果value相同的有两条，且都是最小，那么只选一条的原则是什么？该选id大的还是小的呢？

[Lem0n]

选id小的

[skyxj]

select Name1, Name2, Value, min(ID) from Table1 where Name1||Name2||Value in (select Name1||Name2||min(Value) from Table1 group by Name1||Name2) group by Name1, Name2, Value;

通过！

[meshwork]

SELECT t1.id, t1.name1, t1.name2, t1.value from demo t1 where
t1.value = (Select min(value) from demo t2 where t2.name1 = t1.name1 and t2.name2 = t1.name2)