如何使用row_number创建一个新字段，在两条记录之间的差值上返回true或false

Question

我有一个铁路道口数据库表，在许多其他列的顶部有列CrossingID、ReasonID和LastUpdated。

每次数据库更新该交叉点时，每个CrossingID都有几个穿越时间的记录。我想按CrossingID获取最新的两条记录，按LastUpdated排序，并创建一个新字段，该字段可以发现其他字段之间的任何差异，并返回true或false。

我想好了如何使用row_number和with语句进行分组，因为我不知道如何创建任何差异的检查字段。到目前为止，我得到的是：

WITH CTE AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY crossingid ORDER BY lastupdated DESC) AS RowNo, 
crossingid, lastupdated, reportstatus, reasonid
FROM
statecrossingdata_5year
)
SELECT
crossingid, lastupdated, rowno, reportstatus, reasonid
FROM
cte 
WHERE
rowno <= 2 
ORDER BY 
crossingid, rowno

铁路数据库是公共记录，可以在这里找到：https://safetydata.fra.dot.gov/OfficeofSafety/publicsite/DownloadCrossingInventoryData.aspx

Answer 1

WITH CTE AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY crossingid ORDER BY lastupdated DESC) AS RowNo, 
crossingid, lastupdated, reportstatus, reasonid
FROM
statecrossingdata_5year
),
CTE2 as
(
SELECT
crossingid, lastupdated, rowno, reportstatus, reasonid
FROM
cte 
WHERE
rowno <= 2 
)
SELECT x.*,
CASE WHEN x.reasonID = y.reasonID THEN 'TRUE' ELSE 'FALSE' END
FROM CTE2 x
INNER JOIN CTE2 y
ON x.crossingID = y.crossingID
WHERE X.rowno = 2 AND y.rowno = 1