从哪里选择

Question

使用oracle，如何使用单个查询来执行此操作？

VALUE  TYPE  IDentifier  
12   aa   123 
123   bb   123 
323   aa   1234 
1212   cc   123 
2   aa   12345 
123   dd   123 

SELECT IDentifier , SUM(VALUE) AS typeAASum WHERE TYPE = 'aa' 
SELECT IDentifier , SUM(VALUE) AS typeBBSum WHERE TYPE = 'bb' 
SELECT IDentifier , SUM(VALUE) AS typeCCSum WHERE TYPE = 'cc' 

所以我的结果将是，再次在一个查询中，

IDentifier  TypeAASum TypeBBSum  TypeCCSum 

，我只是每标识符获得一个行是很重要的。

Answer 1

SELECT Identifier, 
     SUM(CASE WHEN TYPE = 'aa' THEN VALUE ELSE 0 END) AS typeAASum, 
     SUM(CASE WHEN TYPE = 'bb' THEN VALUE ELSE 0 END) AS typeBBSum, 
     SUM(CASE WHEN TYPE = 'cc' THEN VALUE ELSE 0 END) AS typeCCSum 
FROM YourTable 
GROUP BY Identifier