英文:
SQL Server find entries existing in all distinct periods (or sequences)
问题
我有一个表 Y，我曾经插入了 room_ID、时间段和一个序列（希望能够简化问题）。
我想找到在所有不同时间段（或序列，如果这更容易）中存在的 room_ID。
在下面的示例中，room_ID 2 是唯一的。谢谢。
DECLARE @y TABLE
(
room_id numeric(10,0) null,
date_from datetime null,
date_to datetime null,
seq int null
)
INSERT @y
SELECT 2, '2023-05-01','2023-05-02',1
INSERT @y
SELECT 5, '2023-05-01','2023-05-02',1
INSERT @y
SELECT 8, '2023-05-01','2023-05-02',1
INSERT @y
SELECT 2, '2023-05-02','2023-05-03',2
INSERT @y
SELECT 8, '2023-05-02','2023-05-03',2
INSERT @y
SELECT 2, '2023-05-07','2023-05-09',3
英文:
I have a table Y where I once off insert room_ID, the period and a sequence (in the hope of making the problem easier).
I want to find the room_IDs that exist in all different periods (or sequences if this is easier)
In the example below, room_ID 2 is the one. Thank you
DECLARE @y TABLE
(room_id numeric(10,0) null,
date_from datetime null,
date_to datetime null,
seq int null)
INSERT @y
SELECT 2, '2023-05-01','2023-05-02',1
INSERT @y
SELECT 5, '2023-05-01','2023-05-02',1
INSERT @y
SELECT 8, '2023-05-01','2023-05-02',1
INSERT @y
SELECT 2, '2023-05-02','2023-05-03',2
INSERT @y
SELECT 8, '2023-05-02','2023-05-03',2
INSERT @y
SELECT 2, '2023-05-07','2023-05-09',3
答案1
得分: 1
以下是您要翻译的内容：
如果您有一组唯一的期间/seq 值，那么您可以简单地执行以下操作：
SELECT y.room_id
FROM y
GROUP BY
y.room_id
HAVING COUNT_BIG(*) = (SELECT COUNT_BIG(*) FROM Period);
您还可以合成总数：
SELECT y.room_id
FROM y
GROUP BY
y.room_id
HAVING COUNT_BIG(*) = (SELECT COUNT_BIG(DISTINCT y2.seq) FROM y AS y2);
英文:
If you have a list of unique periods/seq values then you can simply do
SELECT y.room_id
FROM y
GROUP BY
y.room_id
HAVING COUNT_BIG(*) = (SELECT COUNT_BIG(*) FROM Period);
You can also synthesize the total
SELECT y.room_id
FROM y
GROUP BY
y.room_id
HAVING COUNT_BIG(*) = (SELECT COUNT_BIG(DISTINCT y2.seq) FROM y AS y2);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论