是否可以将case语句中的每个WHEN
输出到其自己的列中?我尝试过,但是没有运气。我最接近我想要的是下面。
我目前有一个查询,如下所示:
select BH.BATCH as batch_number,
BS.TBS_DESCRIPTION as batch_description,
BH.NUM_VOUCHERS as "NUM_VOUCHERS(BATCH)",
case when VS.TVS_CODE = 1 then count(*) else 0 end as in_stock,
case when VS.TVS_CODE = 3 then count(*) else 0 end as terminate,
case when VS.TVS_CODE = 5 then count(*) else 0 end as in_progress,
case when VS.TVS_CODE = 6 then count(*) else 0 end as used,
case when VS.TVS_CODE = 8 then count(*) else 0 end as deactivate
from BATCH_HIST bh,
BATCH_STATUS bs,
VOUCH_HIST vh,
VOUCH_STATUS vs
where substr(BH.ALLOCAT_DATE, 1, 6) = 201207
and to_char(BH.STATUS) = BS.TBS_CODE
and BH.BATCH = VH.BATCH
and to_char(VH.STATUS) = VS.TVS_CODE
group by BH.BATCH,
BS.TBS_DESCRIPTION,
BH.NUM_VOUCHERS,
VS.TVS_CODE
order by 1,2,3;
这给了我以下输出,每个
STATUS
作为不同的记录:BATCH_NUMBER | BATCH_DESCRIPTION | NUM_VOUCHERS(BATCH) | IN_STOCK | TERMINATE | IN_PROGRESS | USED | DEACTIVATE
------------------------------------------------------------------------------------------------------------------------------
4413565 | Allocate | 100 | 67 | 0 | 0 | 0 | 0
4413565 | Allocate | 100 | 0 | 0 | 0 | 33 | 0
4413566 | Allocate | 100 | 63 | 0 | 0 | 0 | 0
4413566 | Allocate | 100 | 0 | 0 | 0 | 37 | 0
我希望将其分组,以便每个
BATCH_NUMBER
有一个记录:BATCH_NUMBER | BATCH_DESCRIPTION | NUM_VOUCHERS(BATCH) | IN_STOCK | TERMINATE | IN_PROGRESS | USED | DEACTIVATE
------------------------------------------------------------------------------------------------------------------------------
4413565 | Allocate | 100 | 67 | 0 | 0 | 33 | 0
4413566 | Allocate | 100 | 63 | 0 | 0 | 37 | 0
最佳答案
如您所写,表达式需要分组依据,但是如果总和匹配,则可以从分组依据中消除tvs_code:
select BH.BATCH as batch_number,
BS.TBS_DESCRIPTION as batch_description,
BH.NUM_VOUCHERS as "NUM_VOUCHERS(BATCH)",
sum (case when VS.TVS_CODE = 1 then 1 else 0 end) as in_stock,
sum (case when VS.TVS_CODE = 3 then 1 else 0 end) as terminate,
sum (case when VS.TVS_CODE = 5 then 1 else 0 end) as in_progress,
sum (case when VS.TVS_CODE = 6 then 1 else 0 end) as used,
sum (case when VS.TVS_CODE = 8 then 1 else 0 end) as deactivate
from BATCH_HIST bh,
BATCH_STATUS bs,
VOUCH_HIST vh,
VOUCH_STATUS vs
where substr(BH.ALLOCAT_DATE, 1, 6) = 201207
and to_char(BH.STATUS) = BS.TBS_CODE
and BH.BATCH = VH.BATCH
and to_char(VH.STATUS) = VS.TVS_CODE
group by BH.BATCH,
BS.TBS_DESCRIPTION,
BH.NUM_VOUCHERS
order by 1,2,3;
关于sql - 将SQL case语句显示为不同的列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12462176/