将多行汇总为一行

wribegjk 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(320)

我有一张这样的table：

USER_ID,ADDED_DATE,STATUS,COMPLETION_ID_TYPE,QA_OPTION,QA_OPTION_COUNT
12543,2020-06-01 00:00:00,qaComplete_L2,chart,Correct,3
12543,2020-06-01 00:00:00,qaComplete_L2,chart,Incorrect,3
12543,2020-06-12 00:00:00,qaComplete_L2,chart,Incorrect,1
12543,2020-06-12 00:00:00,qaComplete_L2,chart,Correct,1

我想将结果显示为：

USER_ID ADDED_DATE  STATUS  COMPLETION_ID_TYPE  L2 Correct  L2 InCorrect
8388    6/01/20 0:00    qaComplete_L2   chart   3   3
8388    6/12/20 0:00    qaComplete_L2   chart   1   1

我尝试过这个，但没有得到我期望的结果：

select distinct user_id,
                         added_date,
                         status,
                         completion_id_type,
                         max(case
                                 when qa_option = 'Correct'
                                     then qa_option_count
                                 else 0
                             end) as L2_Correct,
                         max(case
                                 when qa_option = 'Incorrect' 
                                     then qa_option_count
                                 else 0
                             end) as L2_Incorrect

         from qa_report2
         where user_id = 12543
           and status = 'qaComplete_L2'
         group by user_id, status, added_date, completion_id_type,qa_option, qa_option_count
         order by user_id, added_date;

;

USER_ID,ADDED_DATE,STATUS,COMPLETION_ID_TYPE,L2_CORRECT,L2_INCORRECT
12543,2020-06-01 00:00:00,qaComplete_L2,chart,0,3
12543,2020-06-01 00:00:00,qaComplete_L2,chart,3,0
12543,2020-06-12 00:00:00,qaComplete_L2,chart,1,0
12543,2020-06-12 00:00:00,qaComplete_L2,chart,0,1

sql oracle pivot-table

来源：https://stackoverflow.com/questions/62451881/roll-up-multiple-rows-into-one

2条答案

按热度按时间

ezykj2lf1#

你就快到了：）
我只删除了distinct和最后两个groupby列。计算中需要的列不应出现在GROUPBY子句中，而应仅出现在select子句的group函数中。
最后，我想你要找的是：

select  user_id,
        added_date,
        status,
        completion_id_type,
        max(case
              when qa_option = 'Correct'
                then qa_option_count
              else 0
            end) as L2_Correct,
        max(case
              when qa_option = 'Incorrect' 
                then qa_option_count
              else 0
            end) as L2_Incorrect
from  qa_report2
where user_id = 12543
and   status = 'qaComplete_L2'
group by user_id,
         status,
         added_date,
         completion_id_type
         --,qa_option
         --,qa_option_count
order by user_id,
         added_date;

注意：您应该知道您使用的是max（），我可以想象，如果存在多个记录，您实际上希望使用sum（），但这实际上取决于您的用例。

赞(0）回复(0）举报 2021-07-26

hec6srdp2#

你可以用 PIVOT 为了实现它。

SELECT *
  FROM (
    SELECT USER_ID,
           ADDED_DATE,
           STATUS,
           COMPLETION_ID_TYPE,
           QA_OPTION_COUNT,
           QA_OPTION
      FROM QA_REPORT2
     WHERE USER_ID = 12543
       AND STATUS = 'qaComplete_L2'
) PIVOT (
    MAX ( QA_OPTION_COUNT )
    FOR QA_OPTION
    IN ( 'Correct' AS L2_CORRECT, 'Incorrect' AS L2_INCORRECT )
);

赞(0）回复(0）举报 2021-07-26

我来回答

将多行汇总为一行

2条答案

相关问题

热门标签

最新问答