请教一SQL语句！有点难度！

djfu 2008-10-30 04:14:14
假设有这样一个表：

CarID 汽车车牌号
City 经过城市
RegDate 登记时间


我现在要取同一个【汽车车牌号】的按照【登记日期】升序排列的记录，
连续经过同一个城市的，只取通行记录中【登记时间】最后的一条记录，
最后取时间靠后的最多5条记录。

例如：
CardID City RegDate
001 wh 2008-01-01 18:00:00
001 wh 2008-01-01 18:01:00
001 wh 2008-01-01 18:02:00
001 wh 2008-01-01 18:03:00
001 wh 2008-01-01 18:04:00

001 qd 2008-01-01 18:05:00
001 qd 2008-01-01 18:10:00

001 sh 2008-01-01 18:11:00
001 sh 2008-01-01 18:12:00
001 sh 2008-01-01 18:13:00
001 sh 2008-01-01 18:14:00

001 bj 2008-01-01 18:15:00
001 bj 2008-01-01 18:16:00

001 hn 2008-01-01 18:17:00
001 hn 2008-01-01 18:18:00

001 nb 2008-01-01 18:19:00
001 nb 2008-01-01 18:20:00

那么输出结果是：
001 qd 2008-01-01 18:10:00
001 sh 2008-01-01 18:14:00
001 bj 2008-01-01 18:16:00
001 hn 2008-01-01 18:18:00
001 nb 2008-01-01 18:20:00

这样的SQL语句是很有难度的！
请大家练习一下！
...全文
316 35 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
35 条回复
切换为时间正序
请发表友善的回复…
发表回复
ken2002 2008-10-30
  • 打赏
  • 举报
 回复 


create TABLE test(CardID VARCHAR(3),City VARCHAR(2),RegDate DATETIME)

INSERT INTO Test

SELECT '001','wh','2008-01-01 18:00:00' UNION ALL

SELECT '001','wh','2008-01-01 18:01:00' UNION ALL

SELECT '001','wh','2008-01-01 18:02:00' UNION ALL

SELECT '001','wh','2008-01-01 18:03:00' UNION ALL

SELECT '001','wh','2008-01-01 18:04:00' UNION ALL

SELECT '001','qd','2008-01-01 18:05:00' UNION ALL

SELECT '001','qd','2008-01-01 18:10:00' UNION ALL

SELECT '001','sh','2008-01-01 18:11:00' UNION ALL

SELECT '001','sh','2008-01-01 18:12:00' UNION ALL

SELECT '001','sh','2008-01-01 18:13:00' UNION ALL

SELECT '001','sh','2008-01-01 18:14:00' UNION ALL

SELECT '001','bj','2008-01-01 18:15:00' UNION ALL

SELECT '001','bj','2008-01-01 18:16:00' UNION ALL

SELECT '001','hn','2008-01-01 18:17:00' UNION ALL

SELECT '001','hn','2008-01-01 18:18:00' UNION ALL

SELECT '001','nb','2008-01-01 18:19:00' UNION ALL

SELECT '001','nb','2008-01-01 18:20:00'

go



select * from (select top 5 CardID,city,max(regdate) as RegDate from test group by CardID,city order by regdate desc) a order by RegDate



drop table test



/*结果

CardID city RegDate                                                

------ ---- ------------------------------------------------------ 

001    qd   2008-01-01 18:10:00.000

001    sh   2008-01-01 18:14:00.000

001    bj   2008-01-01 18:16:00.000

001    hn   2008-01-01 18:18:00.000

001    nb   2008-01-01 18:20:00.000



（所影响的行数为 5 行）



*/
ilovewalk 2008-10-30
  • 打赏
  • 举报
 回复
的确,看着晕.
zekelove 2008-10-30
  • 打赏
  • 举报
 回复
学习
Dragon_pan 2008-10-30
  • 打赏
  • 举报
 回复
学习
viva369 2008-10-30
  • 打赏
  • 举报
 回复
小梁很强大
SELECT TOP 5 CardID,City,MAX(RegDate) AS RegDate
FROM
(
SELECT *,
ROW=(SELECT COUNT(*) FROM #T
WHERE CardID=T.CardID AND RegDate<T.RegDate AND City<>T.City
)
FROM #T AS T
) AS T
GROUP BY CardID,City,ROW
ORDER BY RegDate DESC
viva369 2008-10-30
  • 打赏
  • 举报
 回复
