如何避免條件連接以編寫 tsql 視圖

我有一個場景，其中有 Roles、Set、DefaultSet 和 CompanySet 表

角色表

RoleName
---------
Admin
Viewer
User

設置表

Id      Name
---     ----
1       Employee Data
2       Help Desk
3       Comments
4       Interests

DefaultSet 表

RoleName   SetId
--------   -----
Admin      1
Admin      2
User       1
User       3

公司設置表

Company   RoleName   SetId
---------   --------   -----
Acme Inc    Admin      1
Acme Inc    Viewer     1
Bobs Paint  User       2
Dans Disks  Admin      2

此查詢有效，但我希望能夠以無參數的方式對其進行重組（‘Acme Inc’ 條件是參數）並且可以在視圖中使用。

WITH T1 AS
(
   SELECT Company, RoleName, cs.SetId  FROM Roles r
   LEFT JOIN CompanySet cs on cs.RoleName = r.RoleName
   GROUP BY Company, r.RoleName, cs.SetId
),
T2 AS 
(
   SELECT  RoleName, ds.SetId  FROM Roles r
       INNER JOIN DefaultSet ds on r.RoleName = ds.RoleName
       GROUP BY r.RoleName, ds.SetId

)
SELECT * FROM t2
   LEFT JOIN t1 ON t1.RoleName = t2.RoleName 
                AND t1.SetId = t2.SetId 
                AND T1.Company = 'Acme Inc'

似乎我可能必須在 t2 表上交叉加入公司，這樣我才能匹配條件。讓我知道是否有辦法解決這個問題。

在我看來，您的查詢可以重寫為：

SELECT R.RoleName, ds.SetID, --T2 Columns
CS.Company, R.RoleName, cs.SetID --T1 Columns
FROM Roles r
   INNER JOIN DefaultSet ds 
       on r.RoleName = ds.RoleName
   LEFT OUTER JOIN CompanySet CS
       ON CS/RoleName = R.RoleName
WHERE CS.Company = 'Acme Inc'

如果是這種情況，您將能夠創建如下視圖：

CREATE VIEW ViewName
AS
BEGIN
   SELECT R.RoleName, 
   ds.SetID,
   CS.Company --, 
   --R.RoleName, --Don't Want To duplicate The Column In The View
   --cs.SetID --Don't Want To duplicate The Column In The View
   FROM Roles r
       INNER JOIN DefaultSet ds 
           on r.RoleName = ds.RoleName
       LEFT OUTER JOIN CompanySet CS
           ON CS/RoleName = R.RoleName
END

然後您就可以Company像往常一樣查詢該列。

SELECT *
FROM ViewName
WHERE Company = 'Acme Inc'

希望這會有所幫助，並且我沒有錯過閱讀您提出的問題。

問題基本上是我有一個預設值表和一些覆蓋預設設置項的公司租戶。換句話說，查看公司設置表中的角色，如果那裡有功能，然後使用它們。因此，如果為某個角色將一行添加到 CompanySet，則該項目將覆蓋 DefaultSet。另一個問題是，當我通過加入實際功能項來擴展功能時，我無法比較這些項。我很難描述我的挑戰，因為這對我來說是全新的。這似乎是一個可行的解決方案，因此希望可以從範例中推斷出一些資訊。

WITH ComFeatures as
(
   SELECT cs.CompanyId, RoleId, FeatureId
   FROM CompanySet cs 
       INNER JOIN SetItem i on i.SetId = cs.SetId
),
ComGroups as
(
   SELECT CompanyId, RoleId FROM ComFeatures
   GROUP BY CompanyId, RoleId
),
ComRecord as
(
   SELECT CompanyId, RoleId,
       STUFF((SELECT ', ' +  CAST(FeatureId as nvarchar(20))
          FROM ComFeatures n 
          WHERE n.RoleId = ComGroups.RoleId  AND n.CompanyId = ComGroups.CompanyId
          FOR XML PATH('')), 1, 2, '') ComFeatures
   FROM ComGroups
),
DefFeatures as
(
   SELECT c.CompanyId, r.RoleId, FeatureId
   FROM aspnet_Roles r 
       LEFT JOIN DefaultSet cs on cs.RoleId = r.RoleId
       CROSS JOIN (SELECT Id AS CompanyId FROM Company) c
       INNER JOIN SetItem i on i.SetId = cs.SetId 
),
DefGroup as 
(
   SELECT CompanyId, RoleId FROM DefFeatures
   GROUP BY CompanyId, RoleId
),
DefRecord as
(
   SELECT CompanyId, RoleId,
       STUFF((SELECT ', ' +  CAST(FeatureId as nvarchar(20))
          FROM DefFeatures n 
          WHERE n.RoleId = DefGroup.RoleId  AND n.CompanyId = DefGroup.CompanyId 
          FOR XML PATH('')), 1, 2, '') DefFeatures
   FROM DefGroup
),
Record as 
(
   SELECT d.CompanyId ,c.ComFeatures, d.DefFeatures, d.RoleId  From DefRecord d
       LEFT JOIN ComRecord c on c.CompanyId = d.CompanyId AND c.RoleId = d.RoleId
),
FeatureList as 
(
   select CompanyId, RoleId,  COALESCE(ComFeatures, DefFeatures) Features from Record
),
Final as 
(
   select CompanyId,RoleId, [ss].element from FeatureList 
   CROSS APPLY dbo.SplitToInt(Features, ',') as [ss]
   EXCEPT
   SELECT CompanyId,RoleId, FeatureId FROM DisabledCompanyFeature c WHERE c.CompanyId = CompanyId AND c.RoleId = RoleId
)
SELECT *  FROM Final

這似乎是我一直在尋找的答案。我不得不向下折疊行並使用 FOR XML PATH 加入單行以進行比較，然後在比較後展開逗號分隔值。結果查詢的選擇中的 Coalesce 首先從公司集合中按優先順序獲取項目，然後如果沒有匹配項，則最終獲取預設集合。

引用自：https://dba.stackexchange.com/questions/241732