求一个SQL，连续重复的取第一条。

xzzxg 2013-05-03 10:27:52
如下表，连续字段a相同的记录，取第一条

表内容：
id a crDate
1 112.50 2013-05-01 08:10:01.000
2 112.50 2013-05-01 08:10:11.000
5 100.60 2013-05-01 08:15:01.000
8 100.60 2013-05-01 08:20:01.000
13 100.60 2013-05-01 08:21:01.000
26 112.50 2013-05-01 08:22:01.000
35 112.50 2013-05-01 08:25:01.000
40 90.50 2013-05-01 08:40:01.000
41 90.50 2013-05-01 08:42:01.000
52 90.50 2013-05-01 08:42:41.000
55 100.60 2013-05-01 08:50:01.000
70 100.60 2013-05-01 08:51:01.000
71 100.60 2013-05-01 08:53:01.000
72 100.60 2013-05-01 08:55:01.000
77 90.50 2013-05-01 09:03:01.000
78 90.50 2013-05-01 09:04:01.000

需要结果：
1 112.50 2013-05-01 08:10:01.000
5 100.60 2013-05-01 08:15:01.000
26 112.50 2013-05-01 08:22:01.000
40 90.50 2013-05-01 08:40:01.000
55 100.60 2013-05-01 08:50:01.000
77 90.50 2013-05-01 09:03:01.000



create table #t

(id int, a decimal(8,2), crDate datetime )



insert into #t

select 1, 112.5, '2013-05-01 08:10:01' union all

select 2, 112.5, '2013-05-01 08:10:11' union all

select 5, 100.6, '2013-05-01 08:15:01' union all

select 8, 100.6, '2013-05-01 08:20:01' union all

select 13, 100.6, '2013-05-01 08:21:01' union all

select 26, 112.5, '2013-05-01 08:22:01' union all

select 35, 112.5, '2013-05-01 08:25:01' union all

select 40, 90.5, '2013-05-01 08:40:01' union all

select 41, 90.5, '2013-05-01 08:42:01' union all

select 52, 90.5, '2013-05-01 08:42:41' union all

select 55, 100.6, '2013-05-01 08:50:01' union all

select 70, 100.6, '2013-05-01 08:51:01'  union all

select 71, 100.6, '2013-05-01 08:53:01'  union all

select 72, 100.6, '2013-05-01 08:55:01'  union all

select 77, 90.5, '2013-05-01 09:03:01'  union all

select 78, 90.5, '2013-05-01 09:04:01'
...全文
651 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
xzzxg 2013-05-04
  • 打赏
  • 举报
 回复
引用 17 楼 ap0405140 的回复:
SQL2000的方法.. 

create table #t
(id int, a decimal(8,2), crDate datetime )
 
insert into #t
select 1, 112.5, '2013-05-01 08:10:01' union all
select 2, 112.5, '2013-05-01 08:10:11' union all
select 5, 100.6, '2013-05-01 08:15:01' union all
select 8, 100.6, '2013-05-01 08:20:01' union all
select 13, 100.6, '2013-05-01 08:21:01' union all
select 26, 112.5, '2013-05-01 08:22:01' union all
select 35, 112.5, '2013-05-01 08:25:01' union all
select 40, 90.5, '2013-05-01 08:40:01' union all
select 41, 90.5, '2013-05-01 08:42:01' union all
select 52, 90.5, '2013-05-01 08:42:41' union all
select 55, 100.6, '2013-05-01 08:50:01' union all
select 70, 100.6, '2013-05-01 08:51:01'  union all
select 71, 100.6, '2013-05-01 08:53:01'  union all
select 72, 100.6, '2013-05-01 08:55:01'  union all
select 77, 90.5, '2013-05-01 09:03:01'  union all
select 78, 90.5, '2013-05-01 09:04:01' 


select identity(int,1,1) 'rn',id,a,crDate into #t2 from #t

select x.id,x.a,x.crDate
 from #t2 x
 left join #t2 y on x.rn=y.rn+1
 where y.rn is null or x.a<>y.a

