mysql

a64a0gku 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(198)

我正在处理一个查询，希望对行进行分组并返回groupped数据，但我的查询没有按预期工作。
我的问题-

select item, branch, packunit,packlevel,dealqty,PromotionFlag,PromotionID, PromotionEndDate, cnc,delivery, volumedeal, standard_price_scheme,
deliv_price_scheme
from 
(
SELECT 
        `item` AS `item`,
        `branch` AS `branch`,
        `PackUnit` AS `PackUnit`,
        `PackLevel` AS `PackLevel`,
        `DealQty` AS `DealQty`,
        `PromotionFlag` AS `PromotionFlag`,
        `PromotionID` AS `PromotionID`,
        `PromotionEndDate` AS `PromotionEndDate`,
        SUM(`cnc`) AS `cnc`,
        SUM(`delivery`) AS `delivery`,
        SUM(`volumedeal`) AS `volumedeal`,
        `standard_price_scheme` AS `standard_price_scheme`,
        `deliv_price_scheme` AS `deliv_price_scheme`
    FROM
    (
SELECT DISTINCT
        `Pricing_Today`.`item` AS `item`,
        `Pricing_Today`.`branch` AS `branch`,
        `Pricing_Today`.`price_scheme` AS `price_scheme`,
        `Pricing_Today`.`PackUnit` AS `PackUnit`,
        `Pricing_Today`.`PackLevel` AS `PackLevel`,
        `Pricing_Today`.`DealQty` AS `DealQty`,
        `Pricing_Today`.`PromotionFlag` AS `PromotionFlag`,
        `Pricing_Today`.`PromotionID` AS `PromotionID`,
        `Pricing_Today`.`PromotionEndDate` AS `PromotionEndDate`,
        (CASE
            WHEN (`Pricing_Today`.`PriceType` = 'C&C') THEN `Pricing_Today`.`Sell`
        END) AS `cnc`,
        (CASE
            WHEN (`Pricing_Today`.`PriceType` = 'Delivery') THEN `Pricing_Today`.`Sell`
        END) AS `delivery`,
        (CASE
            WHEN (`Pricing_Today`.`PriceType` = 'Volume Deal') THEN `Pricing_Today`.`Sell`
        END) AS `volumedeal`,
        (CASE
            WHEN (`Pricing_Today`.`PriceType` = 'C&C') THEN `Pricing_Today`.`price_scheme`
        END) AS `standard_price_scheme`,
        (CASE
            WHEN
                ((`Pricing_Today`.`PriceType` = 'Delivery')
                    OR (`Pricing_Today`.`PriceType` = 'Volume Deal'))
            THEN
                `Pricing_Today`.`price_scheme`
        END) AS `deliv_price_scheme`
    FROM
        `Pricing_Today`
        where item = 78867
        and branch = 0
    GROUP BY `Pricing_Today`.`item` , `Pricing_Today`.`PackUnit` , `Pricing_Today`.`PriceType`,`standard_price_scheme`,`deliv_price_scheme`
    ) as a

    GROUP BY branch,`item` , `PackUnit`,`standard_price_scheme`,`deliv_price_scheme`
    ) as a
    -- group by item, packunit

它回来了-

但是，当我按项目分组时，我得到这个-

对于cnc，它显示空值。如何消除空值并获得数字？
提前谢谢

mysql

来源：https://stackoverflow.com/questions/52751679/group-by-picking-columns-with-null-value-mysql

1条答案

按热度按时间

6xfqseft1#

你需要把这些 CASE 表达：

SELECT
    p.item,
    p.branch,
    p.price_scheme,
    p.PackUnit,
    p.PackLevel,
    p.DealQty,
    p.PromotionFlag,
    p.PromotionID,
    p.PromotionEndDate,
    MAX(CASE WHEN p.PriceType = 'C&C'         THEN p.Sell END) AS cnc,
    MAX(CASE WHEN p.PriceType = 'Delivery'    THEN p.Sell END) AS delivery,
    MAX(CASE WHEN p.PriceType = 'Volume Deal' THEN p.Sell END) AS volumedeal,
    MAX(CASE WHEN p.PriceType = 'C&C' THEN p.price_scheme END) AS standard_price_scheme,
    MAX(CASE WHEN p.PriceType = 'Delivery' OR p.PriceType = 'Volume Deal'
             THEN p.price_scheme END) AS deliv_price_scheme
FROM
    Pricing_Today p
WHERE
    item = 78867 AND branch = 0
GROUP BY
    p.item,
    p.branch,
    p.price_scheme,
    p.PackUnit,
    p.PackLevel,
    p.DealQty,
    p.PromotionFlag,
    p.PromotionID,
    p.PromotionEndDate;

这只是一个标准的pivot查询。采取行动背后的想法 MAX 的 CASE 表达式是如果给定的一组记录有一个非 NULL 价值，那么 MAX 会正确地提取它。这是因为 MAX 忽略 NULL 价值观。
请注意，我从您的查询中删除了反勾号，这些都不是必需的。除非确实需要，否则我尽量避免使用反勾号，因为这会使查询更难阅读。

赞(0）回复(0）举报 2021-06-18

我来回答

mysql

1条答案

相关问题

热门标签

最新问答