在线求一条分组sql语句
ID weight price cost
1 0.5 30 20
1 1 40 33
2 2 20 18
2 3 35 30
3 ..
4 ...
要得出这样的结果：即取出weight为最小值的记录
ID weight price cost
1 0.5 30 20
2 2 20 18
.....
....
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-14 15:46:45
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[weight] numeric(2,1),[price] int,[cost] int)
insert [huang]
select 1,0.5,30,20 union all
select 1,1,40,33 union ALL
select 2,2,20,18 union all
select 2,3,35,30
--------------开始查询--------------------------
select * from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT MIN([weight])[weight],id FROM huang GROUP BY id) b WHERE a.id=b.id AND a.[weight]=b.[weight])
----------------结果----------------------------
/*
ID weight price cost
----------- --------------------------------------- ----------- -----------
1 0.5 30 20
2 2.0 20 18
*/
;with cte
as
(
select DENSE_Rank()over(partition by id order by [weight])IDs,* from asa
)
select ID,[weight],price ,cost from cte where IDs=1
