Início / dba / Perguntas / 190786
Accepted
SQLserving
Asked: 2017-11-14 08:54:14 +0800 CST

Unindo várias tabelas com requisitos complicados

  • 772

Tenho 3 tabelas assim:

Mestre: PK: SPID

SPID| ProjectID|DesignMix|ProjectType|Date    |ContentType  
1   |123      |AB-12    |New Proj   |1/1/2015|CT_1  
2   |145      |AR-13    |New Proj   |2/1/2015|CT_2  
3   |423      |AB-13    |New Proj   |1/1/2015|CT_3

Detalhes: PK: há um ID de coluna de identidade

SPID|ProjectID|Length|TenthReading  
1   |123      |0.1   |43  
1   |123      |0.1   |45  
1   |123      |0.1   |46  
1   |123      |0.1   |55  
1   |123      |0.1   |59   
1   |123      |0.060 |120  
2   |145      |0.1   |130  
2   |145      |0.1   |45  
2   |145      |0.1   |46  
2   |145      |0.1   |55  
2   |145      |0.1   |59   
2   |145      |0.080 |140   
3   |423      |0.077 |43  
3   |423      |0.1   |45  
3   |423      |0.1   |46  
3   |423      |0.1   |155  
3   |423      |0.1   |59   
3   |423      |0.080 |99

MaterialType: PK é um ID de coluna de identidade

ProjectID|DesignMix|Material  |Perc|ContentType  
123      |AB-12    |Concrete  |20  |CT_1  
123      |AB-12    |Limestone |60  |CT_1  
123      |AB-15    |Concrete  |20  |CT_1  
145      |AR-13    |Concrete  |20  |CT_2
145      |AR-13    |Concrete  |70  |CT_2
423      |AB-13    |Limestone |80  |CT_3

As especificações da consulta são estas:
1. Join master e tabela de detalhes com base em SPID
2. Join master e tabela de materiais com base em ProjectID e DesignMix
3. Se um projectID e DesignMix específicos tiverem várias linhas em MaterialTable, por exemplo. Concrete and Limestone for ID=123, então deve ser mesclado com o nome 'Mixed'
4. Todos os dados da tabela Master e dados relevantes da tabela Details and Materials, ou seja, junções à esquerda preferidas especialmente entre Master e Materials
5. A data deve ser o ano 2015 e o Tipo de Projeto deve ser 'Novo Projeto'

A consulta resultante deve me dar algo assim: 

ContentType|Material |CountLength|SumLength|AvgR |MinR|MaxR|CountRgreater95|SumLengthgreater95  
CT_1       |Mixed    |6          |0.56     |61.33|43  |120 |1              |0.06  
CT_2       |Concrete |6          |0.58     |79.16|45  |140 |2              |0.18  
CT_3       |Limestone|6          |0.557    |74.5 |43  |155 |2              |0.18

Aqui está um link DBFiddle fornecendo definições de tabela de teste

Esta é a consulta que escrevi até agora, mas não está me dando os resultados corretos:

Select
Distinct
z.ContentType

,z.Material
,sum(CountLength) over (partition by Material,ContentType order by ContentType) CountLength
,sum(SumLength) over (partition by Material,ContentType order by ContentType) SumLength
,sum(AvgR) over (partition by Material,ContentType order by ContentType) AvgR
,sum(MinR) over (partition by Material,ContentType order by ContentType)MinR
,sum(MaxR) over (partition by Material,ContentType order by ContentType)MaxR