为什么都不用游标？


CREATE TABLE  #A (C_NO VARCHAR(10),CITY VARCHAR(220),REG_DATE DATETIME)

INSERT INTO #A  SELECT '001','wh','2008-01-01 18:00:00' 

INSERT INTO #A SELECT '001','wh','2008-01-01 18:01:00' 

INSERT INTO #A SELECT '001','wh','2008-01-01 18:02:00' 

INSERT INTO #A SELECT '001','qd','2008-01-01 18:05:00' 

INSERT INTO #A SELECT '001','qd','2008-01-01 18:10:00' 

INSERT INTO #A SELECT '001','wh','2008-01-01 18:22:00' 

INSERT INTO #A SELECT '001','wh','2008-01-01 18:36:00' 

INSERT INTO #A SELECT '001','sh','2008-01-01 18:50:00' 

go

declare @C_NO nvarchar(20),@city nvarchar(20),@reg_date datetime

declare @C_NOtemp nvarchar(20),@citytemp nvarchar(20),@reg_datetemp datetime

declare @table table(C_NO nvarchar(20),city nvarchar(20),reg_date datetime)

declare  c cursor for select * from #A 

	open c

	fetch next from c into @C_NOtemp,@citytemp,@reg_datetemp  

	while @@fetch_status = 0 

	begin

		fetch next from c into @C_NO,@city,@reg_date

		if(@citytemp <> @city)

			insert @table select @C_NOtemp,@citytemp,@reg_datetemp  

		select @C_NOtemp=@C_NO,@citytemp=@city,@reg_datetemp=@reg_date

	end

	close c  

deallocate c

insert @table select @C_NOtemp,@citytemp,@reg_datetemp 

select * from @table
xiaoliaoyun 2008-10-30
  • 打赏
  • 举报
 回复
[Quote=引用 27 楼 djfu 的回复:]
[/Quote]

我用27楼的数据,执行语句得到的结果如下


DECLARE @T TABLE (CardID VARCHAR(3),City VARCHAR(2),RegDate DATETIME) 

INSERT INTO @T 

SELECT '001','wh','2008-01-01 18:00:00' UNION ALL 

SELECT '001','wh','2008-01-01 18:01:00' UNION ALL 

SELECT '001','wh','2008-01-01 18:02:00' UNION ALL 

SELECT '001','wh','2008-01-01 18:03:00' UNION ALL 

SELECT '001','wh','2008-01-01 18:04:00' UNION ALL 

SELECT '001','qd','2008-01-01 18:05:00' UNION ALL 

SELECT '001','qd','2008-01-01 18:06:00' UNION ALL 

SELECT '001','wh','2008-01-01 18:07:00' UNION ALL 

SELECT '001','wh','2008-01-01 18:08:00' UNION ALL 

SELECT '001','wh','2008-01-01 18:09:00' UNION ALL 

SELECT '001','sh','2008-01-01 18:11:00' UNION ALL 

SELECT '001','sh','2008-01-01 18:12:00' UNION ALL 

SELECT '001','sh','2008-01-01 18:13:00' UNION ALL 

SELECT '001','sh','2008-01-01 18:14:00' UNION ALL 

SELECT '001','bj','2008-01-01 18:15:00' UNION ALL 

SELECT '001','bj','2008-01-01 18:16:00' UNION ALL 

SELECT '001','hn','2008-01-01 18:17:00' UNION ALL 

SELECT '001','hn','2008-01-01 18:18:00' UNION ALL 

SELECT '001','nb','2008-01-01 18:19:00' UNION ALL 

SELECT '001','nb','2008-01-01 18:20:00' UNION ALL 

SELECT '001','hn','2008-01-01 18:22:00' UNION ALL 

SELECT '001','hn','2008-01-01 18:23:00' 

;

WITH TEMP

AS

(

        SELECT  CardID,City,RegDate,ROW_NUMBER() OVER (ORDER BY RegDate) AS orders

          FROM  @T

)

SELECT  TOP 5 CardID,City,RegDate

  FROM  (

        SELECT  * FROM TEMP A

         WHERE  EXISTS (SELECT * FROM TEMP B WHERE A.orders + 1 = B.orders AND A.City <> B.City

                     )

            OR  orders = (SELECT MAX(orders) FROM TEMP)

        )X

 ORDER  BY orders DESC



