我有一个属于多个信用组的Student_ID列表。我正在尝试以一种清晰的方式来构造它们以进行分析。我想在每列中有一个学生及其组信息的记录,并使用YES / NO标志作为列值。
目前,我只能使用以下查询来构建格式,该格式会为每个组生成单独的记录:
SELECT STUDENT_ID,
CASE WHEN CREDIT_ID IN ('1000.00', '0105.00', '0707.00','8009.00', '0809.01') THEN 'YES' END AS GROUP1,
CASE WHEN CREDIT_ID IN ('0987.00', '0125.00', '1055.00', '0890.00', '6600.20') THEN 'YES' END AS GROUP2,
CASE WHEN CREDIT_ID IN ('1100.00', '1190.01', '0045.20', '0675.00') THEN 'YES' END AS GROUP3
FROM STUDENT_TABLE
WHERE CREDIT_ID IN ('1000.00', '0105.00', '0707.00','8009.00', '0809.01', '0987.00', '0125.00', '1055.00', '0890.00', '6600.20', '1100.00', '1190.01', '0045.20', '0675.00')
ORDER BY STUDENT_ID;
输出:
STUDENT_ID GROUP1 GROUP2 GROUP3
1233 YES null null
1233 null YES null
3456 YES null null
3456 null null YES
2376 YES null null
2376 null YES null
3499 YES null null
3499 null YES null
3499 null null YES
我希望它看起来像是:
STUDENT_ID GROUP1 GROUP2 GROUP3
1233 YES YES null
3456 YES null YES
2376 YES YES null
3499 YES YES YES
如何在oracle SQL中实现呢?有人可以帮忙吗?
你应该通过聚合STUDENT_ID
,然后取MAX
你的CASE
表达式汇总结果到你想要的东西。
SELECT
STUDENT_ID,
MAX(CASE WHEN CREDIT_ID IN ('1000.00', '0105.00', '0707.00','8009.00', '0809.01') THEN 'YES' END) AS GROUP1,
MAX(CASE WHEN CREDIT_ID IN ('0987.00', '0125.00', '1055.00', '0890.00', '6600.20') THEN 'YES' END) AS GROUP2,
MAX(CASE WHEN CREDIT_ID IN ('1100.00', '1190.01', '0045.20', '0675.00') THEN 'YES' END) AS GROUP3
FROM STUDENT_TABLE
WHERE
CREDIT_ID IN ('1000.00', '0105.00', '0707.00','8009.00', '0809.01', '0987.00', '0125.00', '1055.00', '0890.00', '6600.20', '1100.00', '1190.01', '0045.20', '0675.00')
GROUP BY
STUDENT_ID
ORDER BY
STUDENT_ID;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句