T
T-SQL - 根据其他相关记录值获取记录列表(T-SQL - Getting a list of records depending on other related records values)我正在尝试进行查询并需要一些帮助（SQL Server）。
想象一下以下场景：用户正在查看具有多个相关类别的网页。 根据一些规则，如果特定类别与另一个类别放在一起，则不应显示该页面。
为此，我有2个表：
1）有页面ID和相关类别：
Pk CategoryNumber--------------------1 301 311 452 303 213 263 644 254 125 255 315 305 452）规则表。 第一行表示：当查看具有类别30的页面时，如果它也具有45类别，则不应该检索该页面。
WhenViewingCategoryNumber HideEverythingWithCategoryNumber-------------------------------------------------------30 4525 31预期产量：
234我花了几个小时围绕这个，我不会去任何地方，所以如果有人可以提供帮助，我将不胜感激。 如果可能的话，使用SELECT语句将其直接集成到更大的CTE语句中会更好。 非常感谢。
I'm trying to make a query and need a little help (SQL Server).
Imagine the following scenario: user is viewing a web page which has several related categories. According to some rules, the page should not be displayed if a specific category has been put together with another category.
For this I've got 2 tables:
1) Has the page Id and the related categories:
Pk CategoryNumber--------------------1 301 311 452 303 213 263 644 254 125 255 315 305 452) Rules table. First row means: when viewing a page with the category 30 it should not be retrieved if it also has the 45 category.
WhenViewingCategoryNumber HideEverythingWithCategoryNumber-------------------------------------------------------30 4525 31Output expected:
234I've spent a few hours around this and I'm not going anywhere, so I would appreciate if someone could help. If possible, would be better an answer with a SELECT statement to integrate it directly within a larger CTE statement. Many thanks.
最满意答案您可以使用以下查询来标识与冲突类别相关的页面ID：
SELECT DISTINCT c1.PageIdFROM Categories AS c1INNER JOIN Rules AS r ON c1.ItemNumber = r.WhenViewingCategoryNumber INNER JOIN Categories AS c2 ON c1.PageId = c2.PageId AND r.HideEverythingWithCategoryNumber = c2.ItemNumber这将返回：
PageId------15现在只需使用NOT IN即可获得预期结果：
SELECT DISTINCT PageIdFROM Categories WHERE PageId NOT IN ( ... above query here ....)在这里演示
You can use the following query to identify those page ids related to conflicting categories:
SELECT DISTINCT c1.PageIdFROM Categories AS c1INNER JOIN Rules AS r ON c1.ItemNumber = r.WhenViewingCategoryNumber INNER JOIN Categories AS c2 ON c1.PageId = c2.PageId AND r.HideEverythingWithCategoryNumber = c2.ItemNumberThis will return:
PageId------15Now you can get expected result by simply using NOT IN:
SELECT DISTINCT PageIdFROM Categories WHERE PageId NOT IN ( ... above query here ....)Demo here