在线求一条分组sql语句

lsp69 2013-10-14 03:45:27
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
.....
....
...全文
110 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2013-10-14
  • 打赏
  • 举报
回复
引用 8 楼 mail_yq 的回复:
[quote=引用 1 楼 DBA_Huangzj 的回复:]
----------------------------------------------------------------
-- 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
*/
你的模板怎么配的啊?[/quote]手写
再来壹串 2013-10-14
  • 打赏
  • 举报
回复
引用 1 楼 DBA_Huangzj 的回复:
----------------------------------------------------------------
-- 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
*/
你的模板怎么配的啊?
Landa_Peter 2013-10-14
  • 打赏
  • 举报
回复
 ;with cte as
 (
 select ROW_NUMBER()over(partition by id order by weight asc)num,* from tab 
 )
 select ID,weight,price,cost from cte where num=1
-Tracy-McGrady- 2013-10-14
  • 打赏
  • 举报
回复
用exists 或者not exists嘛
guguda2008 2013-10-14
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author  :借一楼语句一用
-- 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] T1
WHERE NOT EXISTS(
SELECT 1 FROM [huang] T2 
WHERE T2.ID=T1.ID AND T2.weight<T1.weight
)
/*
1	0.5	30	20
2	2.0	20	18
*/
Landa_Aiden 2013-10-14
  • 打赏
  • 举报
回复
;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
  • 打赏
  • 举报
回复

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 ID,weight,price,cost
from
(
select a.*,
       ROW_NUMBER() over(partition by id order by weight) as rownum
from [huang] a
) a
where rownum = 1
/*
ID	weight	price	cost
1	0.5  	30	    20
2	2.0	    20	    18
*/
Landa_Jimmy 2013-10-14
  • 打赏
  • 举报
回复

create table #tab(ID int,weight numeric(18,2),price int, cost int)
insert into #tab
 
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 ID,weight,price,cost from (
select ROW_NUMBER()over(partition by id order by weight asc)num,* from #tab 
)a where num=1
-------------------------------------
ID          weight                                  price       cost
----------- --------------------------------------- ----------- -----------
1           0.50                                    30          20
2           2.00                                    20          18

(2 行受影响)

發糞塗牆 2013-10-14
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- 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
*/

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