求group by 后满足求差条件后的最小值对应的SQL

Question

表名T1 
数据格式如下.we
C1      C2                  C3
A1      ATIME1           112
A1      ATIME2            321
A1      ATIME3            4324
B1      BTIME1            213
B2      BTIME2            434
D1      DTIME1           55 想拉出数据要求如下
以C1 列分组。
在A1 中
如果ATIME1，ATIME2 ，   ATIME3   时间差小于 5分钟只取最小的那条。如果大于5分钟则几条都要    

Answer 1

先做group by ，然后用T1和group by后的子集联，条件是 C1相等 and （max(c3) - min(c3) > 5分钟）or （C3 = min(c3)）

Answer 2

有点问题没明白,
max(c2) - min(c2) > 5分钟,你只比了最大和最小值,还有中间的忽略了

Answer 3

A1 2014-06-25 13:01:37.827 112
A1 2014-06-26 12:51:37.827 4324
A1 2014-06-26 13:01:37.827 112
A1 2014-06-26 13:07:37.827 321
B1 2014-06-26 13:01:37.827 213
B2 2014-06-26 12:55:37.827 4349
B2 2014-06-26 12:58:37.827 4349
B2 2014-06-26 13:01:37.827 434
D1 2014-06-26 13:01:37.827 55这些数据你要保留哪些呢？

Answer 4

with t2 as(
select (row_number() over (partition by c1 order by c2)) id,*
from t1
),t3 as(
select *,(select DATEDIFF(mi,c2,a.c2) from t2 b where a.id=b.id+1 and a.c1=b.c1) mins
from t2 a)
select *
from t3
where mins>5 or mins is null

Answer 5

楼上的逐条计算时间差，从题目来看是符合的，但楼主后面说了“同一用户在5分钟内的重复登录只算时间最早的那次“，从这情形分析，正确方法是递归条件为C1相同且C3间隔>5分钟的记录的top 1

Answer 6

有个问题请教下.A1   18:00:00
A1   18:01:00
A1    18:08:00你加了行后自关联,然后每条和下条比.是这样吗>
那我第一条和第二条中要保留第一条,你这样会漏吗?

Answer 7

能否给个SQL?C3间隔>5分钟的记录top 1是需要的,但最面的也可能需要

Answer 8

有个问题请教下.A1   18:00:00
A1   18:01:00
A1    18:08:00你加了行后自关联,然后每条和下条比.是这样吗>
是这样的，按时间排序，算出每条和上一条的时间间隔mins
那我第一条和第二条中要保留第一条,你这样会漏吗?
不会漏，因为没组的第一条不存在时间间隔，也就是NULL,最后的条件有mins is null

 不会漏

Answer 9

有个问题请教下.A1   18:00:00
A1   18:01:00
A1    18:08:00你加了行后自关联,然后每条和下条比.是这样吗>
是这样的，按时间排序，算出每条和上一条的时间间隔mins
那我第一条和第二条中要保留第一条,你这样会漏吗?
不会漏，因为没组的第一条不存在时间间隔，也就是NULL,最后的条件有mins is null

 不会漏我刚才举例的,第二条和第三条,时间间隔满足大于5,
用你的方法不是第二条和第三条都出来了,
而实际上第一条和二条的间隔小于我,我就不要第二条了.
总体结果就是只要1,3条

Answer 10

楼上这种抽取方式的结果我在7楼就指出了问题，逐行求差在做法上完全正确，但此处并不适用于楼主所要求情形按你的这种说法,C2查大于5的TOP1 
A1   18:00:00
A1   18:01:00
A1    18:08:00
A1    18:18:00
第二条和第四条之间满足,那第2条不是记下来了?逻辑上应该是两个逐行比较,如果于5记第1条,
同时跳过第2条从第三条开始,
如果大于5也记第1条,然后继续逐行比较.感觉这样的SQL好 难写.我这方面比较菜

Answer 11

你把WHERE条件注释掉就发现了
因为第二条和第一条的时间差mins是1分钟
条件中mins>5，当然过滤掉了
这个是没有where条件的结果
1 A1 2014-6-26 18:00:00 NULL
2 A1 2014-6-26 18:01:00 1
3 A1 2014-6-26 18:08:00 7

Answer 12

我列几种情况,你们看下
A1   18:00:00
A1   18:01:00
A1    18:08:00
A1    18:18:00要1,3,4A1   18:00:00
A1   18:07:00
A1    18:08:00
A1    18:18:00
要1,2,4A1   18:00:00
A1   18:01:00
A1    18:08:00
A1    18:09:00
要1,3A1   18:00:00
A1   18:06:00
A1    18:18:00
A1    18:19:00
要1,2,3不知道我说清楚了米

