时间戳

首页 > 解决方案 > 如何在 SQL 中将 2 行合并为 1 行？

sql - 如何在 SQL 中将 2 行合并为 1 行？

问题描述

ReportDateTime   EuId1 EuId2
2020-02-01 1:00  1576  Null    
2020-02-01 1:00  Null  1579    
2020-02-01 2:00  Null  1573    
2020-02-01 2:00  1566  Null

这就是我所拥有的，这就是我想要的……

 ReportDateTime   EuId1 EuId2    
 2020-02-01 1:00  1576  1579    
 2020-02-01 2:00  1566  1573

这是我的代码...

;WITH cteEq AS (
    SELECT e.EntHID, e.EntCode, e.EntName 
    FROM cfEntity fac (NOLOCK)
    JOIN cfEntityRelation er (NOLOCK) ON fac.EntHID = er.EntParentHID AND er.EntRelEffEnd = '12/31/2078'
    JOIN cfEntity e (NOLOCK) ON er.EntChildHID = e.EntHID AND e.EntTypeTID = -200020
    WHERE e.EntHID IN (SELECT ea.EaHID FROM cfEntityAttribute ea (NOLOCK) WHERE ea.EaKey = 'AirPermitXref_Diesel')
)
SELECT
    ReportDateTime = a.EqReportDate
    , EuId1 = CASE
       WHEN EntName LIKE '%EU1%' THEN a.EqPwr
       END
    , EuId2 = CASE
       WHEN EntName LIKE '%EU2%' THEN a.EqPwr
       END
FROM eqHourlyAir a (NOLOCK)
JOIN cteEq e (NOLOCK) ON a.EqHID = e.EntHID
OUTER APPLY (
    SELECT ad.EqHID, ad.EqReportDate, ad.EqRunTime
    FROM eqHourlyAir ad (NOLOCK)
    LEFT JOIN cfEntityAttribute ea (NOLOCK) ON ad.EqHID = CAST(ea.EaValue AS INT) AND ea.EaKey = 'AirPermitXref_DWI'
    WHERE ea.EaHID = a.EqHID 
    AND ad.EqReportDate = a.EqReportDate
) dwi
OUTER APPLY (
    SELECT ad.EqHID, ad.EqReportDate, ad.EqRunTime
    FROM eqHourlyAir ad (NOLOCK)
    LEFT JOIN cfEntityAttribute ea (NOLOCK) ON ad.EqHID = CAST(ea.EaValue AS INT) AND ea.EaKey = 'AirPermitXref_Diesel'
    WHERE ea.EaHID = a.EqHID 
    AND ad.EqReportDate = a.EqReportDate
) dsl
WHERE CAST(a.EqReportDate AS DATE) >= @StartDate AND CAST(a.EqReportDate AS DATE) <= @EndDate 
ORDER BY a.EqReportDate

标签： sqlsql-servertsqlmergerow

解决方案

  1. 使用 nolock 提示不是最好的主意！
  2. 如果每个报告日期只有一个空值和一个非值，则可以按 ReportDateTime 分组并获得最大值：
...
SELECT
    ReportDateTime = a.EqReportDate,
    EuId1 = MAX(CASE WHEN EntName LIKE '%EU1%' THEN a.EqPwr END),
    EuId2 = MAX(CASE WHEN EntName LIKE '%EU2%' THEN a.EqPwr END)
FROM
 {...}
WHERE
    CAST(a.EqReportDate AS DATE) >= @StartDate
    AND CAST(a.EqReportDate AS DATE) <= @EndDate
GROUP BY a.EqReportDate
ORDER BY
    a.EqReportDate

推荐阅读