/*
id          a                                       crDate
----------- --------------------------------------- -----------------------
1           112.50                                  2013-05-01 08:10:01.000
5           100.60                                  2013-05-01 08:15:01.000
26          112.50                                  2013-05-01 08:22:01.000
40          90.50                                   2013-05-01 08:40:01.000
55          100.60                                  2013-05-01 08:50:01.000
77          90.50                                   2013-05-01 09:03:01.000

(6 row(s) affected)
*/
44万的表上 1秒能跑出来。
xzzxg 2013-05-04
  • 打赏
  • 举报
 回复
引用 19 楼 Beirut 的回复:
[quote=引用 18 楼 xzzxg 的回复:] [quote=引用 17 楼 ap0405140 的回复:] SQL2000的方法.. 

create table #t
(id int, a decimal(8,2), crDate datetime )
 
insert into #t
select 1, 112.5, '2013-05-01 08:10:01' union all
select 2, 112.5, '2013-05-01 08:10:11' union all
select 5, 100.6, '2013-05-01 08:15:01' union all
select 8, 100.6, '2013-05-01 08:20:01' union all
select 13, 100.6, '2013-05-01 08:21:01' union all
select 26, 112.5, '2013-05-01 08:22:01' union all
select 35, 112.5, '2013-05-01 08:25:01' union all
select 40, 90.5, '2013-05-01 08:40:01' union all
select 41, 90.5, '2013-05-01 08:42:01' union all
select 52, 90.5, '2013-05-01 08:42:41' union all
select 55, 100.6, '2013-05-01 08:50:01' union all
select 70, 100.6, '2013-05-01 08:51:01'  union all
select 71, 100.6, '2013-05-01 08:53:01'  union all
select 72, 100.6, '2013-05-01 08:55:01'  union all
select 77, 90.5, '2013-05-01 09:03:01'  union all
select 78, 90.5, '2013-05-01 09:04:01' 


select identity(int,1,1) 'rn',id,a,crDate into #t2 from #t

select x.id,x.a,x.crDate
 from #t2 x
 left join #t2 y on x.rn=y.rn+1
 where y.rn is null or x.a<>y.a

/*
id          a                                       crDate
----------- --------------------------------------- -----------------------
1           112.50                                  2013-05-01 08:10:01.000
5           100.60                                  2013-05-01 08:15:01.000
26          112.50                                  2013-05-01 08:22:01.000
40          90.50                                   2013-05-01 08:40:01.000
55          100.60                                  2013-05-01 08:50:01.000
77          90.50                                   2013-05-01 09:03:01.000

(6 row(s) affected)
*/
唐诗 效率快。[/quote] 帮测一下我写的怎么样[/quote] 1700笔的1秒搞定，， 刚换到44万的表上，10分钟了还在跑，就按下了停止。可能子查询比较多
xzzxg 2013-05-04
  • 打赏
  • 举报
 回复
引用 19 楼 Beirut 的回复:
[quote=引用 18 楼 xzzxg 的回复:] [quote=引用 17 楼 ap0405140 的回复:] SQL2000的方法.. 

create table #t
(id int, a decimal(8,2), crDate datetime )
 
insert into #t
select 1, 112.5, '2013-05-01 08:10:01' union all
select 2, 112.5, '2013-05-01 08:10:11' union all
select 5, 100.6, '2013-05-01 08:15:01' union all
select 8, 100.6, '2013-05-01 08:20:01' union all
select 13, 100.6, '2013-05-01 08:21:01' union all
select 26, 112.5, '2013-05-01 08:22:01' union all
select 35, 112.5, '2013-05-01 08:25:01' union all
select 40, 90.5, '2013-05-01 08:40:01' union all
select 41, 90.5, '2013-05-01 08:42:01' union all
select 52, 90.5, '2013-05-01 08:42:41' union all
select 55, 100.6, '2013-05-01 08:50:01' union all
select 70, 100.6, '2013-05-01 08:51:01'  union all
select 71, 100.6, '2013-05-01 08:53:01'  union all
select 72, 100.6, '2013-05-01 08:55:01'  union all
select 77, 90.5, '2013-05-01 09:03:01'  union all
select 78, 90.5, '2013-05-01 09:04:01' 


select identity(int,1,1) 'rn',id,a,crDate into #t2 from #t