Answer 13

直接把数据放到第一个CTE里，结果应该就是你要的

Answer 14

直接把数据放到第一个CTE里，结果应该就是你要的好的,
非常感谢.
给我SQL上了一课.语句我再去好好研究下.

Answer 15

楼主我说那条语句是错的你肯定不信，你测试下这条数据就知道我为什么说逐条求差是不满组你要求的
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:01:00
3 A1 2014-6-26 18:05:01 

Answer 16

楼主我说那条语句是错的你肯定不信，你测试下这条数据就知道我为什么说逐条求差是不满组你要求的
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:01:00
3 A1 2014-6-26 18:05:01 
恩,你说的有道理,刚才我正好也在考虑这情况 ,

Answer 17

1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:01:00
3 A1 2014-6-26 18:05:01 你预期的结果是什么呢？

Answer 18

declare @galenkeny table(C1 VARCHAR(10),C2 DATETIME)
insert into @galenkeny
SELECT 'A1', '2014-6-26 18:00:000' union all
select 'A1', '2014-6-26 18:01:000' union all
select 'A1', '2014-6-26 18:08:000' union all
SELECT 'A1', '2014-6-26 18:18:000' ;WITH cte AS 
(SELECT *,rn=ROW_NUMBER()OVER(PARTITION BY C1 ORDER BY C2 DESC ) FROM @galenkeny) SELECT C1,C2
FROM 
(
SELECT *,
       mis=(SELECT  DATEDIFF(mi,g.C2,a.C2) FROM cte g WHERE g.rn=a.rn+1 AND g.C1=a.C1)
  FROM cte a
)v
WHERE v.mis IS NULL OR mis>5
 

Answer 19

用我这租数据
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:04:00
3 A1 2014-6-26 18:08:00 要1,3

Answer 20

思路我早给你了，可惜你只顾看现成sql，完全不理会

Answer 21

用我这租数据
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:04:00
3 A1 2014-6-26 18:08:00 要1,3
要1、3的话，要么就是最大时间和最小时间。要么就row_number取最大序号和最小序号

Answer 22

用我这租数据
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:04:00
3 A1 2014-6-26 18:08:00 要1,3
declare @galenkeny table(C1 VARCHAR(10),C2 DATETIME)
insert into @galenkeny
SELECT 'A1', '2014-6-26 18:00:00' union all
select 'A1', '2014-6-26 18:04:00' union all
select 'A1', '2014-6-26 18:08:00' SELECT C1,
       min(C2)
       FROM @galenkeny
       GROUP BY C1
 UNION ALL
 SELECT C1,
       max(C2)
       FROM @galenkeny
       GROUP BY C1

Answer 23

用我这租数据
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:04:00
3 A1 2014-6-26 18:08:00 要1,3
declare @galenkeny table(C1 VARCHAR(10),C2 DATETIME)
insert into @galenkeny
SELECT 'A1', '2014-6-26 18:00:00' union all
select 'A1', '2014-6-26 18:04:00' union all
select 'A1', '2014-6-26 18:08:00' ;WITH cte AS 
(SELECT *,rn=ROW_NUMBER()OVER(PARTITION BY C1 ORDER BY C2 DESC ) FROM @galenkeny)SELECT C1,C2 FROM  cte
WHERE rn IN(1,3) ORDER BY C2

Answer 24

with t1 as(
select 'A1' c1,'2014-6-26 18:00:00' c2
union all select 'A1','2014-6-26 18:02:00'
union all select 'A1','2014-6-26 18:06:00'
union all select 'A1','2014-6-26 18:08:00'
union all select 'A1','2014-6-26 18:12:00'
union all select 'A1','2014-6-26 18:14:00'
)
你要什么，这样就基本能判断你的需求了

Answer 25

思路我早给你了，可惜你只顾看现成sql，完全不理会我看了阿,问题真不知道要怎么写,
你这种做法是不是也要逐个比较?
A  18:00:00
A  18:01:00
A  18:07:00
A  18:08:002,3比较大于5,是不是第2条也被记了呢?

Answer 26

思路我早给你了，可惜你只顾看现成sql，完全不理会我看了阿,问题真不知道要怎么写,
你这种做法是不是也要逐个比较?
A  18:00:00
A  18:01:00
A  18:07:00
A  18:08:002,3比较大于5,是不是第2条也被记了呢?
当然按照你的需求，是不记第二条的