,CountRgreater95
,SumLengthgreater95 
From
(
Select 
x.ContentType
,CountLength
,SumLength
, AvgR
,MinR
,MaxR
,x.ProjectID
,x.DesignMix
,Coalesce(y.Material,x.Material) Material
,CountRgreater95
,SumLengthgreater95
From 
(Select Distinct
c.ContentType
,CountLength
,SumLength
, AvgR
,MinR
,MaxR
,c.ProjectID
,c.DesignMix
,c.Material


,CountRgreater95
,SumLengthgreater95

from
(
select distinct a.* ,b.Material,max(b.Perc) over (partition by b.Material,b.DesignMix order by a.ProjectID) Perc from  (SELECT a.ProjectID,a.DesignMix,a.ContentType, COUNT(b.Length) AS CountLength, SUM(b.Length) AS SumLength, CONVERT(int, ROUND(AVG(CONVERT(decimal(6, 2), b.TenthReading)), 0)) AS AvgR, MIN(b.TenthReading) AS MinR, MAX(b.TenthReading) AS MaxR, c.CountRgreater95, 
        c.SumLengthgreater95
FROM            dbo.Master AS a INNER JOIN
dbo.Details AS b ON a.SPID = b.SPID INNER JOIN
(SELECT        x0.ContentType, COUNT(x.Length) AS CountRgreater95, SUM(x.Length) AS SumLengthgreater95
FROM            dbo.Master AS x0 INNER JOIN
dbo.Details AS x ON x0.SPID = x.SPID
WHERE        (x.TenthReading >= 95) AND (x0.ProjectType = 'New Proj') AND (YEAR(x0.Date) = 2015)
GROUP BY x0.ContentType) AS c ON a.ContentType = c.ContentType
WHERE        (a.ProjectType = 'New Proj') AND (YEAR(a.Date) = 2015) 
GROUP BY a.ContentType, YEAR(a.Date), c.CountRgreater95, c.SumLengthgreater95,a.ProjectID,a.DesignMix  )a inner join MaterialType b on a.ProjectID=b.ProjectID and a.DesignMix=b.DesignMix 

) c  group by c.ContentType,ProjectID,DesignMix,Material,CountLength,SumLength,CountRgreater95,SumLengthgreater95,AvgR,MinR,MaxR
)x
Left Join
(
select d.ContentType
,d.ProjectID
,d.DesignMix
,'Mixed' as Material
 ,count(ProjectID) cnt
 from
 (
Select  Distinct
c.ContentType
,CountLength
,SumLength
, AvgR
,MinR
,MaxR
,c.ProjectID
,c.DesignMix
,c.Material
,CountRgreater95
,SumLengthgreater95

from
(
select distinct a.* ,b.Material,max(b.Perc) over (partition by b.Material,b.DesignMix order by a.ProjectID) Perc from  (SELECT a.ProjectID,a.DesignMix,a.ContentType, COUNT(b.Length) AS CountLength, SUM(b.Length) AS SumLength, CONVERT(int, ROUND(AVG(CONVERT(decimal(6, 2), b.TenthReading)), 0)) AS AvgR, MIN(b.TenthReading) AS MinR, MAX(b.TenthReading) AS MaxR, c.CountRgreater95, 
        c.SumLengthgreater95
FROM            dbo.Master AS a INNER JOIN
dbo.Details AS b ON a.SPID = b.SPID INNER JOIN
(SELECT        x0.ContentType,x1.Material, COUNT(x.Length) AS CountRgreater95, SUM(x.Length) AS SumLengthgreater95
FROM            dbo.Master AS x0 INNER JOIN
dbo.Details AS x ON x0.SPID = x.SPID left Join
MaterialType x1 on x0.ProjectID=x1.ProjectID and x0.DesignMix=x1.DesignMix
WHERE        (x.TenthReading >= 95) AND (x0.ProjectType = 'New Proj') AND (YEAR(x0.Date) = 2015)
GROUP BY x0.ContentType,x1.Material) AS c ON a.ContentType = c.ContentType
WHERE        (a.ProjectType = 'New Proj') AND (YEAR(a.Date) = 2015) 
GROUP BY a.ContentType, YEAR(a.Date), c.CountRgreater95, c.SumLengthgreater95,a.ProjectID,a.DesignMix  )a inner join MaterialType b on a.ProjectID=b.ProjectID and a.DesignMix=b.DesignMix 

) c  group by c.ContentType,ProjectID,DesignMix,Material,CountLength,SumLength,CountRgreater95,SumLengthgreater95,AvgR,MinR,MaxR
)d
group by d.ContentType,ProjectID,DesignMix 
Having count(ProjectID)>1
)y on x.ContentType=y.ContentType and x.ProjectID=y.ProjectID and x.DesignMix=y.DesignMix
)z

Verifique o link do DBFiddle para ver meus resultados

sql-server join

