加入以獲得沒有 PIVOT 的租戶的最具體記錄

我有一張Settings桌子和一張Tenant桌子。有一個層次結構，一個Account可以有 1 個或多個Companies，一個Company可以有 1 個或多個“設施”。

Account 1
  ---> Company 1
         ---> Facility 1   
         ---> Facility 2
  ---> Company 2
         ---> Facility 3   
         ---> Facility 4

他們可能有一個適用於整個帳戶的預設設置….

| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|     (null) |    (null) |         1 |            5 |

…除了設施 3 的一個覆蓋僅適用於設施 3 之外，所有其他設施將使用帳戶級別的預設設置值。

| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          3 |    (null) |         1 |            6 |
  

我想在它們之間創建一個連接，以便為每個租戶獲取最具體的設置。**最具體被定義為匹配’s的Setting記錄比匹配更具體的匹配比匹配更具體，最後，如果未找到匹配項，則使用所有 3 個值的設置。Tenant``FacilityId``CompanyId``AccountId``NULL

我不想使用該PIVOT功能，因為程式碼使用實體框架和 LINQ，並且沒有 LINQ to SQLPIVOT. 基本上需要簡單的 SQL，您可以為其創建一個視圖……所以沒有臨時表等。如果可能的話，不要尋找儲存的 proc 解決方案。

SQLFiddle

桌子：Settings

| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          1 |         1 |         1 |            5 |
|     (null) |         2 |         2 |            7 |
|     (null) |         1 |         1 |            4 |
|     (null) |    (null) |         2 |            6 |
|     (null) |    (null) |         1 |            3 |
|     (null) |    (null) |    (null) |            2 |

桌子：Tenants

| FacilityId | CompanyId | AccountId |
|------------|-----------|-----------|
|          1 |         1 |         1 |
|          2 |         2 |         2 |
|          3 |         3 |         3 |

所以加入這些會有這個期望的輸出：

| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          1 |         1 |         1 |            5 |
|          2 |         2 |         2 |            7 | --> this account would match to a setting value of 6 or 7, but the 7 value matches more specifically
|          3 |         3 |         3 |            2 | --> there is no match on Facility, Company, or Account so match to all nulls.

在程式碼中，我正在執行以下操作以獲取Setting給定的最具體資訊Tenant，但我現在需要為大量Tenant數據執行此操作，因此希望通過 SQL 連接來執行此操作。對於不熟悉 LINQ 的人來說，雙管道 ( ||) 等同於OR.

private SettingViewModel GetSettingBy(string strKey)
{
   var allSettings = GetAllSettings();
   var settingQuery = allSettings.Where(x => x.SettingKey == strKey);

   if (_accountCompanyFacilityViewModel.AccountId.HasValue)
   {
       settingQuery = settingQuery.Where(x => (x.AccountId == _accountCompanyFacilityViewModel.AccountId || x.AccountId == null));
   }

   if (_accountCompanyFacilityViewModel.CompanyId.HasValue)
   {
       settingQuery = settingQuery.Where(x => (x.CompanyId == _accountCompanyFacilityViewModel.CompanyId || x.CompanyId == null));
   }

   if (_accountCompanyFacilityViewModel.FacilityId.HasValue)
   {
       settingQuery = settingQuery.Where(x => (x.FacilityId == _accountCompanyFacilityViewModel.FacilityId || x.FacilityId == null));
   }

   var setting = settingQuery
           .OrderByDescending(x => x.FacilityId)
           .ThenByDescending(x => x.CompanyId)
           .ThenByDescending(x => x.AccountId)
           .FirstOrDefault();
           
   return setting;
}

這是答案的SQL Fiddle

感謝您提供出色的數據設置。這是在 SQL 中獲得這些命中的一種方法。正如 Aaron 所提到的，這可以是沒有任何變數的 CTE。

WITH cte AS (
               SELECT  t.AccountId
                       ,t.CompanyId
                       ,t.FacilityId
                       ,CASE
                            WHEN s.AccountId IS NOT NULL THEN 1
                            ELSE 0
                        END + CASE
                                  WHEN s.CompanyId IS NOT NULL THEN 1
                                  ELSE 0
                              END + CASE
                                        WHEN s.FacilityId IS NOT NULL THEN 1
                                        ELSE 0
                                    END AS HitCount
                       ,s.SettingValue
               FROM    dbo.Tenant AS t
                       LEFT OUTER JOIN dbo.Settings AS s ON t.FacilityId LIKE ISNULL(CAST(s.FacilityId AS VARCHAR), '%')
                                                            AND   t.CompanyId LIKE ISNULL(
                                                                                             CAST(s.FacilityId AS VARCHAR)
                                                                                             ,'%'
                                                                                         )
                                                            AND   t.AccountId LIKE ISNULL(
                                                                                             CAST(s.AccountId AS VARCHAR)
                                                                                             ,'%'
                                                                                         )
           )
SELECT  t.AccountId
       ,t.CompanyId
       ,t.FacilityId
       ,t.SettingValue
FROM    cte AS t
       CROSS APPLY (
                       SELECT  MAX(t2.HitCount) AS MaxHits
                       FROM    cte AS t2
                       WHERE   t.AccountId = t2.AccountId
                               AND t.CompanyId = t2.CompanyId
                               AND t.FacilityId = t2.FacilityId
                   ) AS hc
WHERE   1 = 1
       AND hc.MaxHits = t.HitCount;

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