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

2014-06-15 05:45:05 410 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

限行查询API_路帮网目前全国越来越多的城市开始制定车辆限行的交通管制方式，路帮网的全国车辆限行查询接口可查询全国车辆尾号限行的数据，分别可查询尾号限行查询API和事件限行查询API，将返回城市名称、日期、星期、限行类型、限行时间、限制尾号、限行规定、限行区域及图片等字段。接口名称：限行查询API 接口平台：路帮网数据格式：JSON 查询全国限行城市请求参数说明：输出结果说明：结果示例阅读详情

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

...全文

手把手教你用STM32H743+FreeRTOS+LWIP搭建物联网网关（附完整工程）本文详细介绍了如何使用STM32H743微控制器结合FreeRTOS实时操作系统和LWIP协议栈构建高性能物联网网关。内容涵盖硬件配置、FreeRTOS任务设计、LWIP协议栈优化及物联网协议集成，提供完整工程示例，助力开发者快速实现稳定可靠的网络通信枢纽。阅读详情

haochin

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

诺维斯基 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

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

相关推荐