当前位置：首页 > 数据库 > 正文内容

Sql根据不同条件统计总数

2024-11-29数据库2

经常会遇到根据不同的条件统计总数的问题，一般有两种写法：count和sum都可以
数据准备：

方法一：Count

代码：

SELECT
    COUNT(
        CASE
        WHEN age > 20
        AND age < 25 THEN
            1
        ELSE
            NULL
        END
    ) AS cnt0,
    COUNT(
        CASE
        WHEN age >= 25
        AND age < 30 THEN
            1
        ELSE
            NULL
        END
    ) AS cnt1
FROM
    USER;

结果：

方法二：sum

代码：

SELECT
    SUM(
        CASE
        WHEN age > 20
        AND age < 25 THEN
            1
        ELSE
            0
        END
    ) AS cnt0,
    SUM(
        CASE
        WHEN age >= 25
        AND age < 30 THEN
            1
        ELSE
            0
        END
    ) AS cnt1
FROM
    USER;

结果：

当然也可以和count代码一样ELSE后面也写为NULL

SELECT
    SUM(
        CASE
        WHEN age > 20
        AND age < 25 THEN
            1
        ELSE
            NULL
        END
    ) AS cnt0,
    SUM(
        CASE
        WHEN age >= 25
        AND age < 30 THEN
            1
        ELSE
            NULL
        END
    ) AS cnt1
FROM
    USER;