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

本文介绍了如何检查 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:With EXISTS:select t.* from tablename twhere 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.numcolfrom ( select *, row_number() over (order by startdate desc) rn1, row_number() over (order by numcol) rn2 from tablename) twhere rn1 > rn2请参阅演示. 这篇关于如何检查 SQL 记录是否按特定顺序排列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！