mysql - 如何在联合中使用 case 语句

Question

下面是我的脚本的一部分。

select a. itemcode, sum(b.quantity) 'y1qty', 
    isnull(null,0) as 'yr2qty', sum(c.amt) 'yr1amt' 
    isnull(null,0) as 'yr2amt',
from ABC a
left join DEF b on a.itemcode = b.itemcode
group by a. itemcode

UNION

select  a1. itemcode, isnull(null,0) as 'yr1qty',
    sum(b1.quantity) 'yr2qty', isnull(null,0) as 'yr1amt',
    sum(c.amt) 'yr2amt' 
from ABC a1
left join DEF b1 on a1.itemcode = b1.itemcode
group by a1.itemcode

表 A:示例输出(基于上面的查询)

itemcode     y1qty  yr2qty   yr1amt      yr2amt
item 001     150      0       200.00       0
item 002      0      300        0         150.00
item 003      0      50         0         100.00
item 004     20      0        150.00       0       

我想插入一个 case 语句，其中逻辑如下:如果 y1qty = 0 且 yr2qty = 0 那么等等等等..如果 y1qty = 0 且 y2qty > 0 那么等等等等.. 'DIff'

并致力于实现这一结果:

Table B: 
itemcode     y1qty  yr2qty   Diff      yr1amt      yr2amt
item 001     150      0         0        200.00       0
item 002      0      300       100        0         150.00
item 003      0      50        100        0         100.00
item 004     20      0           0      150.00       0 
item 005     20      30       1000      100.00      200.00

Answer 1

您需要将查询包装为子查询并执行以下操作:

select 
  itemcode, 
  y1qty, 
  yr2qty, 
  case when y1qty = 0 and yr2qty = 0 then ... when ... then ... else ... end Diff,
  yr1amt,
  yr2amt
from (
  select a. itemcode, sum(b.quantity) 'y1qty', 
      isnull(null,0) as 'yr2qty', sum(c.amt) 'yr1amt' 
      isnull(null,0) as 'yr2amt',
  from ABC a
  left join DEF b on a.itemcode = b.itemcode
  group by a. itemcode

  UNION

  select  a1. itemcode, isnull(null,0) as 'yr1qty',
      sum(b1.quantity) 'yr2qty', isnull(null,0) as 'yr1amt',
      sum(c.amt) 'yr2amt' 
  from ABC a1
  left join DEF b1 on a1.itemcode = b1.itemcode
  group by a1.itemcode
) t