获取记录的先前值并将其与当前值一起显示

huangapple
go评论101阅读模式
英文:

Get previous value of a record and display it along with the current

问题

以下是翻译好的内容：

我有一个显示房间（room_id）条件变化（condition_id）和发生时间（date_registered）的表格。SQL Server 2008-R2。

我想要构建一个查询，用于给定日期范围内显示每个房间的房间条件的变化，以及变化发生在其前一次条件变化之后。

以下是一个示例：

  1. DECLARE @tbl TABLE (
  2.     room_id			numeric(10,0)	null,
  3.     condition_id	numeric(10,0)	null,
  4.     date_registered	datetime		null
  5. );
  7. INSERT @tbl (room_id, condition_id, date_registered)
  8. VALUES
  9. (1,2,'2018-12-07 08:37:19.300'),
  10. (2,1,'2018-12-08 08:37:19.300'),
  11. (1,3,'2018-12-09 08:37:19.300'),
  12. (2,2,'2018-12-10 08:37:19.300'),
  13. (1,1,'2018-12-11 08:37:19.300');

我希望最终得到的结果如下所示：

  1. room_id old_condition_id	condition_id	date_registered
  2. 1		2					3				2018-12-09 08:37:19.300
  3. 2		1					2				2018-12-10 08:37:19.300
  4. 1       3                   1               2018-12-11 08:37:19.300

谢谢。

英文:

I have a table that displays the changes in Conditions (condition_id) of Rooms (room_id) and when did it happened (date_registered).
SQL Server 2008-R2.

I want to build a query that for a given date period displays the changes in room conditions of every room that changed next to its previous condition

Here is an example

  1. DECLARE @tbl TABLE (
  2.     room_id			numeric(10,0)	null,
  3.     condition_id	numeric(10,0)	null,
  4.     date_registered	datetime		null
  5. );
  7. INSERT @tbl (room_id, condition_id, date_registered)
  8. VALUES
  9. (1,2,'2018-12-07 08:37:19.300'),
  10. (2,1,'2018-12-08 08:37:19.300'),
  11. (1,3,'2018-12-09 08:37:19.300'),
  12. (2,2,'2018-12-10 08:37:19.300')
  13. (1,1,'2018-12-11 08:37:19.300');

I would like to end up with a result like that:

  1. room_id old_condition_id	condition_id	date_registered
  2. 1		2					3				2018-12-09 08:37:19.300
  3. 2		1					2				2018-12-10 08:37:19.300
  4. 1       3                   1               2018-12-11 08:37:19.300

Thank you

答案1

得分: 1

在没有LAG()函数的情况下，我们可以尝试使用ROW_NUMBER()进行自连接：

  1. WITH cte AS (
  2.     SELECT *, ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered) rn
  3.     FROM @tbl
  4. )
  6. SELECT
  7.     t1.room_id,
  8.     t1.condition_id AS old_condition_id,
  9.     t2.condition_id,
  10.     t2.date_registered
  11. FROM cte t1
  12. INNER JOIN cte t2
  13.     ON t2.room_id = t1.room_id AND
  14.        t2.rn = t1.rn + 1 AND
  15.        t2.condition_id <> t1.condition_id
  16. ORDER BY
  17.     t1.date_registered;

获取记录的先前值并将其与当前值一起显示

Demo

英文:

In the absence of LAG(), we can instead try a self join with the help of ROW_NUMBER():

<!-- language: sql -->

  1. WITH cte AS (
  2.     SELECT *, ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered) rn
  3.     FROM @tbl
  4. )
  6. SELECT
  7.     t1.room_id,
  8.     t1.condition_id AS old_condition_id,
  9.     t2.condition_id,
  10.     t2.date_registered
  11. FROM cte t1
  12. INNER JOIN cte t2
  13.     ON t2.room_id = t1.room_id AND
  14.        t2.rn = t1.rn + 1 AND
  15.        t2.condition_id &lt;&gt; t1.condition_id
  16. ORDER BY
  17.     t1.date_registered;

获取记录的先前值并将其与当前值一起显示

<h2>Demo</h2>

答案2

得分: 1

我能够使用一个CTE来实现所需的结果，我已经在2008兼容级别上进行了测试，它可以正常工作。

  1. DECLARE @StartDate datetime = '2018-12-07 00:00:00.000';
  2. DECLARE @EndDate datetime = '2018-12-12 23:59:59.997';
  4. WITH RoomChanges AS (
  5.     SELECT
  6.         room_id,
  7.         condition_id,
  8.         date_registered,
  9.         ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered ASC) AS rn
  10.     FROM @tbl
  11.     WHERE date_registered >= @StartDate
  12.       AND date_registered <= @EndDate
  13. )
  14. SELECT
  15.     rc.room_id,
  16.     prev.condition_id AS old_condition_id,
  17.     rc.condition_id,
  18.     rc.date_registered
  19. FROM RoomChanges rc
  20. LEFT JOIN RoomChanges prev ON rc.room_id = prev.room_id AND rc.rn = prev.rn + 1
  21. WHERE rc.rn > 1
  22. ORDER BY rc.date_registered;
英文:

I was able to achieve the required result with a CTE I have tested it on 2008 compliantly level and it works.

  1. DECLARE @StartDate datetime = &#39;2018-12-07 00:00:00.000&#39;;
  2. DECLARE @EndDate datetime = &#39;2018-12-12 23:59:59.997&#39;;
  4. WITH RoomChanges AS (
  5.     SELECT
  6.         room_id,
  7.         condition_id,
  8.         date_registered,
  9.         ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered ASC) AS rn
  10.     FROM @tbl
  11.     WHERE date_registered &gt;= @StartDate
  12.       AND date_registered &lt;= @EndDate
  13. )
  14. SELECT
  15.     rc.room_id,
  16.     prev.condition_id AS old_condition_id,
  17.     rc.condition_id,
  18.     rc.date_registered
  19. FROM RoomChanges rc
  20. LEFT JOIN RoomChanges prev ON rc.room_id = prev.room_id AND rc.rn = prev.rn + 1
  21. WHERE rc.rn &gt; 1
  22. ORDER BY rc.date_registered;

huangapple
  • 本文由 发表于 2023年6月22日 16:29:41
  • 转载请务必保留本文链接：https://go.coder-hub.com/76529955.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定