mysql - 如何计算 SQL SUM 函数内求和的行数？

Question

这是我的 SQL 查询的特定部分:

SELECT pir.paymentInstrumentItemID AS id,
       SUM(pir.priceChange) AS priceSum,
       pir.clientInfoID AS clientID,
       IFNULL(SUM(pir.costChange), 0) AS selfPrice,
       pir.paymentInstrumentItemID
FROM paymentInstrumentRegister as pir

GROUP BY pir.paymentInstrumentItemID 

我需要做的是计算第一个 SUM 函数内分组的行数，并从中创建一个 CASE 语句，如下所示:

SUM(CASE WHEN amtRows = 1 THEN 0 ELSE pir.priceChange) as priceSum

所以基本上只要有一行我就不需要该值，但只要有两个或更多行，我就需要它们的总和。

非常感谢反馈。

Answer 1

我认为最好的选择是添加 COUNT() 来查询，并让编程语言完成其余的工作，但如果您确实需要通过 MySQL 来完成:

SELECT
    id,
    (CASE WHEN amtRows = 1 THEN 0 ELSE priceSum) as priceSum,
    amtRows,
    clientID,
    selfPrice,
    paymentInstrumentItemID
FROM (

    SELECT
        pir.paymentInstrumentItemID AS id,
        SUM(pir.priceChange) AS priceSum,
        COUNT(pir.priceChange) as as amtRows,
        pir.clientInfoID AS clientID,
        IFNULL(SUM(pir.costChange),0) AS selfPrice,
        pir.paymentInstrumentItemID
    FROM 
        paymentInstrumentRegister as pir
    GROUP BY 
        pir.paymentInstrumentItemID 

) tmp