技术求助：如何用SQL计算有/无股权众筹公司的占比（附错误代码）

方法1：使用CASE表达式分组统计（更简洁高效） #

SELECT
    CASE 
        WHEN equity_crowdfunding > 0 THEN 'with_crowdfunding'
        ELSE 'no_crowdfunding'
    END AS category,
    COUNT(market) AS count,
    ROUND(COUNT(market) * 100.0 / (SELECT COUNT(market) FROM investment_finance), 0) AS percentage
FROM investment_finance
GROUP BY category;

方法2：修正原CTE写法 #

WITH stats AS (
    SELECT
        COUNT(CASE WHEN equity_crowdfunding > 0 THEN market END) AS with_count,
        COUNT(CASE WHEN equity_crowdfunding = 0 THEN market END) AS no_count,
        COUNT(market) AS total
    FROM investment_finance
)
SELECT
    'with_crowdfunding' AS category,
    with_count AS count,
    ROUND(with_count * 100.0 / total, 0) AS percentage
FROM stats
UNION ALL
SELECT
    'no_crowdfunding' AS category,
    no_count AS count,
    ROUND(no_count * 100.0 / total, 0) AS percentage
FROM stats;

原SQL的问题说明 #

• 原CTE定义的三个统计结果都是单个聚合值，但主查询直接从investment_finance表查询market字段，导致原表每行数据都会输出，无法得到预期的两行聚合结果。
• 计算百分比时未处理整数除法问题：如果两个整数相除，结果会被截断为整数（比如5/105会得到0），需要乘以100.0将数值转为浮点数后再计算。
• 未正确将两类统计结果合并为两行输出，而是错误地关联了原表字段。

内容的提问来源于stack exchange，提问作者tutubhie1584