select x.id,x.a,x.crDate
 from #t2 x
 left join #t2 y on x.rn=y.rn+1
 where y.rn is null or x.a<>y.a

/*
id          a                                       crDate
----------- --------------------------------------- -----------------------
1           112.50                                  2013-05-01 08:10:01.000
5           100.60                                  2013-05-01 08:15:01.000
26          112.50                                  2013-05-01 08:22:01.000
40          90.50                                   2013-05-01 08:40:01.000
55          100.60                                  2013-05-01 08:50:01.000
77          90.50                                   2013-05-01 09:03:01.000

(6 row(s) affected)
*/
唐诗 效率快。[/quote] 帮测一下我写的怎么样[/quote] 效率也很快，1秒搞定。
csdn_风中雪狼 2013-05-04
  • 打赏
  • 举报
 回复
都是大牛。。。
雨雪纷纷 2013-05-04
  • 打赏
  • 举报
 回复
楼主，我不会，我是来拿分的
xzzxg 2013-05-03
  • 打赏
  • 举报
 回复
引用 9 楼 OrchidCat 的回复:
DECLARE @T TABLE(iid INT IDENTITY(1,1),id INT ,a INT,crdate DATETIME)

INSERT INTO @t(id,a,crdate)
SELECT id,a,crdate FROM dbo.TB



SELECT  id ,
        a ,
        crdate
FROM    @T
WHERE   iid IN ( SELECT ( SELECT    MAX(iid)
                          FROM      @T AS B
                          WHERE     B.iid <= A.iid
                                    AND b.a = a.a
                                    AND NOT EXISTS ( SELECT *
                                                     FROM   @T AS C
                                                     WHERE  B.iid - 1 = C.iid
                                                            AND B.a = C.a )
                        ) AS grp
                 FROM   @T AS A )



/*
id	a	crdate
1	112	2013-05-01 08:10:01.000
5	100	2013-05-01 08:15:01.000
26	112	2013-05-01 08:22:01.000
40	90	2013-05-01 08:40:01.000
55	100	2013-05-01 08:50:01.000
77	90	2013-05-01 09:03:01.000*/
效率有点慢，1800条记录耗时5秒， 实际应用大概在100w条记录
GodisaAVman 2013-05-03
  • 打赏
  • 举报
 回复
CREATE PROCEDURE PRC_ReadDistinctData AS BEGIN create table #test(id int,a decimal(8,2),createTime datetime) declare @a_before decimal(8,2), --保存上一条数据的a @id int,--这一条数据的id @a decimal(8,2), --这一条数据的a @crDate datetime ----这一条数据的crDate set @a_before = '-1314.15926' --第一次循环时没有@a_before,初始化一个数据（这个数据绝对不会重复有木有） declare Ctemp cursor local for select id,a,crDate from test open Ctemp fetch next from Ctemp into @id,@a,@crDate while(@@fetch_status=0) begin if(@a!=@a_before)--和上一条数据不一样 begin set @a_before = @a insert into #test(id,a,createTime) values (@id,@a_before,@crDate) end fetch next from Ctemp into @id,@a,@crDate end close Ctemp deallocate Ctemp select * from #test END
黄_瓜 2013-05-03
  • 打赏
  • 举报
 回复
