如何检查 SQL 记录是否按特定顺序排列

问题描述：

我无法弄清楚如何检查表上的记录是否按特定顺序排列.简化的表设计本质上是这样的:

I'm having trouble figuring out how I can check if records on a table are in a specific order. The simplified table design is essentially this:

+------------+----------------+--------+
| ID (GUID)  |   StartDate    | NumCol |
+------------+----------------+--------+
| CEE8C17... | 8/17/2019 3:11 |     22 |
| BB22001... | 8/17/2019 3:33 |     21 |
| 4D40B12... | 8/17/2019 3:47 |     21 |
| 3655125... | 8/17/2019 4:06 |     20 |
| 3456CD1... | 8/17/2019 4:22 |     20 |
| 38BAF92... | 8/17/2019 4:40 |     19 |
| E60CBE8... | 8/17/2019 5:09 |     19 |
| 5F2756B... | 8/17/2019 5:24 |     18 |
+------------+----------------+--------+

ID 列是非顺序 GUID.当输入数据时，表格默认按 StartDate 排序.但是，我试图标记 NumCol 值不按降序排列的实例.NumCol 值在相邻记录上可以相同，但最终它们必须是降序的.

The ID column is a non-sequential GUID. The table is sorted by default on the StartDate when data is entered. However I am trying to flag instances where the NumCol values are out of descending order. The NumCol values can be identical on adjacent records, but ultimately they must be descending.

+--------+
| NumCol |
+--------+
|     22 |
|    *20 | <- I want the ID where this occurs
|     21 |
|     20 |
|     20 |
|     19 |
|     19 |
|     18 |
+--------+

我已经尝试了 LEFT JOIN 这个表本身，但似乎无法想出一个 ON 子句来给出正确的结果:

I've tried LEFT JOIN this table to itself, but can't seem to come up with an ON clause that gives the right results:

ON a.ID b.ID AND a.NumCol >b.NumCol

我还认为我可以使用 OFFSET n ROWS 将默认排序表与对其执行的 ORDER BY NumCol 进行比较.我想不出任何有用的东西.

I also thought I could use OFFSET n ROWS to compare the default sorted table against one with an ORDER BY NumCol performed on it. I can't come up with anything that works.

我需要一个适用于 SQL Server 和 SQL Compact 的解决方案.

I need a solution that will work for both SQL Server and SQL Compact.

答

With EXISTS:

select t.* from tablename t
where exists (
  select 1 from tablename
  where numcol > t.numcol and startdate > t.startdate
)

或者用 row_number() 窗口函数:

Or with row_number() window function:

select t.id, t.startdate, t.numcol
from (
  select *,
    row_number() over (order by startdate desc) rn1,
    row_number() over (order by numcol) rn2
  from tablename 
) t
where rn1 > rn2

请参阅演示.

如何检查 SQL 记录是否按特定顺序排列

相关推荐