主页 / dba / 问题 / 276815
Accepted
crichavin
Asked: 2020-10-10 11:40:55 +0800 CST

加入以获得没有 PIVOT 的租户的最具体记录

  • 772

我有一张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 个值的设置。TenantFacilityIdCompanyIdAccountIdNULL

我不想使用该PIVOT功能，因为代码使用实体框架和 LINQ，并且没有 LINQ to SQL 用于PIVOT. 基本上需要简单的 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-server sql-server-2016

1 个回答

  1. Best Answer

    感谢您提供出色的数据设置。这是在 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;
    • 1

相关问题