34,575
社区成员
发帖
与我相关
我的任务
分享
;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
----------------------------------------------------------------
-- 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
*/
;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
*/
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 行受影响)
----------------------------------------------------------------
-- 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
*/