时间戳

首页 > 解决方案 > 用于合并结果的 Pivot 或 Group 函数

sql - 用于合并结果的 Pivot 或 Group 函数

问题描述

我有一个代码如下，

WITH result_set 
     AS (SELECT DISTINCT lt.id                       AS labTestId, 
                         lt.pass_customer_specs      AS passCustomer, 
                         lt.pass_internal_specs      AS passInternal, 
                         ltr.lab_test_measurement_id AS ltMeasurementId, 
                         ltc.compound_id             AS compoundId, 
                         lt.batch_number             AS batch_number, 
                         m.NAME                      AS NAME, 
                         ltn.id                      AS test_machine 
         FROM   lab_test lt 
                JOIN lab_test_result ltr 
                  ON ltr.lab_test_id = lt.id 
                JOIN lab_test_measurement ltm 
                  ON ltm.id = ltr.lab_test_measurement_id 
                JOIN lab_test_machine ltn 
                  ON ltn.id = ltm.lab_test_machine_id 
                JOIN lab_test_compound ltc 
                  ON ltc.compound_id = lt.lab_test_compound_id 
                JOIN compound c 
                  ON c.id = ltc.compound_id 
                JOIN measurement m 
                  ON m.id = ltm.measurement_id) 
SELECT compoundid, 
       batch_number, 
       COALESCE(Cast([1] AS VARCHAR(1)), '-') AS 'MV', 
       COALESCE(Cast([2] AS VARCHAR(1)), '-') AS 'MDR', 
       COALESCE(Cast([3] AS VARCHAR(1)), '-') AS 'Hardness', 
       COALESCE(Cast([9] AS VARCHAR(1)), '-') AS 'SPG' 
FROM   (SELECT * 
        FROM   result_set) pvt 
       PIVOT ( Min (passcustomer) 
             FOR test_machine IN ([1], 
                                  [2], 
                                  [3], 
                                  [9]) ) AS pvt 
ORDER  BY compoundid, 
          batch_number

这给了我如下的东西，

图片1

我要做的是每个批次编号有一个条目，并将所有“MV”、“MDR”、“硬度”、“SPG”放在 1 行。

标签： sqlsql-servertsql

解决方案

尝试这个

WITH result_set 
     AS (SELECT DISTINCT lt.id                       AS labTestId, 
                         lt.pass_customer_specs      AS passCustomer, 
                         lt.pass_internal_specs      AS passInternal, 
                         ltr.lab_test_measurement_id AS ltMeasurementId, 
                         ltc.compound_id             AS compoundId, 
                         lt.batch_number             AS batch_number, 
                         m.NAME                      AS NAME, 
                         ltn.id                      AS test_machine 
         FROM   lab_test lt 
                JOIN lab_test_result ltr 
                  ON ltr.lab_test_id = lt.id 
                JOIN lab_test_measurement ltm 
                  ON ltm.id = ltr.lab_test_measurement_id 
                JOIN lab_test_machine ltn 
                  ON ltn.id = ltm.lab_test_machine_id 
                JOIN lab_test_compound ltc 
                  ON ltc.compound_id = lt.lab_test_compound_id 
                JOIN compound c 
                  ON c.id = ltc.compound_id 
                JOIN measurement m 
                  ON m.id = ltm.measurement_id) 
SELECT compoundid,batch_number, MAX(MV)MV, MAX(MDR)MDR, MAX(Hardness)Hardness, MAX(SPG)SPG
FROM (
SELECT compoundid, 
       batch_number, 
       COALESCE(Cast([1] AS VARCHAR(1)), '-') AS 'MV', 
       COALESCE(Cast([2] AS VARCHAR(1)), '-') AS 'MDR', 
       COALESCE(Cast([3] AS VARCHAR(1)), '-') AS 'Hardness', 
       COALESCE(Cast([9] AS VARCHAR(1)), '-') AS 'SPG' 
FROM   (SELECT * 
        FROM   result_set) pvt 
       PIVOT ( Min (passcustomer) 
             FOR test_machine IN ([1], 
                                  [2], 
                                  [3], 
                                  [9]) ) AS pvt 
) X
GROUP BY compoundid, batch_number
ORDER  BY compoundid, batch_number

推荐阅读