2 respostas

  1. Best Answer

    Vou fazer algumas suposições, pois há um punhado de discrepâncias na pergunta original:

    • ignorar SPIDcoluna; ausente do dbfiddle; ausente da consulta de amostra
    • junte -se Mastere DetailsemProjectID
    • join Mastere MaterialTypeem 3 colunas ( ProjectID, DesignMix, ContentType)

    Será mais fácil executar agregados separadamente nas tabelas Detailse MaterialType(via CTEs) e, em seguida, juntar-se à Mastertabela de acordo com os requisitos listados na pergunta:

    with

mat_type as
(select ProjectID,
        DesignMix,
        case when count(distinct Material) > 1 then 'Mixed' else min(Material) end as 'Material',
        ContentType

from    MaterialType
group by ProjectID, DesignMix, ContentType),

dtls as
(select ProjectID,
        count(Length)                                           as CountLength,
        convert(numeric(6,3),sum(Length))                       as SumLength,
        convert(numeric(6,2),avg(TenthReading*1.0))             as AvgR,
        min(TenthReading)                                       as MinR,
        max(TenthReading)                                       as MaxR,
        sum(case when TenthReading > 95 then 1      else 0 end) as CountRgreater95,
        sum(case when TenthReading > 95 then Length else 0 end) as SumLengthgreater95

from    Details
group by ProjectID)

select  m.ContentType,
        mt.Material,
        d.CountLength,
        d.SumLength,
        d.AvgR,
        d.MinR,
        d.MaxR,
        d.CountRgreater95,
        d.SumLengthgreater95

from    Master m

left
join    dtls d
on      d.ProjectID = m.ProjectID

left
join    mat_type mt
on      mt.ProjectID   = m.ProjectID
and     mt.DesignMix   = m.DesignMix
and     mt.ContentType = m.ContentType

where   m.ProjectTYpe = 'New Proj'
and     year(m.[Date]) = 2015

order by m.ContentType

    E os resultados da execução da consulta acima:

     ContentType | Material  | CountLength | SumLength | AvgR  | MinR | MaxR | CountRgreater95 | SumLengthgreater95
 ----------- | --------- | ----------- | --------- | ----- | ---- | ---- | --------------- | ------------------
 CT_1        | Mixed     |           6 | 0.560     | 61.33 |   43 |  120 |               1 |               0.06
 CT_2        | Concrete  |           6 | 0.580     | 79.17 |   45 |  140 |               2 |               0.18
 CT_3        | Limestone |           6 | 0.557     | 74.50 |   43 |  155 |               2 |               0.18

    Aqui está um dbfiddle

    NOTA: Para ContentType=CT_2, recebo AvgR=79.17(79,1667 arredondado), enquanto os resultados desejados estão sendo exibidos AvgR=79.16(79,1667 truncado para 2 casas decimais); dependendo do que é desejado (arredondamento vs truncamento), não deve ser muito difícil ajustar a consulta de acordo.

    • 2

  2. Tente esta consulta

    WITH cte_sum
AS (
    SELECT ProjectID
        ,DesignMix
        ,Max(Material) Material
        ,count(DISTINCT Material) ct
    FROM MaterialType
    GROUP BY ProjectID
        ,DesignMix
    )
    ,cte_dtl
AS (
    SELECT ProjectID
        ,count(Length) CountLength
        ,Sum(Length) SumLength
        ,Avg(TenthReading) AvgR
        ,Min(TenthReading) MinR
        ,Max(TenthReading) MaxR
        ,Sum(CASE WHEN TenthReading > 95 THEN 1 ELSE 0 END) CountRGreater95
        ,Sum(CASE WHEN TenthReading > 95 THEN Length ELSE 0 END) SumLengthGreater95
    FROM Details
    GROUP BY ProjectID
    )
SELECT M.ContentType
    ,CASE WHEN cte_sum.ct > 1 THEN 'Mixed' ELSE cte_sum.material END Material
    ,D.CountLength
    ,D.SumLength
    ,D.AvgR
    ,D.MinR
    ,D.MaxR
    ,D.CountRGreater95
    ,D.SumLengthGreater95
FROM Master M
LEFT OUTER JOIN cte_sum ON (
        M.ProjectID = cte_sum.ProjectID
        AND M.DesignMix = cte_sum.DesignMix
        )
LEFT OUTER JOIN cte_dtl D ON (M.ProjectID = D.ProjectID)
    • 1

relate perguntas