34,838
社区成员




if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([c1] nvarchar(2),[c2] int,[c3] nvarchar(4))
insert [tb1]
select 'a',1,'AA' union all
select 'a',2,'BB' union all
select 'a',3,'CC' union all
select 'b',1,'CD' union all
select 'b',2,'CA' union all
select 'c',1,'AA' union all
select 'c',3,'CF' union all
select 'd',4,'TR'
SELECT c1,c2,c3 FROM (
select rankId=rank() over(partition by c1 order by c2 desc),* from [tb1])a
WHERE a.rankId='1'
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([c1] nvarchar(2),[c2] int,[c3] nvarchar(4))
insert [tb1]
select 'a',1,'AA' union all
select 'a',2,'BB' union all
select 'a',3,'CC' union all
select 'b',1,'CD' union all
select 'b',2,'CA' union all
select 'c',1,'AA' union all
select 'c',3,'CF' union all
select 'd',4,'TR'
SELECT * FROM [tb1] a WHERE c2=(SELECT MAX(c2) FROM [tb1] WHERE a.c1=c1)ORDER BY c1
*************************
c1 c2 c3
a 3 CC
b 2 CA
c 3 CF
d 4 TR
************************
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([c1] nvarchar(2),[c2] int,[c3] nvarchar(4))
insert [tb1]
select 'a',1,'AA' union all
select 'a',2,'BB' union all
select 'a',3,'CC' union all
select 'b',1,'CD' union all
select 'b',2,'CA' union all
select 'c',1,'AA' union all
select 'c',3,'CF' union all
select 'd',4,'TR'
SELECT * FROM [tb1] a GROUP BY c1,c2,c3
HAVING c2=(SELECT MAX(c2) FROM [tb1] WHERE a.c1=c1)ORDER BY c1
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-16 14:48:16
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([c1] nvarchar(2),[c2] int,[c3] nvarchar(4))
insert [tb1]
select 'a',1,'AA' union all
select 'a',2,'BB' union all
select 'a',3,'CC' union all
select 'b',1,'CD' union all
select 'b',2,'CA' union all
select 'c',1,'AA' union all
select 'c',3,'CF' union all
select 'd',4,'TR'
--------------生成数据--------------------------
select *
from [tb1] a
WHERE EXISTS(SELECT 1 FROM (SELECT c1,MAX(c2)c2 FROM tb1 GROUP BY c1) b WHERE a.c1=b.c1 AND a.c2=b.c2
)
----------------结果----------------------------
/*
c1 c2 c3
---- ----------- ----
a 3 CC
b 2 CA
c 3 CF
d 4 TR
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-16 14:48:16
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([c1] nvarchar(2),[c2] int,[c3] nvarchar(4))
insert [tb1]
select 'a',1,'AA' union all
select 'a',2,'BB' union all
select 'a',3,'CC' union all
select 'b',1,'CD' union all
select 'b',2,'CA' union all
select 'c',1,'AA' union all
select 'c',3,'CF' union all
select 'd',4,'TR'
--------------生成数据--------------------------
SELECT c1,c2,c3
FROM (
select * ,MAX(c2)OVER(PARTITION BY c1)rn
from [tb1])a
WHERE c2=rn
----------------结果----------------------------
/*
c1 c2 c3
---- ----------- ----
a 3 CC
b 2 CA
c 3 CF
d 4 TR
*/
select a.c1,a.c2,a.c3
from tb1 a
where not exists
(select 1 from tb1 b where b.c1=a.c1 and b.c2>a.c2)
order by a.c1
/*
c1 c2 c3
----- ----------- -----
a 3 CC
b 2 CA
c 3 CF
d 4 TR
(4 row(s) affected)
*/
create table tb1
(c1 varchar(5),c2 int,c3 varchar(5))
insert into tb1
select 'a',1,'AA' union all
select 'a',2,'BB' union all
select 'a',3,'CC' union all
select 'b',1,'CD' union all
select 'b',2,'CA' union all
select 'c',1,'AA' union all
select 'c',3,'CF' union all
select 'd',4,'TR'
select a.c1,a.c2,a.c3
from tb1 a
inner join
(select c1,max(c2) 'c2'
from tb1
group by c1) b on a.c1=b.c1 and a.c2=b.c2
order by a.c1
/*
c1 c2 c3
----- ----------- -----
a 3 CC
b 2 CA
c 3 CF
d 4 TR
(4 row(s) affected)
*/
select c1 , c2 , c3
from
(
select *,row_number() over(partition by c1 order by c2 desc) as rownum
from tb1
)t
where rownum = 1