MySql : How do I have Horizontal Union of results?

Question

我希望将结果生成到水平联合中。我能够组合在一起并生成结果。但是，我需要以预期结果格式显示结果。

SQL

(SELECT 
    kpa_id AS 'KPA', SUM(weightage) AS 'Total'
FROM
    pmm_question_details
WHERE
    weightage NOT LIKE '%-%'
GROUP BY kpa_id) UNION (SELECT 
    kpa_id AS 'KPA', SUM(weightage_value) AS 'Acheived'
FROM
    pmm_answer_details
WHERE
    application_id = 2
        AND archive_value = 'No'
GROUP BY kpa_id)

实际结果

1   14
2   37
3   19
4   40
5   51
6   24
1   12
2   19
3   0
6   2

预期结果

1   14  1 12
2   37  2 19
3   19  3 0
4   40  6 2
5   51
6   24

Answer 1

如果我们可以假设 pmm_question_Details 始终具有与 pmm_answer_details 一样多或更多的记录...那么两个子查询和左联接应该可以通过用户变量 rownum (RN) 上的联接来实现这一点

SELECT A.KPA, A.Total, B.KPA, B.Acheived
FROM (SELECT kpa_id AS 'KPA'
           , SUM(weightage) AS 'Total'
           , @RN1 := @RN1 + 1 as RN
      FROM pmm_question_details
      CROSS JOIN (SELECT @RN1 :=0) r1
      WHERE weightage NOT LIKE '%-%'
      ORDER BY KPA
      GROUP BY kpa_id) A

LEFT JOIN (SELECT kpa_id AS 'KPA'
                , SUM(weightage_value) AS 'Acheived'
                , @RN1 := @RN2 + 1 as RN
           FROM pmm_answer_details
           CROSS JOIN (SELECT @RN2 :=0) r2
           WHERE application_id = 2
             AND archive_value = 'No'
           ORDER BY KPA
           GROUP BY kpa_id) B
 on A.RN = B.RN
ORDER BY A.KPA

尽管我必须承认，如果您一开始就可以在 KPA_ID 上保留连接，那么我不明白为什么需要行号...

如果这可能是预期结果...(再次假设 pmm_question 具有 pmm_answer 中可能存在的所有 ID...)

Expected Results

1   14  1 12
2   37  2 19
3   19  3 0
4   40
5   51
6   24  6 2

那么查询就只是......

SELECT A.KPA, A.Total, B.KPA, B.Acheived
FROM (SELECT kpa_id AS 'KPA', SUM(weightage) AS 'Total'
      FROM pmm_question_details
      WHERE weightage NOT LIKE '%-%'
      GROUP BY kpa_id) A
LEFT JOIN (SELECT kpa_id AS 'KPA', SUM(weightage_value) AS 'Acheived'
           FROM pmm_answer_details
           WHERE application_id = 2
             AND archive_value = 'No'
           GROUP BY kpa_id) B
 on A.KPA = B.KPA