希望只用sql就能做到。我有一个返回包含time_stamp和hash_index列的数据集的查询。基本上是这样的东西:
1 1583365548 6ff11ad5536f28d66098f6d74f97d877
2 1583365554 6ff11ad5536f28d66098f6d74f97d877
3 1583365556 6ff11ad5536f28d66098f6d74f97d877
4 1583365562 a2e99acb2540d49955ef93fb2684ac25
5 1583365571 a2e99acb2540d49955ef93fb2684ac25
6 1583365572 a2e99acb2540d49955ef93fb2684ac25
7 1583365574 a2e99acb2540d49955ef93fb2684ac25
8 1583365578 a2e99acb2540d49955ef93fb2684ac25
9 1583365580 a2e99acb2540d49955ef93fb2684ac25我想要做的是进一步过滤这个查询,以便只在hash_index与前一条记录不同的情况下才包括该记录。这是我可以做的事情,而不必将其转储到PHP中并遍历它吗?
我现在的问题如下:
SELECT
(@cnt:=@cnt + 1) AS row_number,
time_stamp,
MD5(GROUP_CONCAT(CONCAT(user_state_name,
option_id,
option_code,
item_id,
item_code))) AS hash_index
FROM
user_state
WHERE
user_id = 2
GROUP BY
time_stamp;发布于 2020-03-27 03:07:45
像这样的查询有什么用:
SELECT t1.*
FROM user_state t1
LEFT JOIN user_state t2 ON t1.id-1 = t2.id
WHERE t1.hash_index <> t2.hash_index;发布于 2020-03-27 05:19:50
如果你想过滤掉相邻的重复项,我只需要使用lag(),省去了散列和聚合:
SELECT us.*
FROM (SELECT us.*,
LAG(time_stamp) OVER (PARTITION BY user_id ORDER BY time_stamp) as prev_ts,
LAG(time_stamp) OVER (PARTITION BY user_id, user_state_nae, option_id, option_code, item_id, item_code ORDER BY time_stamp) as prev_ts_values
FROM user_state us
WHERE user_id = 2
) t
WHERE prev_ts_values is null or prev_ts_values <> prev_ts;您可以选择所需的任何行。
https://stackoverflow.com/questions/60874321
复制相似问题