TSQL：给定分区的最高记录（条件） [英] TSQL : the top records of a given partition (conditional)
问题描述
我需要获取 TABLE_A 中的所有记录，其中至少 2 最后状态 TABLE_B Inspection_Date ）和 Room_ID / code>。
这是我使用的简化表格：
TABLE_A ：
Room_Id状态Inspection_Date
----------- --------------------------
1个空缺2015年5月15日
2个2015年5月21日
2空置1/19/2016
1已占用12/16/2015
4空置3/25/2016
3空置8/27/2015
1空置4 / 17/2016
3空置12/12/2015
3空置3/22/2016
4占用2/2/2015
4空置3/24/2015
TABLE_B ：
房间_Id状态Inspection_Date
------------------------------------
1空置5 / 15/2015
2占用5/21/2015
2空置1/19/2016
1空置12/16/2015
1空置2016/4/17
我的结果应该如下所示：
Room_Id状态Inspection_Date
---------------------------------
3空置8/27/2015
3空置12/12/2015
3空置3/22/2016
4占用2/2/2015
4空置3 / 24/2015
4空置3/25/2016
我试过这种方法，它与这个例子一起工作，但没有处理我的数据......逻辑不完整：
用cteA作为
（
）选择*，Row_Number（）（根据Room_ID分区，根据Inspection_Date的状态顺序）RowNum来自表_A
）
选择* From Table_A Where Room_Id在
（
）中选择Room_Id
从cteA
其中Room_Id不在（从Table_B中选择Room_Id）
和状态='空置'和RowNum> 1
）
按Room_Id，Inspection_Date排序
以下是模式：
CREATE TABLE TABLE_A（`Room_Id` int，
`Status` varchar（55），
`Inspection_Date `日期
）;
INSERT INTO TABLE_A（Room_Id，Status，Inspection_Date）
VALUES（1，'vacant'，'5/15/2015'），
（2，'占用' '2015年5月21日'），
（2，'vacant'，'1/19/2016'），
（1，'占用'，'12 / 16/2015'），
（4，'vacant'，'3/25/2016'），
（3，'vacant'，'8/27/2015'），
（1，'vacant'， '3/17/2016'），
（3，'vacant'，'12 / 12/2015'），
（3，'vacant'，'3/22/2016'），
（4，'占用'，'2/2/2015'），
（4，'vacant'，'3/24/2015'）;
CREATE TABLE TABLE_B（`Room_Id` int，
`Status` varchar（55），
`Inspection_Date` Date
）;
INSERT INTO TABLE_B（Room_Id，Status，Inspection_Date）
VALUES
（1，'vacant'，'5/15/2015'），
（2， '占领'，'5/21/2015'），
（2，'vacant'，'1/19/2016'），
（1，'空白'，'12 / 16/2015 '），
（1，'vacant'，'4/17/2016'）;;
PLAIN
-
对于TABLE_A中的每个房间，选择最后一个日期（作为lastDate）
-
从lastDate获取room_ids，其状态为'vacant'（如lastDateVacant） p>
-
从prevLastDate中获取状态为'vacant'的room_ids（作为prevLastDateVacant）
-
TABLE_A仅拥有lastDateVacant和prevLastDateVacant（inner）中的ID
-
过滤TABLE_A以便只有不在TABLE_B中的ID（left outer + IS NULL ）
-
按照房间排序和按日期排序
-
检查排名为1和2的ID有'空房间状态，按ID分组，并让他们多次出现
选择房间AS（ For each room in TABLE_A select the last date (as lastDate)
for each room in TABLE_A select previous date (as prevLastDate)
Get room_ids from lastDate which has the status 'vacant' (as lastDateVacant)
Get room_ids from prevLastDate which has the status 'vacant' (as prevLastDateVacant)
Filter TABLE_A to have only IDs which are there in lastDateVacant and prevLastDateVacant (inner)
Filter TABLE_A to have only IDs which are not in TABLE_B (left outer + IS NULL)
Ranking with partion by room and or order by date
Check for IDs with rank 1 and 2 having 'vacant' status, grouping by ID and having them occured more than once
在TABLE_A中的每个房间选择以前的日期（如prevLastDate）
结果你有：
<$ p $ （
SELECT room_id AS room，MAX（inspection_date）AS date
FROMTABLE_A
GROUP BY room_id
），prevLastDate AS （
SELECT room_id AS room，MAX（inspection_date）AS日期
FROMTABLE_Aa
INNER JOIN lastDate ON a.room_id = lastDate.room and a.inspection_date< lastDate.date
GROUP BY room_id
），lastDateVacant AS（
SELECT room_id AS房间FROMTABLE_A
WHERE（room_id，inspection_date）IN（
SELECT room， date FROM lastDate
）AND status ='vacant'
），prevLastDateVacant AS（
SELECT room_id AS房间FROMTABLE_A
WHERE（room_id，inspection_date）IN（
SELECT room，date FROM prevLastDate
）AND status ='vacant'
）
SELECT a。* FROMTABLE_Aa
INNER JOIN lastDateVacant
ON a.room_id = lastDateVacant.room
INNER JOIN prevLastDateVacant
ON a.room_id = prevLastDateVacant.room
LEFT OUTER JOINTABLE_BAS b
ON a.room_id = b .room_id
WHERE b.room_id IS NULL
ORDER BY a.room_id ASC，a.inspection_date DESC
窗口函数 不知道TSQL的语法是否相同，但是这里是较短的变体：
select房间from（
）select room_id as room，status，inspection_date as date，
RANK（）OVER（PARTITION BY room_id ORDER BY inspection_date DESC）AS RANK
fromTABLE_A
）
其中（（1,2）和status ='vacant'中的排名）
按房间分组
有计数（）> 1
）
SELECT a。 FROMTABLE_Aa
INNER JOIN room
ON a.room_id = room.room
LEFT OUTER JOINTABLE_BAS b
ON a.room_id = b.room_id
WHERE b.room_id IS NULL
ORDER BY a.room_id ASC，a.inspection_date DESC
I need to get all the records in TABLE_A where at least the 2 last Status are vacant (relative to Inspection_Date) and the Room_ID does not exist in TABLE_B.
This is a simplified table I am using as an example:
TABLE_A:
Room_Id Status Inspection_Date
-------------------------------------
1 vacant 5/15/2015
2 occupied 5/21/2015
2 vacant 1/19/2016
1 occupied 12/16/2015
4 vacant 3/25/2016
3 vacant 8/27/2015
1 vacant 4/17/2016
3 vacant 12/12/2015
3 vacant 3/22/2016
4 occupied 2/2/2015
4 vacant 3/24/2015
TABLE_B:
Room_Id Status Inspection_Date
------------------------------------
1 vacant 5/15/2015
2 occupied 5/21/2015
2 vacant 1/19/2016
1 vacant 12/16/2015
1 vacant 4/17/2016
My result should look like this:
Room_Id Status Inspection_Date
---------------------------------
3 vacant 8/27/2015
3 vacant 12/12/2015
3 vacant 3/22/2016
4 occupied 2/2/2015
4 vacant 3/24/2015
4 vacant 3/25/2016
I have tried it this way, it works with the example but is not working with my data .. the logic is not complete:
With cteA As
(
Select *, Row_Number() Over (Partition By Room_ID, Status Order By Inspection_Date Desc) RowNum From Table_A
)
Select * From Table_A Where Room_Id In
(
Select Room_Id
From cteA
Where Room_Id Not In (Select Room_Id From Table_B)
And Status = 'vacant' And RowNum > 1
)
Order By Room_Id, Inspection_Date
Here is the schema:
CREATE TABLE TABLE_A (`Room_Id` int,
`Status` varchar(55),
`Inspection_Date` Date
);
INSERT INTO TABLE_A (Room_Id, Status, Inspection_Date)
VALUES (1, 'vacant', '5/15/2015'),
(2, 'occupied', '5/21/2015'),
(2, 'vacant', '1/19/2016'),
(1, 'occupied', '12/16/2015'),
(4, 'vacant', '3/25/2016'),
(3, 'vacant', '8/27/2015'),
(1, 'vacant', '4/17/2016'),
(3, 'vacant', '12/12/2015'),
(3, 'vacant', '3/22/2016'),
(4, 'occupied', '2/2/2015'),
(4, 'vacant', '3/24/2015');
CREATE TABLE TABLE_B (`Room_Id` int,
`Status` varchar(55),
`Inspection_Date` Date
);
INSERT INTO TABLE_B (Room_Id, Status, Inspection_Date)
VALUES
(1, 'vacant', '5/15/2015'),
(2, 'occupied', '5/21/2015'),
(2, 'vacant', '1/19/2016'),
(1, 'vacant', '12/16/2015'),
(1, 'vacant', '4/17/2016'),;
PLAIN
As the result you have:
WITH lastDate AS (
SELECT room_id AS room,MAX(inspection_date) AS date
FROM "TABLE_A"
GROUP BY room_id
), prevLastDate AS (
SELECT room_id AS room,MAX(inspection_date) AS date
FROM "TABLE_A" a
INNER JOIN lastDate ON a.room_id = lastDate.room and a.inspection_date < lastDate.date
GROUP BY room_id
), lastDateVacant AS (
SELECT room_id AS room FROM "TABLE_A"
WHERE (room_id,inspection_date) IN (
SELECT room, date FROM lastDate
) AND status = 'vacant'
), prevLastDateVacant AS (
SELECT room_id AS room FROM "TABLE_A"
WHERE (room_id,inspection_date) IN (
SELECT room, date FROM prevLastDate
) AND status = 'vacant'
)
SELECT a.* FROM "TABLE_A" a
INNER JOIN lastDateVacant
ON a.room_id = lastDateVacant.room
INNER JOIN prevLastDateVacant
ON a.room_id = prevLastDateVacant.room
LEFT OUTER JOIN "TABLE_B" AS b
ON a.room_id = b.room_id
WHERE b.room_id IS NULL
ORDER BY a.room_id ASC, a.inspection_date DESC
Window Function
Not sure if the syntax for TSQL is the same, but here is the shorter variant:
WITH room AS (
select room from (
select room_id as room,status,inspection_date as date,
RANK() OVER (PARTITION BY room_id ORDER BY inspection_date DESC) AS RANK
from "TABLE_A"
)
where (rank in ( 1,2) and status = 'vacant')
group by room
having count() > 1
)
SELECT a. FROM "TABLE_A" a
INNER JOIN room
ON a.room_id = room.room
LEFT OUTER JOIN "TABLE_B" AS b
ON a.room_id = b.room_id
WHERE b.room_id IS NULL
ORDER BY a.room_id ASC, a.inspection_date DESC
这篇关于TSQL：给定分区的最高记录（条件）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
