select * from tb as t where not exists (select 1 from tb where A=t.A and B<t.B)
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-03-13 17:18:48 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([A] varchar(1),[B] int,[C] int) insert [huang] select 'a',1,10 union all select 'a',2,20 union all select 'a',3,30 union all select 'b',2,40 union all select 'b',3,50 union all select 'b',4,60 union all select 'c',6,70 union all select 'c',3,80 union all select 'c',5,90 --------------开始查询--------------------------select * from [huang] a WHERE EXISTS (SELECT 1 FROM (SELECT a,MIN(b)b FROM huang GROUP BY a) b WHERE a.a=b.a AND a.b=b.b) ----------------结果---------------------------- /* A B C ---- ----------- ----------- a 1 10 b 2 40 c 3 80(3 行受影响) */
;with cte as (select *,row_number() over(partition by A order by B)rn from tb ) select * from cte where rn=1
select a,b,c from(select *,r=row_number() over(partition by A order by B) from tb)t where r=1
满足一下你兽欲---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-03-13 17:18:48 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721) -- ---------------------------------------------------------------- --> 测试数据:[huang]if object_id('[huang]') is not null drop table [huang] go create table [huang]([A] varchar(1),[B] int,[C] int) insert [huang] select 'a',1,10 union all select 'a',2,20 union all select 'a',3,30 union all select 'b',2,40 union all select 'b',3,50 union all select 'b',4,60 union all select 'c',6,70 union all select 'c',3,80 union all select 'c',5,90 --------------开始查询-------------------------- --2000以上通用 SELECT * FROM [huang] a WHERE EXISTS ( SELECT 1 FROM ( SELECT a , MIN(b) b FROM huang GROUP BY a ) b WHERE a.a = b.a AND a.b = b.b )
--2005以上才可用 ; WITH cte AS ( SELECT * , row_number() OVER ( PARTITION BY A ORDER BY B ) rn FROM huang ) SELECT * FROM cte WHERE rn = 1 ----------------结果---------------------------- /* A B C ---- ----------- ----------- a 1 10 b 2 40 c 3 80(3 行受影响)A B C rn ---- ----------- ----------- -------------------- a 1 10 1 b 2 40 1 c 3 80 1(3 行受影响) */
select * from test a where a.b1<=all(select b1 from test where a1=a.a1)
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-03-13 17:18:48
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([A] varchar(1),[B] int,[C] int)
insert [huang]
select 'a',1,10 union all
select 'a',2,20 union all
select 'a',3,30 union all
select 'b',2,40 union all
select 'b',3,50 union all
select 'b',4,60 union all
select 'c',6,70 union all
select 'c',3,80 union all
select 'c',5,90
--------------开始查询--------------------------select * from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT a,MIN(b)b FROM huang GROUP BY a) b WHERE a.a=b.a AND a.b=b.b)
----------------结果----------------------------
/*
A B C
---- ----------- -----------
a 1 10
b 2 40
c 3 80(3 行受影响)
*/
(select *,row_number() over(partition by A order by B)rn from tb )
select * from cte where rn=1
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-03-13 17:18:48
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([A] varchar(1),[B] int,[C] int)
insert [huang]
select 'a',1,10 union all
select 'a',2,20 union all
select 'a',3,30 union all
select 'b',2,40 union all
select 'b',3,50 union all
select 'b',4,60 union all
select 'c',6,70 union all
select 'c',3,80 union all
select 'c',5,90
--------------开始查询--------------------------
--2000以上通用
SELECT *
FROM [huang] a
WHERE EXISTS ( SELECT 1
FROM ( SELECT a ,
MIN(b) b
FROM huang
GROUP BY a
) b
WHERE a.a = b.a
AND a.b = b.b )
--2005以上才可用
;
WITH cte
AS ( SELECT * ,
row_number() OVER ( PARTITION BY A ORDER BY B ) rn
FROM huang
)
SELECT *
FROM cte
WHERE rn = 1
----------------结果----------------------------
/*
A B C
---- ----------- -----------
a 1 10
b 2 40
c 3 80(3 行受影响)A B C rn
---- ----------- ----------- --------------------
a 1 10 1
b 2 40 1
c 3 80 1(3 行受影响)
*/
select * from test a where a.b1<=all(select b1 from test where a1=a.a1)