求万能的 V2EX 拯救我吧~小白问一条初级 SQL

table: id(primary key), card_num, process_time
不断有记录写进table，记录写入其写进数据库的时间为process_time
求SQL: 输出所有某同一card_num的每相邻两次记录的process_time大于一秒的记录

例如：第一条卡号码121212 20140808 08:08:08
第二条卡号码343434 20140808 08:09:08
第三条卡号码565656 20140808 08:10:08
第四条卡号码121212 20140808 08:11:08
e.g.第一条与第四条为同一张卡，20140808 08:08:08 到 20140808 08:11:08 大于一秒 ---》 so符合要求,最终输出这两条

号码

输出

V2EX

15 replies • 2014-12-02 04:24:59 +08:00

RemRain

Dec 1, 2014

SELECT * FROM xxx
然后在程序里面搞定

zts1993

Dec 1, 2014

字查询和join 应该都可以

select a.id,b.id FROM xxx a,xxx b where a.id='卡id' AND b.id='卡id' and b.process_time > a.process_time +1

注意时间用函数替换一下。

这只是个解决思路不是最优解，应该能找到更高效的写法，仅供参考。。

错了不要喷我。

phoneli

Dec 1, 2014

感觉这样的逻辑业务。不应该在用sql占用数据库。你就select ＊出来，然后用shell处理一下。

jianghu52

Dec 1, 2014

我好奇的是相邻两条这个要求怎么在sql里面体现出来

Automan

Dec 1, 2014

其实你只要DISTINCT就可以了。。因为只要是不同的时间，那肯定是大于一秒

msg7086

Dec 1, 2014

数据库里哪来相邻……

7654

Dec 1, 2014

这样解决吧，数据库应该只提供数据，不做分析

beginor

Dec 1, 2014

对 MySql 不熟悉，不过感觉用游标应该可以解决的；

如果是 SQL Server 的话，除了游标之外，可以使用 LEAD/LAG 函数来解决 http://msdn.microsoft.com/zh-cn/library/hh231256.aspx

loading

Dec 1, 2014 via Android

全选出来，order by id，process_time
然后在程序里判断就好了。

hcymk2

Dec 1, 2014

select a.id ,b.id ,a.card_num from
(select id ,process_time,card_num, @num1 := @num1 + 1 as row_number
from (SELECT @num1:=1) r1, you_table_name order by card_num,process_time ) a ,
(select id ,process_time,card_num, @num2 := @num2 + 1 as row_number
from (SELECT @num2:=0) r2, you_table_name order by card_num,process_time) b
where a.row_number=b.row_number and a.card_num=b.card_num and b.process_time-a.process_time>1;

献丑了。

ixiaohei

Dec 1, 2014

题主，如果想简单就@zts1993 说的来，不过表记录不多还好，多了效率超级低。

最好还是@RemRain 说的，查出来自己程序里面处理，如果频繁的话，建议缓存

oojiayu

Dec 1, 2014

@hcymk2
@ixiaohei
@zts1993
@RemRain
你们都是数据库牛人，膜拜啊~

jucelin

Dec 1, 2014 via Android

可以考虑提前预处理数据，例如插入前就计算出与上一次的时间差算放进库里去。

lu18887

Dec 2, 2014 via iPhone

楼上说程序处理的各位仁兄，当数据量足够大，计算要求足够复杂的时候数据库的SQL还是不错的选择，前提是要能写对。

jinghli

Dec 2, 2014

这是典型的SQL window function.不算是太小白的问题。我简单写了个postgresql可用的。

SELECT
card_num
FROM
(
SELECT
lag(process_time) OVER (PARTITION BY card_num ORDER BY process_time) AS prev_process_time,
card_num,
process_time
FROM
your_table
) x
WHERE
prev_process_time is NULL --not sure whether you need this
OR (process_time - prev_process_time > '1 second'::interval)