sql查询两条记录的时间差

今天突然想到了一个需求，即在一张带有id和time字段的表中，查询相邻时间的时间差。

表的记录如下：

表名为wangxin

id是一个不重复的字符串，time是一个时间戳。

现在的需求如下：

比如id分别有wangxin1到wangxin4的几个椅子，小王同学，先坐上wangxin1的椅子，然后坐了几秒后，又坐到了编号为wangxin2的椅子上，然后一会儿又换到了wangxin3的椅子上，最后坐到wangxin4的椅子上，问，分别在每一个椅子上坐的时间。最后一个默认为0.

需求解决思路

想要知道差值，那就意味着必须把时间都比一下，可是只有一张表怎么办，就只能使用子查询的方式，利用内连接，获取到笛卡尔积，即一个时间点对应四个时间点，这样一来，就方便对其进行一个运算了。

-- 第一步，创建笛卡尔积
SELECT
    w1.*, w2.*
FROM
    wangxin w1
INNER JOIN (SELECT w3.time FROM wangxin w3) w2 ;

使用内连接的方式创建笛卡尔积，如下所示，这是用来方便计算两个时间点的差值的：

-- 第二步，进行运算
SELECT
    w1.id, w1.time,w2.time-w1.time as time_diff
FROM
    wangxin w1
INNER JOIN (SELECT w3.time FROM wangxin w3) w2 ;

这一步运算就是为了计算出两次的时间差，可见，我们需要的是相近时间点的差值，并且因为是差值，所以只能大于0，故我们第三步整理中，以上一步的结果作为一个新的表，再进行一次select操作，同时我们以time字段分组，并且取time_diff字段非0以外的最小值。

-- 第三步，整理
SELECT
        t1.id,
        t1.time,
MIN(t1.time_diff) AS time_diff
FROM
        (
SELECT
                w1.id,
                w1.time,
                w2.time - w1.time AS time_diff
FROM
                wangxin w1
INNER JOIN (SELECT w3.time FROM wangxin w3) w2
        ) t1
WHERE
        t1.time_diff > 0
GROUP BY
        t1.time

这时候，我们发现了咦，为什么wangxin4这个记录没有了，原来是因为我们上面采用的是时间差大于0的情况，由于wangxin4后面就没有新的时间戳了，所以无法计算时间差，故其时间差为0，在上一步的where语句中被略去。

最后我们再加上一不外链接

-- 加回原来的表中
SELECT
    t.*, IFNULL(t2.time_diff, 0) as '时间差'
FROM
    wangxin t
LEFT JOIN (
SELECT
        t1.id,
        t1.time,
MIN(t1.time_diff) AS time_diff
FROM
        (
SELECT
                w1.id,
                w1.time,
                w2.time - w1.time AS time_diff
FROM
                wangxin w1
INNER JOIN (SELECT w3.time FROM wangxin w3) w2
        ) t1
WHERE
        t1.time_diff > 0
GROUP BY
        t1.time
) t2 ON t.id = t2.id

这一步外链接里面需要注意的是，因为上一次的表中没有id为wangxin4这个记录，所以在左外链接的情况下，就会变成null，因此我们在select语句中，需要对这种情况做一个判断，判断是否为null，如果是null的话，就让这个单元格设置为0

最终结果为：

结果和想象中的一样。

总结：

之前一直觉得sql很简单，随便写写就行了，但是今天接触了一个非常复杂的sql，顿时觉得自己先前真的太幼稚了，sql相对于java更贴近算法，从书写到优化，发现sql是一门非常厉害的技术，自愧不如、自愧不如哇！

需要学习的东西还很多，与君共勉！

秒客网

sql查询两条记录的时间差

相关文章