/* 结果如下. 为什么你的和我的不同? 应该是你少了最后的"ORDER  BY orders DESC" 

CardID City RegDate

------ ---- -----------------------

001    hn   2008-01-01 18:23:00.000

001    nb   2008-01-01 18:20:00.000

001    hn   2008-01-01 18:18:00.000

001    bj   2008-01-01 18:16:00.000

001    sh   2008-01-01 18:14:00.000

*/
djfu 2008-10-30
  • 打赏
  • 举报
 回复
流浪的云：

你这个好像不行，测试如下：
DECLARE @T TABLE (CardID VARCHAR(3),City VARCHAR(2),RegDate DATETIME)
INSERT INTO @T
SELECT '001','wh','2008-01-01 18:00:00' UNION ALL
SELECT '001','wh','2008-01-01 18:01:00' UNION ALL
SELECT '001','wh','2008-01-01 18:02:00' UNION ALL
SELECT '001','wh','2008-01-01 18:03:00' UNION ALL
SELECT '001','wh','2008-01-01 18:04:00' UNION ALL
SELECT '001','qd','2008-01-01 18:05:00' UNION ALL
SELECT '001','qd','2008-01-01 18:06:00' UNION ALL
SELECT '001','wh','2008-01-01 18:07:00' UNION ALL
SELECT '001','wh','2008-01-01 18:08:00' UNION ALL
SELECT '001','wh','2008-01-01 18:09:00' UNION ALL
SELECT '001','sh','2008-01-01 18:11:00' UNION ALL
SELECT '001','sh','2008-01-01 18:12:00' UNION ALL
SELECT '001','sh','2008-01-01 18:13:00' UNION ALL
SELECT '001','sh','2008-01-01 18:14:00' UNION ALL
SELECT '001','bj','2008-01-01 18:15:00' UNION ALL
SELECT '001','bj','2008-01-01 18:16:00' UNION ALL
SELECT '001','hn','2008-01-01 18:17:00' UNION ALL
SELECT '001','hn','2008-01-01 18:18:00' UNION ALL
SELECT '001','nb','2008-01-01 18:19:00' UNION ALL
SELECT '001','nb','2008-01-01 18:20:00' UNION ALL
SELECT '001','hn','2008-01-01 18:22:00' UNION ALL
SELECT '001','hn','2008-01-01 18:23:00'

----------------------------------------------------
001 wh 2008-01-01 18:04:00.000
001 qd 2008-01-01 18:06:00.000
001 wh 2008-01-01 18:09:00.000
001 sh 2008-01-01 18:14:00.000
001 bj 2008-01-01 18:16:00.000
等不到来世 2008-10-30
  • 打赏
  • 举报
 回复
顶小梁～
xiaoliaoyun 2008-10-30
  • 打赏
  • 举报
 回复 


- SQL 2005写法 ,主要是生成序号.2000同理,生成序号就好办了

DECLARE @T TABLE (CardID VARCHAR(3),City VARCHAR(2),RegDate DATETIME)

INSERT INTO @T

SELECT '001','wh','2008-01-01 18:00:00' UNION ALL

SELECT '001','wh','2008-01-01 18:01:00' UNION ALL

SELECT '001','wh','2008-01-01 18:02:00' UNION ALL

SELECT '001','wh','2008-01-01 18:03:00' UNION ALL

SELECT '001','wh','2008-01-01 18:04:00' UNION ALL

SELECT '001','qd','2008-01-01 18:05:00' UNION ALL

SELECT '001','qd','2008-01-01 18:06:00' UNION ALL

SELECT '001','wh','2008-01-01 18:07:00' UNION ALL

SELECT '001','wh','2008-01-01 18:08:00' UNION ALL 

SELECT '001','wh','2008-01-01 18:09:00' UNION ALL

SELECT '001','sh','2008-01-01 18:11:00' UNION ALL

SELECT '001','sh','2008-01-01 18:12:00' UNION ALL

SELECT '001','sh','2008-01-01 18:13:00' UNION ALL

SELECT '001','sh','2008-01-01 18:14:00' UNION ALL

SELECT '001','bj','2008-01-01 18:15:00' UNION ALL

SELECT '001','bj','2008-01-01 18:16:00' UNION ALL

SELECT '001','hn','2008-01-01 18:17:00' UNION ALL

