是否可以将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/

10-12 12:41
查看更多