其实这是老问题了， 跟取间断数一样的本质。 除了双重否定式， 还有聚合分组法，等等。
黄_瓜 2013-05-03
  • 打赏
  • 举报
 回复
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t go create table #t (id int, a decimal(8,2), crDate datetime ) insert into #t select 1, 112.5, '2013-05-01 08:10:01' union all select 2, 112.5, '2013-05-01 08:10:11' union all select 5, 100.6, '2013-05-01 08:15:01' union all select 8, 100.6, '2013-05-01 08:20:01' union all select 13, 100.6, '2013-05-01 08:21:01' union all select 26, 112.5, '2013-05-01 08:22:01' union all select 35, 112.5, '2013-05-01 08:25:01' union all select 40, 90.5, '2013-05-01 08:40:01' union all select 41, 90.5, '2013-05-01 08:42:01' union all select 52, 90.5, '2013-05-01 08:42:41' union all select 55, 100.6, '2013-05-01 08:50:01' union all select 70, 100.6, '2013-05-01 08:51:01' union all select 71, 100.6, '2013-05-01 08:53:01' union all select 72, 100.6, '2013-05-01 08:55:01' union all select 77, 90.5, '2013-05-01 09:03:01' union all select 78, 90.5, '2013-05-01 09:04:01' SELECT * FROM #t AS t WHERE NOT EXISTS ( SELECT 1 FROM #t AS b WHERE b.a = t.a AND b.id < t.id AND NOT EXISTS ( SELECT 1 FROM #t AS c WHERE c.a != t.a AND c.id < t.id AND c.id > b.id ) ) /* id a crDate 1 112.50 2013-05-01 08:10:01.000 5 100.60 2013-05-01 08:15:01.000 26 112.50 2013-05-01 08:22:01.000 40 90.50 2013-05-01 08:40:01.000 55 100.60 2013-05-01 08:50:01.000 77 90.50 2013-05-01 09:03:01.000 */
daiyueqiang2045 2013-05-03
  • 打赏
  • 举报
 回复
引用 11 楼 xzzxg 的回复:
[quote=引用 9 楼 OrchidCat 的回复:] 
DECLARE @T TABLE(iid INT IDENTITY(1,1),id INT ,a INT,crdate DATETIME)

INSERT INTO @t(id,a,crdate)
SELECT id,a,crdate FROM dbo.TB



SELECT  id ,
        a ,
        crdate
FROM    @T
WHERE   iid IN ( SELECT ( SELECT    MAX(iid)
                          FROM      @T AS B
                          WHERE     B.iid <= A.iid
                                    AND b.a = a.a
                                    AND NOT EXISTS ( SELECT *
                                                     FROM   @T AS C
                                                     WHERE  B.iid - 1 = C.iid
                                                            AND B.a = C.a )
                        ) AS grp
                 FROM   @T AS A )



/*
id	a	crdate
1	112	2013-05-01 08:10:01.000
5	100	2013-05-01 08:15:01.000
26	112	2013-05-01 08:22:01.000
40	90	2013-05-01 08:40:01.000
55	100	2013-05-01 08:50:01.000
77	90	2013-05-01 09:03:01.000*/
嗯，测试成功，就是要用个临时表， 这样的话要用存储过程了。[/quote] 确实
xzzxg 2013-05-03
  • 打赏
  • 举报
 回复
引用 9 楼 OrchidCat 的回复:
DECLARE @T TABLE(iid INT IDENTITY(1,1),id INT ,a INT,crdate DATETIME)

INSERT INTO @t(id,a,crdate)
SELECT id,a,crdate FROM dbo.TB



SELECT  id ,
        a ,
        crdate
FROM    @T
WHERE   iid IN ( SELECT ( SELECT    MAX(iid)
                          FROM      @T AS B
                          WHERE     B.iid <= A.iid
                                    AND b.a = a.a
                                    AND NOT EXISTS ( SELECT *
                                                     FROM   @T AS C
                                                     WHERE  B.iid - 1 = C.iid
                                                            AND B.a = C.a )
                        ) AS grp
                 FROM   @T AS A )



/*
id	a	crdate
1	112	2013-05-01 08:10:01.000
5	100	2013-05-01 08:15:01.000
26	112	2013-05-01 08:22:01.000
40	90	2013-05-01 08:40:01.000
55	100	2013-05-01 08:50:01.000
77	90	2013-05-01 09:03:01.000*/
嗯，测试成功，就是要用个临时表， 这样的话要用存储过程了。
xzzxg 2013-05-03
  • 打赏
  • 举报
 回复
引用 7 楼 ZaoLianBuXiQi 的回复:
你排下序 select * from #t order by a
不能这样排序，要按ID排升序，比如场景是这样： 记录一个高度的变化，如果高度不变，一段连续时间内的值都是一样的， 这些一样的值只取一条。 但如果曾经也出现过一样的高度(不连续，中间变化过其它的高度)，也要取出来。
Mr_Nice 2013-05-03
  • 打赏
  • 举报
 回复 
