list记录在mysql中顺序错误

ezykj2lf 于 2021-06-19 发布在 Mysql

关注(0)|答案(3)|浏览(357)

我有一张简单的table

+--------+------------+
| REFNUM | TSTAMP     |
+--------+------------+
| 1      | 2018-JUN-1 |
| 2      | 2018-JUN-3 |
| 3      | 2018-JAN-1 |
| 4      | 2018-JUN-4 |
| 5      | 2018-JUN-6 |
| 6      | 2018-JAN-2 |
+--------+------------+

3号和6号记录的日期顺序不一致。我想要一个查询从我的表中返回refnum的3和6。基于类似的问题，我构建了一个查询：

SELECT REFNUM FROM
(
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY REFNUM ORDER BY REFNUM  ) AS sequenceCorrect,
    ROW_NUMBER() OVER (PARTITION BY REFNUM ORDER BY TSTAMP) AS sequenceActual
  FROM queries
)
  AS yourTableSequenced
WHERE
  sequenceCorrect <> sequenceActual
ORDER BY REFNUM

但我总是得到一个空的结果。有人能解释一下为什么以及如何改正吗？

sql mysql sequence

来源：https://stackoverflow.com/questions/53381124/list-records-out-of-sequence-in-mysql

3条答案

按热度按时间

t98cgbkg1#

你的分区依据 refnum ，这似乎是独一无二的。这意味着两个值总是 1 .

SELECT q.*
FROM (SELECT q.*,
             ROW_NUMBER() OVER (ORDER BY REFNUM ) AS sequenceCorrect,
             ROW_NUMBER() OVER (ORDER BY TSTAMP) AS sequenceActual
      FROM queries q
     ) q
WHERE sequenceCorrect <> sequenceActual
ORDER BY REFNUM;

您也可以使用单个窗口函数来执行此操作：

SELECT q.*
FROM (SELECT q.*,
             MAX(TSTAMP) OVER (ORDER BY REFNUM) as MAX_TSTAMP
      FROM queries q
     ) q
WHERE MAX_TSTAMP <> TSTAMP
ORDER BY REFNUM;

编辑：
如果你特别想要那些顺序颠倒的，那么使用 lag() :

SELECT q.*
FROM (SELECT q.*,
             LAG(TSTAMP) OVER (ORDER BY REFNUM) as PREV_TSTAMP
      FROM queries q
     ) q
WHERE PREV_TSTAMP > TSTAMP
ORDER BY REFNUM;

赞(0）回复(0）举报 2021-06-19

wvmv3b1j2#

这个带有子查询的简单查询（假设 TSTAMP 是一个 DATE 列）：

SELECT REFNUM
FROM queries AS outer_query
WHERE TSTAMP < (SELECT TSTAMP FROM queries WHERE REFNUM = outer_query.REFNUM-1);

在这里试试。希望有帮助。

赞(0）回复(0）举报 2021-06-19

nimxete23#

假设id之间可能存在间隙，通过检查当前tstamp是否小于前一个id的tstamp：

SELECT *  FROM queries q WHERE 
    (q.TSTAMP < 
    (SELECT qq.TSTAMP FROM queries qq WHERE REFNUM = 
    (SELECT MAX(REFNUM) FROM queries WHERE REFNUM < q.REFNUM)))

赞(0）回复(0）举报 2021-06-19

我来回答

list记录在mysql中顺序错误

3条答案

相关问题

热门标签

最新问答