有没有一种方法可以包含所有案例的总和？

dnph8jn4 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(283)

给定sql：有没有方法引入结果集的总数？

SELECT 
    SUM(CASE
        WHEN status = 3 THEN 1
        ELSE 0
    END) AS Open,
    SUM(CASE
        WHEN status = 4 THEN 1
        ELSE 0
    END) AS Close
FROM
    Table1
WHERE
    id = 2;

结果：

Open,Close
5,5

期望结果：

Open,Close,Total
5,5,10

mysql

来源：https://stackoverflow.com/questions/51567864/is-there-a-way-to-include-the-sum-of-all-cases

3条答案

按热度按时间

ymdaylpp1#

使用子查询

select open,close,open+close as total from
    (
       SELECT 
        SUM(CASE  WHEN status = 3 THEN 1
             ELSE 0
             END ) AS Open,
       SUM(CASE   WHEN status = 4 THEN 1
           ELSE 0
           END) AS Close
    FROM
    Table1
    WHERE   id = 2   ) as T

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

wfypjpf42#

再加一句话

SELECT 
    SUM(CASE
        WHEN status = 3 THEN 1
        ELSE 0
    END) AS Open,
    SUM(CASE
        WHEN status = 4 THEN 1
        ELSE 0
    END) AS Close
    SUM(CASE
        WHEN status IN (3, 4) THEN 1
        ELSE 0
    END) AS Total
FROM
    Table1
WHERE
    id = 2;

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

2ic8powd3#

您可以使用cte：

WITH sumCase AS (
SELECT 
    SUM(CASE
        WHEN status = 3 THEN 1
        ELSE 0
    END) AS Open,
    SUM(CASE
        WHEN status = 4 THEN 1
        ELSE 0
    END) AS Close
FROM
    Table1
WHERE
    id = 2;)
SELECT Open,Close, Open + Close AS Total FROM Table1;

http://www.mysqltutorial.org/mysql-cte/

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

我来回答

有没有一种方法可以包含所有案例的总和？

3条答案

相关问题

热门标签

最新问答