DECLARE @T TABLE(iid INT IDENTITY(1,1),id INT ,a INT,crdate DATETIME)

INSERT INTO @t(id,a,crdate)
SELECT id,a,crdate FROM dbo.TB



SELECT  id ,
        a ,
        crdate
FROM    @T
WHERE   iid IN ( SELECT ( SELECT    MAX(iid)
                          FROM      @T AS B
                          WHERE     B.iid <= A.iid
                                    AND b.a = a.a
                                    AND NOT EXISTS ( SELECT *
                                                     FROM   @T AS C
                                                     WHERE  B.iid - 1 = C.iid
                                                            AND B.a = C.a )
                        ) AS grp
                 FROM   @T AS A )



/*
id	a	crdate
1	112	2013-05-01 08:10:01.000
5	100	2013-05-01 08:15:01.000
26	112	2013-05-01 08:22:01.000
40	90	2013-05-01 08:40:01.000
55	100	2013-05-01 08:50:01.000
77	90	2013-05-01 09:03:01.000*/
MrYangkang 2013-05-03
  • 打赏
  • 举报
 回复
你看id的值 1和26 5和55
MrYangkang 2013-05-03
  • 打赏
  • 举报
 回复
你排下序
select * from #t order by a

MrYangkang 2013-05-03
  • 打赏
  • 举报
 回复
如果你要的结果是你给的那种，那真不知道怎么分组
xzzxg 2013-05-03
  • 打赏
  • 举报
 回复
引用 3 楼 ZaoLianBuXiQi 的回复:
还是这样
不是这样， 只要是不连续的，哪怕有重复也出来。
xzzxg 2013-05-03
  • 打赏
  • 举报
 回复
引用 2 楼 ZaoLianBuXiQi 的回复:
你确定要的结果是这个吗、？
是的，有什么问题吗？
MrYangkang 2013-05-03
  • 打赏
  • 举报
 回复
还是这样
MrYangkang 2013-05-03
  • 打赏
  • 举报
 回复
你确定要的结果是这个吗、？
加载更多回复（5）
sql如何对某一个重复字段只取其第一个数据
最近需要写个存储过程对一些数据进行处理，需求类似于这样的： 有一个张表（order）记录的是用户购买商品的数据，字段为用户id，用户购买商品的名称，购买时间，这样的话里面的用户id和商品名称肯定就会有重复数据，那么对重复的数据只需要取其第一条记录即可。 那么这时候就可以对其使用下面的函数对数据进行分组排序 ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2) 上面的函数表示根据COL1字段对数据进行分组，在分组内部再根据 COL2字段进行排序，而此函数计算的
oracle相同数据取第一条,oracle去除重复, 获取取最新的第一条数据
最近项目中的一条sql语句涉及到取一个表中相同工单编码中最新的一条数据，在网上看到一函数完美解决此问题，如下是内容要点：问题：在项目中有一张设备检测信息表DEVICE_INFO_TBL, 每个设备每天都会产生一条检测信息，现在需要从该表中检索出每个设备的最新检测信息。也就是device_id字段不能重复，消除device_id字段重复的记录，而且device_id对应的检测信息test_resul...
Sql中如何实现连续相同的数据只取第一条
WITH c1(ID, 记录值) AS ( SELECT 1, 3000 UNION ALL SELECT 2, 3200 UNION ALL SELECT 3, 3500 UNION ALL SELECT 4, 3500 UNION ALL SELECT 5, 3700 UNION ALL SELECT 6, 3900 UNION ALL SELECT 7, 3900 UNION ALL SE...
Oracle数据库sql语句处理业务表重复数据取其一
oracle数据库sql语句重复数据取其一
sql求31到第40条数据(id不连续)
sql求31到第40条数据(id不连续) --利用开窗函数,并将其当作结果集 查询其31到第40条 select* from (select *,ROW_NUMBER() over(order by id) 排序 from Product) A where A.排序 between 31 and 40; --先查询前三十条,在查询十条 并且id不再这三十条中 select top(10) * from Product where id not in(select top(30) Id from Prod
MS-SQL Server

34,590

社区成员

254,589

社区内容

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

加载中

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

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

+ 用AI写文章