英文:
Get previous value of a record and display it along with the current
问题
以下是翻译好的内容：
我有一个显示房间（room_id）条件变化（condition_id）和发生时间（date_registered）的表格。SQL Server 2008-R2。
我想要构建一个查询，用于给定日期范围内显示每个房间的房间条件的变化，以及变化发生在其前一次条件变化之后。
以下是一个示例：
DECLARE @tbl TABLE (
room_id numeric(10,0) null,
condition_id numeric(10,0) null,
date_registered datetime null
);
INSERT @tbl (room_id, condition_id, date_registered)
VALUES
(1,2,'2018-12-07 08:37:19.300'),
(2,1,'2018-12-08 08:37:19.300'),
(1,3,'2018-12-09 08:37:19.300'),
(2,2,'2018-12-10 08:37:19.300'),
(1,1,'2018-12-11 08:37:19.300');
我希望最终得到的结果如下所示：
room_id old_condition_id condition_id date_registered
1 2 3 2018-12-09 08:37:19.300
2 1 2 2018-12-10 08:37:19.300
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
DECLARE @tbl TABLE (
room_id numeric(10,0) null,
condition_id numeric(10,0) null,
date_registered datetime null
);
INSERT @tbl (room_id, condition_id, date_registered)
VALUES
(1,2,'2018-12-07 08:37:19.300'),
(2,1,'2018-12-08 08:37:19.300'),
(1,3,'2018-12-09 08:37:19.300'),
(2,2,'2018-12-10 08:37:19.300')
(1,1,'2018-12-11 08:37:19.300');
I would like to end up with a result like that:
room_id old_condition_id condition_id date_registered
1 2 3 2018-12-09 08:37:19.300
2 1 2 2018-12-10 08:37:19.300
1 3 1 2018-12-11 08:37:19.300
Thank you
答案1
得分: 1
在没有LAG()函数的情况下，我们可以尝试使用ROW_NUMBER()进行自连接：
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered) rn
FROM @tbl
)
SELECT
t1.room_id,
t1.condition_id AS old_condition_id,
t2.condition_id,
t2.date_registered
FROM cte t1
INNER JOIN cte t2
ON t2.room_id = t1.room_id AND
t2.rn = t1.rn + 1 AND
t2.condition_id <> t1.condition_id
ORDER BY
t1.date_registered;
Demo
英文:
In the absence of LAG(), we can instead try a self join with the help of ROW_NUMBER():
<!-- language: sql -->
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered) rn
FROM @tbl
)
SELECT
t1.room_id,
t1.condition_id AS old_condition_id,
t2.condition_id,
t2.date_registered
FROM cte t1
INNER JOIN cte t2
ON t2.room_id = t1.room_id AND
t2.rn = t1.rn + 1 AND
t2.condition_id <> t1.condition_id
ORDER BY
t1.date_registered;
答案2
得分: 1
我能够使用一个CTE来实现所需的结果，我已经在2008兼容级别上进行了测试，它可以正常工作。
DECLARE @StartDate datetime = '2018-12-07 00:00:00.000';
DECLARE @EndDate datetime = '2018-12-12 23:59:59.997';
WITH RoomChanges AS (
SELECT
room_id,
condition_id,
date_registered,
ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered ASC) AS rn
FROM @tbl
WHERE date_registered >= @StartDate
AND date_registered <= @EndDate
)
SELECT
rc.room_id,
prev.condition_id AS old_condition_id,
rc.condition_id,
rc.date_registered
FROM RoomChanges rc
LEFT JOIN RoomChanges prev ON rc.room_id = prev.room_id AND rc.rn = prev.rn + 1
WHERE rc.rn > 1
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.
DECLARE @StartDate datetime = '2018-12-07 00:00:00.000';
DECLARE @EndDate datetime = '2018-12-12 23:59:59.997';
WITH RoomChanges AS (
SELECT
room_id,
condition_id,
date_registered,
ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered ASC) AS rn
FROM @tbl
WHERE date_registered >= @StartDate
AND date_registered <= @EndDate
)
SELECT
rc.room_id,
prev.condition_id AS old_condition_id,
rc.condition_id,
rc.date_registered
FROM RoomChanges rc
LEFT JOIN RoomChanges prev ON rc.room_id = prev.room_id AND rc.rn = prev.rn + 1
WHERE rc.rn > 1
ORDER BY rc.date_registered;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论