Início / dba / Perguntas / 276815
Accepted
crichavin
Asked: 2020-10-10 11:40:55 +0800 CST

Junte-se para obter o registro mais específico para o inquilino sem PIVOT

  • 772

Eu tenho uma Settingsmesa e uma Tenantmesa. Existe uma hierarquia para que um Accountpode ter 1 ou muitos Companiese um Companypode ter 1 ou muitos 'Instalações'.

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

Eles podem ter uma configuração padrão que se aplica a toda a conta....

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

...exceto que eles têm uma substituição para a Instalação 3 que se aplica apenas à Instalação 3, todas as outras instalações usarão o valor de configuração padrão no nível da conta.

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

Quero criar uma junção entre eles para obter a configuração mais específica para cada locatário. Mais específico é definido como o Settingregistro que corresponde ao Tenant's FacilityIdé mais específico do que uma correspondência em CompanyIdque é mais específica do que uma correspondência em AccountIde, finalmente, se nenhuma correspondência for encontrada, use a configuração que tem NULLpara todos os 3 valores.

Não quero usar o PIVOTrecurso, pois o código usa Entity Framework e LINQ e não há LINQ to SQL para PIVOT. Basicamente, precisa de SQL simples para o qual você possa criar uma visão ... para que não haja tabelas temporárias, etc. Não procurando uma solução de proc armazenada, se possível.

SQLFiddle

Mesa: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 |

Mesa:Tenants

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

Então junte-se a eles teria esta saída desejada:

| 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.

No código, estou fazendo o seguinte para obter o mais específico Settingpara um determinado Tenant, mas agora preciso fazer isso para um grande conjunto de Tenantdados e, portanto, quero fazê-lo por um SQL Join. Para aqueles que não estão familiarizados com LINQ, o double pipe( ||) é equivalente a 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;
}

Aqui está o SQL Fiddle para a resposta

sql-server sql-server-2016

1 respostas

  1. Best Answer

    Obrigado pela ótima configuração de dados. Aqui está uma maneira de obter esses acertos no SQL. Como Aaron mencionou, isso pode ser um CTE sem variáveis.

    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

relate perguntas