mysql - 在 MySQL 中进行透视时如何生成没有零值字段的单行结果集

Question

我有一个需要旋转的查询，所以我关注了 this tutorial我设法根据需要调整数据，除了一件事。我得到如下结果集:

PRODUCT 1 | PRODUCT 2 | PRODUCT 3
0         | 0         | 9
0         | 39        | 0
61        | 0         | 0

我想得到这些相同的数字，它们是正确的，但是这样:

PRODUCT 1 | PRODUCT 2 | PRODUCT 3
61        | 39        | 9

我想要一行包含没有零值的数字。

我有以下查询:

select
ceil(
    sum(if(ba_brand = 'PRODUCT 1', sk_warehouse + sk_exhibition, 0))
    / count(distinct(vi_pdv_id_pdv))
    ) as 'PRODUCT 1', 
ceil(
    sum(if(ba_brand = 'PRODUCT 2', sk_warehouse + sk_exhibition, 0))
    / count(distinct(vi_pdv_id_pdv))
    ) as 'PRODUCT 2', 
ceil(
    sum(if(ba_brand = 'PRODUCT 3', sk_warehouse + sk_exhibition, 0))
    / count(distinct(vi_pdv_id_pdv))
    ) as 'PRODUCT 3'

from sf_visit_stock

join sf_visit on sk_vi_id_visit = id_visit
join sf_pdv on vi_pdv_id_pdv = id_pdv
join sf_format on pdv_fo_id_format = id_format
join sf_group on fo_gr_id_group = id_group
join sf_channel on gr_ch_id_channel = id_channel

join sf_product on sk_pd_id_product = id_product
join sf_family on pd_fa_id_family = id_family
join sf_brand on fa_ba_id_brand = id_brand

and vi_pr_id_proyect = 5

group by ba_brand
order by ba_brand

我已经尝试过 concat_group() 并尝试使用 group by 解决此问题，但我无法找到问题所在。

Answer 1

由于 ba_brand 位于聚合函数内，因此您应该能够删除 group by 子句并获得所需的结果。

select
ceil(
    sum(if(ba_brand = 'PRODUCT 1', sk_warehouse + sk_exhibition, 0))
    / count(distinct(if(ba_brand = 'PRODUCT 1', vi_pdv_id_pdv, null)))
    ) as 'PRODUCT 1', 
ceil(
    sum(if(ba_brand = 'PRODUCT 2', sk_warehouse + sk_exhibition, 0))
    / count(distinct(if(ba_brand = 'PRODUCT 2', vi_pdv_id_pdv, null)))
    ) as 'PRODUCT 2', 
ceil(
    sum(if(ba_brand = 'PRODUCT 3', sk_warehouse + sk_exhibition, 0))
    / count(distinct(if(ba_brand = 'PRODUCT 3', vi_pdv_id_pdv, null)))
    ) as 'PRODUCT 3'

from sf_visit_stock

join sf_visit on sk_vi_id_visit = id_visit
join sf_pdv on vi_pdv_id_pdv = id_pdv
join sf_format on pdv_fo_id_format = id_format
join sf_group on fo_gr_id_group = id_group
join sf_channel on gr_ch_id_channel = id_channel

join sf_product on sk_pd_id_product = id_product
join sf_family on pd_fa_id_family = id_family
join sf_brand on fa_ba_id_brand = id_brand

and vi_pr_id_proyect = 5

group by ba_brand //remove this line
order by ba_brand //and this one