SELECT '001','hn','2008-01-01 18:18:00' UNION ALL

SELECT '001','nb','2008-01-01 18:19:00' UNION ALL

SELECT '001','nb','2008-01-01 18:20:00' 



;

WITH TEMP

AS

(

        SELECT  CardID,City,RegDate,ROW_NUMBER() OVER (ORDER BY RegDate) AS orders

          FROM  @T

)

SELECT  TOP 5 CardID,City,RegDate

  FROM  (

        SELECT  * FROM TEMP A

         WHERE  EXISTS (SELECT * FROM TEMP B WHERE A.orders + 1 = B.orders AND A.City <> B.City

                     )

            OR  orders = (SELECT MAX(orders) FROM TEMP)

        )X

 ORDER  BY orders DESC
djfu 2008-10-30
  • 打赏
  • 举报
 回复
小梁 四星级 的高手写的SQL语句真牛，OK了！！！

牛啊, pf pf~~~~
liangCK 2008-10-30
  • 打赏
  • 举报
 回复
[Quote=引用 22 楼 djfu 的回复:]
有一个问题，大家好像忽略了，
只有对于汽车连续经过同一城市的记录才能合并为一条记录

加了这个限制条件，这个SQL语句显得比较难了。。。
[/Quote]

21楼的不行?
djfu 2008-10-30
  • 打赏
  • 举报
 回复
有一个问题，大家好像忽略了，
只有对于汽车连续经过同一城市的记录才能合并为一条记录

加了这个限制条件，这个SQL语句显得比较难了。。。
liangCK 2008-10-30
  • 打赏
  • 举报
 回复 
--> liangCK小梁 于2008-10-30

--> 生成测试数据: #T

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T

CREATE TABLE #T (CardID VARCHAR(3),City VARCHAR(2),RegDate DATETIME)

INSERT INTO #T

SELECT '001','wh','2008-01-01 20:00:00' UNION ALL

SELECT '001','wh','2008-01-01 20:01:00' UNION ALL

SELECT '001','wh','2008-01-01 20:02:00' UNION ALL

SELECT '001','wh','2008-01-01 20:03:00' UNION ALL

SELECT '001','wh','2008-01-01 20:04:00' UNION ALL

SELECT '001','sz','2008-01-01 20:05:00' UNION ALL

SELECT '001','sz','2008-01-01 20:06:00' UNION ALL

SELECT '001','wh','2008-01-01 20:07:00' UNION ALL

SELECT '001','wh','2008-01-01 20:08:00' UNION ALL

SELECT '001','wh','2008-01-01 20:09:00' UNION ALL

SELECT '001','hz','2008-01-01 20:10:00' UNION ALL

SELECT '001','hz','2008-01-01 20:11:00' UNION ALL

SELECT '001','hz','2008-01-01 20:12:00'



--SQL查询如下:



SELECT TOP 5 CardID,

       City,

       MAX(RegDate) AS RegDate

FROM

(

   SELECT *,

          ROW=(SELECT COUNT(*)

               FROM #T

               WHERE CardID=T.CardID

                   AND RegDate<T.RegDate

                   AND City<>T.City)

   FROM #T AS T

) AS T

GROUP BY CardID,City,ROW

ORDER BY RegDate DESC



/*

CardID City RegDate

------ ---- -----------------------

001    hz   2008-01-01 20:12:00.000

001    wh   2008-01-01 20:09:00.000

001    sz   2008-01-01 20:06:00.000

001    wh   2008-01-01 20:04:00.000



(4 行受影响)



*/
等不到来世 2008-10-30
  • 打赏
  • 举报
 回复
够狠，够YD，的确值得练习一下
liangCK 2008-10-30
  • 打赏
  • 举报
 回复
哦..我明白了.
viva369 2008-10-30
  • 打赏
  • 举报
 回复 


CREATE TABLE  #A (C_NO VARCHAR(10),CITY VARCHAR(220),REG_DATE DATETIME)

INSERT INTO #A  SELECT '001','wh','2008-01-01 18:00:00' 

INSERT INTO #A SELECT '001','wh','2008-01-01 18:01:00' 

INSERT INTO #A SELECT '001','wh','2008-01-01 18:02:00' 

INSERT INTO #A SELECT '001','wh','2008-01-01 18:03:00' 

