请大家帮我看看这个查询怎么写

haochin 2014-06-15 05:45:05
tb1
c1 c2 c3
a 1 AA
a 2 BB
a 3 CC
b 1 CD
b 2 CA
c 1 AA
c 3 CF
d 4 TR

查出每组c1中c2最大的记录
结果如下:
c1 c2
a 3 CC
b 2 CA
c 3 CF
d 4 TR
...全文
293 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
诺维斯基 2014-06-16
  • 打赏
  • 举报
回复
引用 2 楼 ap0405140 的回复:

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)
*/
我同意这个。
以学习为目的 2014-06-16
  • 打赏
  • 举报
回复
只能连续回复3次,终于可以再发三次了
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'
Miss胡不归 2014-06-16
  • 打赏
  • 举报
回复
学习了 原来可以这样
以学习为目的 2014-06-16
  • 打赏
  • 举报
回复
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
************************
以学习为目的 2014-06-16
  • 打赏
  • 举报
回复
来一个非主流一点的
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
以学习为目的 2014-06-16
  • 打赏
  • 举报
回复
你们把我知道的不知道的都写了,让我怎么发挥
發糞塗牆 2014-06-16
  • 打赏
  • 举报
回复
还有一种:
----------------------------------------------------------------
-- 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

*/
發糞塗牆 2014-06-16
  • 打赏
  • 举报
回复
我来个变态点的:
----------------------------------------------------------------
-- 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
*/
唐诗三百首 2014-06-15
  • 打赏
  • 举报
回复
方法2,

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)
*/
唐诗三百首 2014-06-15
  • 打赏
  • 举报
回复

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)
*/
LongRui888 2014-06-15
  • 打赏
  • 举报
回复
如果是2005,可以试试这个:

select c1 , c2 , c3
from 
(
select *,row_number() over(partition by c1 order by c2 desc) as rownum
from tb1
)t
where rownum = 1

34,838

社区成员

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

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