主页 / dba / 问题 / 190786
Accepted
SQLserving
Asked: 2017-11-14 08:54:14 +0800 CST

连接具有复杂需求的多个表

  • 772

我有 3 个这样的表：

大师：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

详情：PK：有一个Identity列ID

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是一个Identity列ID

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

查询规范如下：
1. 根据 SPID连接主表和明细表 2.
根据 ProjectID 和 DesignMix 连接主表和 Material 表
3. 如果特定的 projectID 和 DesignMix 在 MaterialTable 中有多个行，例如。ID=123 的混凝土和石灰石，则应将其与名称“混合”合并在一起
4. 主表中的所有数据以及详细信息和材料表中的相关数据，即首选左连接，尤其是在主表和材料之间
5. 日期应为年份2015 年和项目类型应为“新项目”

结果查询应该给我这样的东西： 

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

这是一个提供测试表定义的DBFiddle 链接

这是我到目前为止写的查询，但没有给我正确的结果：

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

检查 DBFiddle 链接以查看我的结果

sql-server join

2 个回答

  1. Best Answer

    由于原始问题中存在一些差异，因此将做出一些假设：

    • 忽略SPID列；从 dbfiddle 中丢失；示例查询中缺少
    • 加入Master并Details继续ProjectID
    • 加入3 列( Master, , )MaterialTypeProjectIDDesignMixContentType

    这将更容易在表Details和MaterialType表上单独运行聚合（通过 CTE），然后Master根据问题中列出的要求与表连接：

    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

    以及运行上述查询的结果：

     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

    这是一个dbfiddle

    注意：对于ContentType=CT_2，我得到AvgR=79.17（79.1667 四舍五入），同时显示所需的结果AvgR=79.16（79.1667 截断为小数点后两位）；根据需要（舍入与截断），相应地调整查询应该不会太难。

    • 2

  2. 试试这个查询

    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

相关问题