英文:
Check if a group belonging to another table has set of certain ID's
问题
这是您提供的 SQL 查询的翻译结果：
这是您提供的 SQL 查询的翻译结果：
-- 这是您的查询，用于查找包含符合条件的 S.StatusID 和 OS.StatusID 的记录。
-- 表 S 是主表，包含 S.StatusID 列，该列应等于 4（已完成状态），当另一个表 OS 的状态等于多个数字之一时。
-- 这些 OS.StatusID 的可能值为 4、5、7 或 8。
Select Distinct S.SID
From STable S
left Join OSTable OS On S.SID = OS.SID
Where S.StatusID = 3
Group By S.SubmissionID
Having Count(CASE OS.StatusID WHEN 4 THEN 1 END) > 0
and Count(CASE OS.StatusID WHEN 6 THEN 1 END) > 0
and Count(CASE OS.StatusID WHEN 7 THEN 1 END) > 0
and Count(CASE OS.StatusID WHEN 8 THEN 1 END) > 0
这个查询旨在查找符合特定条件的记录，其中 S 表中的 S.StatusID 等于 3，并且关联的 OS 表中的 StatusID 等于 4、6、7 或 8。然后使用 GROUP BY 子句按 SubmissionID 进行分组，然后使用 HAVING 子句来确保每个组中都存在符合条件的记录。
请注意，这个查询还考虑了 WHERE 子句中的 S.StatusID = 3 条件，所以只有当 S.StatusID 为 3 时，才会考虑在内。您可以根据需要调整查询中的条件来满足您的需求。
英文:
Here's the scenario, I'm trying to get a set of IDs in one table that contain certain statuses in another.
Table S is this main table. Table S has a column called S.StatusID which should be at the number 4 (Completed Status) when the other table OS, has its status equal one of multiple numbers. These OS.StatusID's are 4, 5, 7 or 8.
Table S can contain one or more of OS, so if there were 3 OS's in Table S, for S to be considered at Completed Status (4), all the OS Statuses should be any of the OS statuses of 4, 6, 7 or 8.
| S ID | OS Status |
|---|---|
| 1600 | 6 |
| 1600 | 8 |
| 1600 | 4 |
So, in that table, S would be considered at Completed Status (4).
Now, in our website while the code works in setting everything correctly now. Some tables in the DB do not match this standard, and we want to find all the S ID's that are not at S.StatusID 4 and force an DB call to change these values to the correct status.
This is my current attempt, but it doesn't consider them as a whole group to work:
-- Additional note, anything before S.StatusID 3 an OS will not exist and anything > 3 is already complete or expired making it irrelevant, hence the Where clause.
Select Distinct S.SID
From STable S
left Join OSTable OS On S.SID = OS.SID
Where S.StatusID = 3
Group By S.SubmissionID
Having Count(CASE OS.StatusID WHEN 4 THEN 1 END) > 0
and Count(CASE OS.StatusID WHEN 6 THEN 1 END) > 0
and Count(CASE OS.StatusID WHEN 7 THEN 1 END) > 0
and Count(CASE OS.StatusID WHEN 8 THEN 1 END) > 0
I have found some similar threads (https://stackoverflow.com/questions/39922045/tsql-find-if-all-records-in-a-group-have-the-same-value, https://stackoverflow.com/questions/44653833/sql-check-if-group-containes-certain-values-of-given-column-oracle?newreg=d3c70b497bda4dd883ac1e3dce9ae4cb) but I can't find one that answers my question with multiple things being checked to match.
Just to be clear, I want to find any S IDs that contain OS's at OS.Status 4, 6, 7 or 8, but are not at S.Status 4.
How could I write this SQL?
Thanks!
答案1
得分: 0
“所有 x 是 y”的逻辑语句等同于“没有 x 不是 y”。您需要的逻辑大致为 NOT EXISTS(OS WHERE OS.Status NOT IN (...))。
类似这样的：
SELECT *
FROM STable S
WHERE S.Status = 3
AND NOT EXISTS (
SELECT *
FROM OSTable OS
WHERE OS.SID = S.SID
AND OS.Status NOT IN (4, 6, 7, 8)
)
请注意，如果恰好没有匹配的 OS 记录，条件仍然为真。
英文:
The logic statement "all x are y" is equivalent to "no x is not y". The logic you need then is roughly NOT EXISTS(OS WHERE OS.Status NOT IN (...)).
Something like:
SELECT *
FROM STable S
WHERE S.Status = 3
AND NOT EXISTS (
SELECT *
FROM OSTable OS
WHERE OS.SID = S.SID
AND OS.Status NOT IN (4, 6, 7, 8)
)
Note that if by chance there are no matching OS records at all, the condition is still true.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论