select * from T s where not exists(select 1 from T where A=s.A and b<s.B)
select * from T a where not exists (select 1 from T b where b.A = a.A and a.B > b.A)
SELECT A,B,C FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RID,* FROM tb_name ) AS T WHERE RID=1;
打错了select * from T a where not exists (select 1 from T b where b.A = a.A and a.B > b.B)
select * from T s where not exists(select 1 from T where A=s.A and b<s.B)
DECLARE @TB TABLE(A VARCHAR(3), B INT, C INT) INSERT @TB SELECT 'A01', 1, 1 UNION ALL SELECT 'A01', 2, 3 UNION ALL SELECT 'A01', 3, 2 UNION ALL SELECT 'B01', 1, 1 UNION ALL SELECT 'B01', 2, 1SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB SELECT A,B,C FROM ( SELECT A,B,C,ID=ID-(SELECT COUNT(*) FROM # WHERE A<A.A) FROM # AS A ) A WHERE ID=1DROP TABLE # /* A B C ---- ----------- ----------- A01 1 1 B01 1 1 */
DECLARE @TB TABLE(A VARCHAR(3), B INT, C INT) INSERT @TB SELECT 'A01', 1, 1 UNION ALL SELECT 'A01', 2, 3 UNION ALL SELECT 'A01', 3, 2 UNION ALL SELECT 'B01', 1, 1 UNION ALL SELECT 'B01', 2, 1SELECT A,B=MIN(B), C =MIN(C) FROM @TB GROUP BY A
declare @a table (a varchar(5),b int,c int) insert into @a select 'A01',1,1 union all select 'A01',2,3 union all select 'A01',3,2 union all select 'B01',1,1 union all select 'B01',2,1 select * from @a a where b=(select min(b) from @a where a.A=A)A01 1 1 B01 1 1
from T a
where not exists (select 1 from T b where b.A = a.A and a.B > b.A)
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RID,*
FROM tb_name
) AS T
WHERE RID=1;
from T a
where not exists (select 1 from T b where b.A = a.A and a.B > b.B)
DECLARE @TB TABLE(A VARCHAR(3), B INT, C INT)
INSERT @TB
SELECT 'A01', 1, 1 UNION ALL
SELECT 'A01', 2, 3 UNION ALL
SELECT 'A01', 3, 2 UNION ALL
SELECT 'B01', 1, 1 UNION ALL
SELECT 'B01', 2, 1SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB
SELECT A,B,C FROM (
SELECT A,B,C,ID=ID-(SELECT COUNT(*) FROM # WHERE A<A.A) FROM # AS A
) A WHERE ID=1DROP TABLE #
/*
A B C
---- ----------- -----------
A01 1 1
B01 1 1
*/
INSERT @TB
SELECT 'A01', 1, 1 UNION ALL
SELECT 'A01', 2, 3 UNION ALL
SELECT 'A01', 3, 2 UNION ALL
SELECT 'B01', 1, 1 UNION ALL
SELECT 'B01', 2, 1SELECT A,B=MIN(B), C =MIN(C) FROM @TB GROUP BY A
declare @a table (a varchar(5),b int,c int)
insert into @a select 'A01',1,1
union all select 'A01',2,3
union all select 'A01',3,2
union all select 'B01',1,1
union all select 'B01',2,1
select * from @a a where b=(select min(b) from @a where a.A=A)A01 1 1
B01 1 1