Answer 27

用我这租数据
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:04:00
3 A1 2014-6-26 18:08:00 要1,3
declare @galenkeny table(C1 VARCHAR(10),C2 DATETIME)
insert into @galenkeny
SELECT 'A1', '2014-6-26 18:00:00' union all
select 'A1', '2014-6-26 18:04:00' union all
select 'A1', '2014-6-26 18:08:00' ;WITH cte AS 
(SELECT *,rn=ROW_NUMBER()OVER(PARTITION BY C1 ORDER BY C2 DESC ) FROM @galenkeny)SELECT C1,C2 FROM  cte
WHERE rn IN(1,3) ORDER BY C2
这个不满足需求

Answer 28

WITH cte as
 (
    SELECT top 1 C1,C2,C3 FROM T1 WHERE C1 = 'A1'
    UNION ALL
    SELECT top 1 a.C1,a.C2,a.C3 FROM T1 a,cte b
    where a.C1 = b.C1 and a.C3 - b.C3 > 5分钟   //时间比较自己写
)
SELECT *  from cte拿去自己改改，没环境测试，我只写了a1数据，如果要其他一起，你自己改 UNION上面代码，用分组排序取每个c1的第一条数据

Answer 29

create table loginlog(UserID bigint, LoginTime datetime)insert into loginlog(UserID, LoginTime)
values
(201401, '2014-06-27 08:00:43'),
(201401, '2014-06-27 08:03:43'),
(201401, '2014-06-27 08:05:43'),
(201401, '2014-06-27 08:08:43'),
(201402, '2014-06-27 08:01:43'),
(201402, '2014-06-27 08:02:43'),
(201402, '2014-06-27 08:07:43'),
(201402, '2014-06-27 08:09:43'),
(201403, '2014-06-27 08:01:43'),
(201403, '2014-06-27 08:04:43'),
(201403, '2014-06-27 08:06:43'),
(201403, '2014-06-27 08:09:43'),
(201401, '2014-06-27 09:00:43'),
(201401, '2014-06-27 09:03:43'),
(201401, '2014-06-27 09:05:43'),
(201401, '2014-06-27 09:08:43'),
(201402, '2014-06-27 09:01:43'),
(201402, '2014-06-27 09:02:43'),
(201402, '2014-06-27 09:07:43'),
(201402, '2014-06-27 09:09:43'),
(201403, '2014-06-27 09:01:43'),
(201403, '2014-06-27 09:04:43'),
(201403, '2014-06-27 09:06:43'),
(201403, '2014-06-27 09:09:43')with cte as (
select UserID, 
   LoginTime, 
   (DATEPART(MINUTE,LoginTime)/5)*5 'GroupID', 
   ROW_NUMBER() over(partition by UserID, (DATEPART(MINUTE,LoginTime)/5)*5, CONVERT(varchar(14), LoginTime) order by LoginTime ) 'Num'
from loginlog)
select UserID, LoginTime from cte where Num = 1

Answer 30

也写了个，应该能满足吧
WITH T1(C1,C2) AS(
SELECT 'A1' ,'2014-6-26 18:00:00' 
UNION ALL SELECT 'A1','2014-6-26 18:02:00'
UNION ALL SELECT 'A1','2014-6-26 18:06:00'
UNION ALL SELECT 'A1','2014-6-26 18:08:00'
UNION ALL SELECT 'A1','2014-6-26 18:12:00'
UNION ALL SELECT 'A1','2014-6-26 18:14:00'
UNION ALL SELECT 'B1','2014-6-23 17:00:00'
UNION ALL SELECT 'B1','2014-6-23 17:01:00'
UNION ALL SELECT 'B1','2014-6-23 17:08:00'
UNION ALL SELECT 'B1','2014-6-23 17:18:00'
UNION ALL SELECT 'C1','2014-6-24 19:00:00'
UNION ALL SELECT 'C1','2014-6-24 19:07:00'
UNION ALL SELECT 'C1','2014-6-24 19:08:00'
UNION ALL SELECT 'C1','2014-6-24 19:18:00'
)
,t2 AS (
SELECT *
,ROW_NUMBER() OVER(ORDER BY c1,c2) AS ID
,ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2) AS ID1
,MIN(c2) OVER(PARTITION BY c1) AS mintime
FROM t1
)
,t3 AS (
SELECT *,CAST(mintime AS DATETIME ) AS c3 FROM t2 WHERE ID=1 AND ID1=1
UNION ALL
    SELECT A.*,CASE WHEN A.c1=B.c1 THEN (CASE WHEN  DATEADD(MINUTE,5,CAST(B.c3 AS DATETIME))>=CAST(A.c2 AS datetime) 
THEN CAST(B.c3 AS datetime) ELSE CAST(A.c2 AS DATETIME) END ) ELSE CAST(A.c2 AS datetime) END
FROM t2 AS A,t3 AS B
WHERE A.ID=B.ID+1  
)
SELECT ID1, c1,c2 
FROM t3 
WHERE c2=c3/*
ID1 c1 c2
1 A1 2014-6-26 18:00:00
3 A1 2014-6-26 18:06:00
5 A1 2014-6-26 18:12:00
1 B1 2014-6-23 17:00:00
3 B1 2014-6-23 17:08:00
4 B1 2014-6-23 17:18:00
1 C1 2014-6-24 19:00:00
2 C1 2014-6-24 19:07:00
4 C1 2014-6-24 19:18:00
*/

