我的查询如下:
我收到一个错误“意外的*”。在第6行,请让我知道,如何重写或更正此问题
select * from (SELECT gl gl_acc,
reg reg,
mapp map_name,
field as f1
FROM gl_acc a
WHERE TYPE = 'AGG') PIVOT (count(*)
FOR f1
IN (
'JOURN' ,
'JOU' ,
'J' ,
'CASE_' ,
'JOUR'
))as p;
谢谢哥
只需使用条件聚合:
SELECT gl, reg, mapp,
SUM(CASE WHEN field = 'JOURN' THEN 1 ELSE 0 END) as journ,
SUM(CASE WHEN field = 'JOU' THEN 1 ELSE 0 END) as jou,
SUM(CASE WHEN field = 'J' THEN 1 ELSE 0 END) as j,
SUM(CASE WHEN field = 'CASE_' THEN 1 ELSE 0 END) as case_,
SUM(CASE WHEN field = 'JOUR' THEN 1 ELSE 0 END) as jour
FROM gl_acc a
WHERE TYPE = 'AGG'
GROUP BY gl, reg, mapp;
或更简单地使用 COUNT_IF()
SELECT gl, reg, mapp,
COUNT_IF(field = 'JOURN') as journ,
COUNT_IF(field = 'JOU') as jou,
COUNT_IF(field = 'J') as j,
COUNT_IF(field = 'CASE_') as case_,
COUNT_IF(field = 'JOUR') as jour
FROM gl_acc a
WHERE TYPE = 'AGG'
GROUP BY gl, reg, mapp;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句