SQL统计损坏门数量（含无损坏区块）及长查询复用方法咨询

1. 统计损坏门数量（包含无损坏的记录） #

要实现包含Block B这类无损坏门的记录（Count_Broken为0），不能直接在WHERE子句中过滤Broken='Y'，而是要在聚合函数中判断损坏状态。以下是适用的SQL语句：

SELECT 
    Block,
    key_Number,
    SUM(CASE WHEN Broken = 'Y' THEN 1 ELSE 0 END) AS Count_Broken
FROM doorStatus
GROUP BY Block, key_Number
ORDER BY Block, key_Number;

说明： #

• SUM(CASE...)会对每条记录判断：如果Broken='Y'则计1，否则计0，最终求和得到损坏门数量。
• GROUP BY Block, key_Number确保按楼栋和钥匙编号分组统计，不会遗漏任何分组（包括Broken全为N的组）。
• 执行后会得到你期望的结果，包括Block B的013组，Count_Broken显示为0。

2. 长查询的复用与变量定义 #

不同数据库系统的变量定义方式略有差异，以下是几种常见场景的实现：

MySQL/MariaDB #

• 用SET定义变量，后续直接引用：

-- 定义变量存储查询条件或子查询
SET @target_blocks = 'A,B';
SET @long_subquery = '(SELECT key_Number FROM doorStatus WHERE Broken = ''Y'')';

-- 复用变量的查询
SELECT 
    Block,
    key_Number,
    SUM(CASE WHEN Broken = 'Y' THEN 1 ELSE 0 END) AS Count_Broken
FROM doorStatus
WHERE Block IN (@target_blocks)
GROUP BY Block, key_Number;

• 也可以创建存储过程封装整个长查询，通过参数传递变量：

DELIMITER //
CREATE PROCEDURE GetBrokenDoorCount(IN target_block VARCHAR(10))
BEGIN
    SELECT 
        Block,
        key_Number,
        SUM(CASE WHEN Broken = 'Y' THEN 1 ELSE 0 END) AS Count_Broken
    FROM doorStatus
    WHERE Block = target_block
    GROUP BY Block, key_Number;
END //
DELIMITER ;

-- 调用存储过程
CALL GetBrokenDoorCount('B');

SQL Server #

• 用DECLARE定义局部变量：

DECLARE @target_block VARCHAR(10) = 'B';

SELECT 
    Block,
    key_Number,
    SUM(CASE WHEN Broken = 'Y' THEN 1 ELSE 0 END) AS Count_Broken
FROM doorStatus
WHERE Block = @target_block
GROUP BY Block, key_Number;

• 复杂查询可以封装为视图或表值函数实现复用。

PostgreSQL #

• 用WITH子句定义可复用的公共表达式（适合单次会话内复用）：

WITH broken_door_stats AS (
    SELECT 
        Block,
        key_Number,
        SUM(CASE WHEN Broken = 'Y' THEN 1 ELSE 0 END) AS Count_Broken
    FROM doorStatus
    GROUP BY Block, key_Number
)
-- 复用公共表达式
SELECT * FROM broken_door_stats WHERE Block = 'B';

• 也可以用SET定义会话级变量，或创建函数封装查询。

Oracle #

• 用DECLARE定义变量，结合执行语句：

DECLARE
    v_target_block VARCHAR2(10) := 'B';
BEGIN
    FOR rec IN (
        SELECT 
            Block,
            key_Number,
            SUM(CASE WHEN Broken = 'Y' THEN 1 ELSE 0 END) AS Count_Broken
        FROM doorStatus
        WHERE Block = v_target_block
        GROUP BY Block, key_Number
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.Block || ' ' || rec.key_Number || ' ' || rec.Count_Broken);
    END LOOP;
END;
/

• 长期复用可创建视图或存储过程。

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