Answer 31

用我这租数据
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:04:00
3 A1 2014-6-26 18:08:00 要1,3
declare @galenkeny table(C1 VARCHAR(10),C2 DATETIME)
insert into @galenkeny
SELECT 'A1', '2014-6-26 18:00:00' union all
select 'A1', '2014-6-26 18:04:00' union all
select 'A1', '2014-6-26 18:08:00' ;WITH cte AS 
(SELECT *,rn=ROW_NUMBER()OVER(PARTITION BY C1 ORDER BY C2 DESC ) FROM @galenkeny)SELECT C1,C2 FROM  cte
WHERE rn IN(1,3) ORDER BY C2
这个不满足需求你给出的例子这个是满足你期望结果的。或者你多列出数据，写出正确的期望结果

Answer 32

早上过来，又写了个，用CTE作了个递归，应该可以了吧
with t1 as(
select 'A1' c1,'2014-6-26 18:00:00' c2
union all select 'A1','2014-6-26 18:02:00'
union all select 'A1','2014-6-26 18:05:00'
union all select 'A1','2014-6-26 18:07:00'
union all select 'A1','2014-6-26 18:11:00'
union all select 'A1','2014-6-26 18:12:00'
union all select 'A1','2014-6-26 18:14:00'
union all select 'B1','2014-6-26 18:02:00'
union all select 'B1','2014-6-26 18:06:00'
union all select 'B1','2014-6-26 18:08:00'
union all select 'B1','2014-6-26 18:12:00'
union all select 'B1','2014-6-26 18:14:00'
)
,t2 as(
select (row_number() over (partition by c1 order by c2)) id,*,min(c2) over(partition by c1) c3
from t1
)
,t3 as(
select *
from t2
where id=1
union all
select t2.id,t2.c1,t2.c2,
case when datediff(mi,t3.c3,t2.c2)>=5 then t2.c2 else t3.c3 end
from t2 inner join t3
on t2.c1=t3.c1 and t2.id=t3.id+1
)
select * from t3 where c2=c3 order by c1,c2

Answer 33

都是和最小时间比较的,其实最小时间再变得 
如分钟时 1,3,7,8,9, 11,12
希望结果1,7,9,12
其实在8被 过滤后就是9开始了,应该算的.逻辑伪码大概是这样
DO(string C1 ,string C2,string C3) //记录的一行,C1类型,C2 开始时间,C3 结束时间
{
    //一条条读记录X,Y,Z
    没下条记录跳出  if (X==C1) ,
    if  (Y- C2)< 5,
    {
         C3 = Z    覆盖原来结束时间  
          DO(C1.C2.C3)
    }
    else
    {
          SAVE()保存记录
      DO(C1.Y.Z)    }
} 

Answer 34

都是和最小时间比较的,其实最小时间再变得 
如分钟时 1,3,7,8,9, 11,12
希望结果1,7,9,12
其实在8被 过滤后就是9开始了,应该算的.逻辑伪码大概是这样
DO(string C1 ,string C2,string C3) //记录的一行,C1类型,C2 开始时间,C3 结束时间
{
    //一条条读记录X,Y,Z
    没下条记录跳出  if (X==C1) ,
    if  (Y- C2)< 5,
    {
         C3 = Z    覆盖原来结束时间  
          DO(C1.C2.C3)
    }
    else
    {
          SAVE()保存记录
      DO(C1.Y.Z)    }
} 

9开始了，后面为什么又是12呢，不是要差5啊
看不懂了～～
能不能上20条实际数据和预期结果