代码之家 › 专栏 › 技术社区 › TSG

在MySQL中无序列出记录

sequence sql mysql

2

TSG · 技术社区 · 7 年前

+--------+------------+
| 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 |
+--------+------------+

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

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

3 回复 | 直到 7 年前

1

0

Gordon Linoff 7 年前

您正在按分区 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;

2

0

Taher A. Ghaleb 7 年前

这个带有子查询的简单查询(假设 TSTAMP DATE 栏):

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

HERE . 希望能有帮助。

3

0

forpas 7 年前

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