如何在MySQL5.7中实现行号功能?

pn9klfpd  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(540)

表名:表1列:(id,ref\u id,timestamp)

  1. |**id**|**ref_id**| **timestamp** |
  2. | 1 | 111 | 2020-04-27 02:59:45 |
  3. | 2 | 111 | 2020-04-28 02:59:45 |
  4. | 3 | 111 | 2020-04-26 02:59:45 |

我想实现一些

  1. DELETE
  2. FROM (
  3. SELECT *,
  4. ROW_NUMBER() OVER(PARTITION BY red_id ORDER BY TIMESTAMP DESC) AS rn
  5. FROM table_one
  6. ).
  7. s
  8. WHERE r.rn != 1;

执行上述删除查询后的表1应该如下所示

  1. |**id**|**ref_id**| **timestamp**
  2. | 2 | 111 | 2020-04-28 02:59:45 |

我试图选择需要删除的行,但没有得到正确的行号。请在下面找到我的问题:

  1. SET @row_number : = 0;
  2. SELECT *
  3. FROM (
  4. SELECT @row_number: = CASE
  5. WHEN @ci = ref_id THEN @row_number + 1
  6. ELSE 1
  7. END AS rn,
  8. @ci: = ref_id ref_id,
  9. id,
  10. TIMESTAMP
  11. FROM table_one
  12. ORDER BY
  13. TIMESTAMP DESC

提前谢谢。

q3qa4bjr

q3qa4bjr1#

  1. DELETE t1.*
  2. FROM table_one t1, table_one t2
  3. WHERE t1.ref_id = t2.ref_id
  4. AND t1.`timestamp` < t2.`timestamp`

或者在连接语法中相同

  1. DELETE t1.*
  2. FROM table_one t1
  3. JOIN table_one t2 ON t1.ref_id = t2.ref_id
  4. WHERE t1.`timestamp` < t2.`timestamp`

小提琴

jogvjijk

jogvjijk2#

使用 join :

  1. delete t
  2. from table_one t join
  3. (select tt.ref_id, max(tt.timestamp) as max_ts
  4. from table_one tt
  5. group by tt.ref_id
  6. ) tt
  7. on tt.ref_id = t.ref_id
  8. where t.timestamp < tt.max_ts;

不需要窗口函数。

相关问题