一个SQL 问题 HELP! 求SQL语句

[安得权]

表Test中有三列

Name Qty Loc

目的：得到 Name 相同 的 Qty最小的 Loc信息


求SQL 语句 多谢

[Regan-lin]

select MIN(Qty) from Test where Name='····';

[u011603208]

select name,count(*) as num,Loc from Test group by name

[dianjixue1]

如果是MSSQL 采用ROW_NUMBER()

Select A.Name,A.Qty,A.Loc From 
(
   select *,ROW_NUMBER() over(partition by [Name] order by Qty) AS RowNumber from    Test
) A
Where A.RowNumber = 1

[莱昂纳多达芬奇]

试试： select * from 表 where (name, qty) in ( SELECT NAME, MIN(QTY) FROM 表 GROUP BY NAME )

[莱昂纳多达芬奇]

select * from
(
Select name,qty, loc, row_number() over (partition by name order by qty) as row_number from 表
)
where row_number = 1

[安得权]

引用 3 楼 dianjixue1 的回复:

如果是MSSQL 采用ROW_NUMBER()

Select A.Name,A.Qty,A.Loc From 
(
   select *,ROW_NUMBER() over(partition by [Name] order by Qty) AS RowNumber from    Test
) A
Where A.RowNumber = 1

多谢，已验证，可以。嘿嘿 太好了，O(∩_∩)O哈哈~

[安得权]

引用 5 楼 davinciyxw 的回复:

select * from
(
Select name,qty, loc, row_number() over (partition by name order by qty) as row_number from 表
)
where row_number = 1

恩 这个可以 嘿嘿 多谢了