INSERT INTO #A SELECT '001','wh','2008-01-01 18:04:00' 

INSERT INTO #A SELECT '001','qd','2008-01-01 18:05:00' 

INSERT INTO #A SELECT '001','qd','2008-01-01 18:10:00' 

INSERT INTO #A SELECT '001','sh','2008-01-01 18:11:00' 

INSERT INTO #A SELECT '001','sh','2008-01-01 18:12:00' 

INSERT INTO #A SELECT '001','sh','2008-01-01 18:13:00' 

INSERT INTO #A SELECT '001','sh','2008-01-01 18:14:00' 

INSERT INTO #A SELECT '001','bj','2008-01-01 18:15:00'

INSERT INTO #A SELECT '001','bj','2008-01-01 18:16:00' 

INSERT INTO #A SELECT '001','hn','2008-01-01 18:17:00' 

INSERT INTO #A SELECT '001','hn','2008-01-01 18:18:00' 

INSERT INTO #A SELECT '001','nb','2008-01-01 18:19:00' 

INSERT INTO #A SELECT '001','nb','2008-01-01 18:20:00'



select * from

(

select top 5 C_NO,City,max(Reg_Date) Reg_Date from #A group by C_NO,City

order by max(Reg_Date) desc

)tbl order by Reg_Date
djfu 2008-10-30
  • 打赏
  • 举报
 回复
8楼的，不行啊，你自己用我上面的测试用例试一下就知道了
djfu 2008-10-30
  • 打赏
  • 举报
 回复
有一个问题，大家好像忽略了，
只有对于汽车连续经过同一城市的记录才能合并为一条记录


13 楼的高手，我下面的就是例子啊

例如：


001 wh 2008-01-01 20:00:00
001 wh 2008-01-01 20:01:00
001 wh 2008-01-01 20:02:00
001 wh 2008-01-01 20:03:00
001 wh 2008-01-01 20:04:00

001 sz 2008-01-01 20:05:00
001 sz 2008-01-01 20:06:00

001 wh 2008-01-01 20:07:00
001 wh 2008-01-01 20:08:00
001 wh 2008-01-01 20:09:00

001 hz 2008-01-01 20:10:00
001 hz 2008-01-01 20:11:00
001 hz 2008-01-01 20:12:00

合并的结果是：
001 wh 2008-01-01 20:04:00
001 sz 2008-01-01 20:06:00
001 wh 2008-01-01 20:09:00
001 hz 2008-01-01 20:12:00

注意：上面的经过城市为wh的在结果集里面出现了2次。
因为中间经过了sz
等不到来世 2008-10-30
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 djfu 的回复:]
有一个问题，大家好像忽略了，
只有对于汽车连续经过同一城市的记录才能合并为一条记录
[/Quote]
我没有忽略这个问题。
试问，8楼不能解决你的问题？
加载更多回复（14）
软件设计师考试真题（2004-2011）
2004-2011年软件设计师考试真题以及答案
计算机803_北邮真题-史上最强.zip
史上最全北京邮电大学计算机803考研真题和答案，包括2016-2018年真题及答案。因为北邮是从16年以后才自主命题的。其中有5-7题单选答案有待商榷。请谨慎参考。
MySQL 常用 SQL 语句全解析与实践总结
本文是软件技术专业学生基于《MySQL数据库技术》课程的知识总结，对常用SQL语句进行系统性梳理。涵盖数据定义语言（DDL，如CREATE、ALTER、DROP ）、数据操作语言（DML，如INSERT、UPDATE、DELETE ）、数据查询语言（DQL，含SELECT基础、WHERE子句、GROUP BY与聚合函数、JOIN关联查询 ）、数据控制语言（DCL，GRANT、REVOKE ）及事务控制语句。介绍各语句的使用规范、应用场景、易错点并附实例，同时总结学习经验，提出对大规模数据优化、高并发事务处理
sql语句优化之SQL Server(详细整理)
这篇文章主要介绍了sql语句优化之SQL Server篇，整理的比较详细，推荐收藏 MS SQL Server查询优化方法 查询速度慢的原因很多，常见如下几种 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据
sql语句优化之SQL Server(优化方案大全)
MS SQL Server查询优化方法 查询速度慢的原因很多，常见如下几种 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 9、返回了不必要的行和列 10、查询语句不好，没有优化可以
MS-SQL Server

34,838

社区成员

254,631

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章