mysql - 如何建立内容变成行的表

Question

我想对表进行如下查询:
enter image description here

b.sostatus必须成为列标题，并汇总订单编号

我的SQL如下:

`

select 
 b.orderTime as orderDate,
sum( case when b.soStatus='00' then count(a.orderNo)  else 0 end ) as Not_Allocated,
sum( case when b.soStatus='30' then count(a.orderNo) else 0 end ) as Partially_Allocated,
 sum( case when b.soStatus='40' then count(a.orderNo) else 0 end ) as Allocated,
sum(case when b.soStatus='50' then count(a.orderNo) else 0 end ) as Partially_Picked ,
 sum(case when b.soStatus='60' then count(a.orderNo) else 0 end ) as Picked,
  sum(case when b.soStatus='63' then count(a.orderNo) else 0 end ) as Cartonized,
  sum(case when b.soStatus='99' then count(a.orderNo) else 0 end ) as Closed,
 case 
 when datediff(NOW(),b.orderTime)  >=2 then '>=H-2'
when datediff(NOW(),b.orderTime)  =1 then 'H-1'
when  datediff(NOW(),b.orderTime) <1 then 'H' end as status
    from DOC_ORDER_HEADER b
    left join DOC_ORDER_DETAILS a
    on 
    b.organizationId='ID_8COM'
    and b.warehouseId='WHCPT01'
       AND a.orderno     = b.orderno
    where b.organizationId='ID_8COM'
    and b.warehouseId='WHCPT01'

`

但是，它 pop 错误:无效使用组功能

Answer 1

您不需要根据此要求使用count()函数。 sum(1)足以根据您的条件获得计数。

select 
    b.orderTime as orderDate,
    sum(case when b.soStatus='00' then 1 else 0 end) as Not_Allocated,
    sum(case when b.soStatus='30' then 1 else 0 end) as Partially_Allocated,
    sum(case when b.soStatus='40' then 1 else 0 end) as Allocated,
    sum(case when b.soStatus='50' then 1 else 0 end) as Partially_Picked ,
    sum(case when b.soStatus='60' then 1 else 0 end) as Picked,
    sum(case when b.soStatus='63' then 1 else 0 end) as Cartonized,
    sum(case when b.soStatus='99' then 1 else 0 end) as Closed,
    case when datediff(NOW(),b.orderTime)  >=2 then '>=H-2'
    when datediff(NOW(),b.orderTime)  = 1 then 'H-1'
    when datediff(NOW(),b.orderTime) < 1 then 'H' end as status
from DOC_ORDER_HEADER b
left join DOC_ORDER_DETAILS a on b.organizationId='ID_8COM' and b.warehouseId='WHCPT01'
    and a.orderno = b.orderno
where b.organizationId='ID_8COM'
    and b.warehouseId='WHCPT01'
group by b.orderTime

您也可以这样做。

sum(b.soStatus='00')