如何在SQL语句中传递参数值获取分段计算结果

你的CASE逻辑本身是正确的，只需通过参数绑定的方式传入x的值即可，不同数据库的实现方式略有不同，以下是主流数据库的具体实现方案：

MySQL/MariaDB #

方式1：使用用户变量（快速测试） #

SET @x = 7;
SELECT  
CASE
    WHEN @x>0 AND @x<=5 THEN 1
    WHEN @x>5 AND @x<=10 THEN 2
    WHEN @x>10 AND @x<=15 THEN 3
    WHEN @x>15 AND @x<=35 THEN 4
    WHEN @x>35 THEN 6
END AS result;

方式2：预处理语句（安全规范，避免注入） #

PREPARE stmt FROM 'SELECT CASE WHEN ? >0 AND ? <=5 THEN 1 WHEN ? >5 AND ? <=10 THEN 2 WHEN ? >10 AND ? <=15 THEN 3 WHEN ? >15 AND ? <=35 THEN 4 WHEN ? >35 THEN 6 END AS result';
SET @x = 7;
EXECUTE stmt USING @x, @x, @x, @x, @x, @x, @x;
DEALLOCATE PREPARE stmt;

PostgreSQL #

方式1：psql中直接使用占位符 #

SELECT  
CASE
    WHEN $1>0 AND $1<=5 THEN 1
    WHEN $1>5 AND $1<=10 THEN 2
    WHEN $1>10 AND $1<=15 THEN 3
    WHEN $1>15 AND $1<=35 THEN 4
    WHEN $1>35 THEN 6
END AS result
\bind 7
\g

方式2：创建函数复用逻辑 #

CREATE OR REPLACE FUNCTION get_calculation_result(x numeric) RETURNS integer AS $$
BEGIN
    RETURN CASE
        WHEN x>0 AND x<=5 THEN 1
        WHEN x>5 AND x<=10 THEN 2
        WHEN x>10 AND x<=15 THEN 3
        WHEN x>15 AND x<=35 THEN 4
        WHEN x>35 THEN 6
    END;
END;
$$ LANGUAGE plpgsql;

-- 调用函数获取结果
SELECT get_calculation_result(7);

SQL Server #

方式1：使用局部变量 #

DECLARE @x int = 7;
SELECT  
CASE
    WHEN @x>0 AND @x<=5 THEN 1
    WHEN @x>5 AND @x<=10 THEN 2
    WHEN @x>10 AND @x<=15 THEN 3
    WHEN @x>15 AND @x<=35 THEN 4
    WHEN @x>35 THEN 6
END AS result;

方式2：创建存储过程 #

CREATE PROCEDURE GetCalculationResult @x int
AS
BEGIN
    SELECT  
    CASE
        WHEN @x>0 AND @x<=5 THEN 1
        WHEN @x>5 AND @x<=10 THEN 2
        WHEN @x>10 AND @x<=15 THEN 3
        WHEN @x>15 AND @x<=35 THEN 4
        WHEN @x>35 THEN 6
    END AS result;
END;

-- 执行存储过程
EXEC GetCalculationResult @x=7;

如果是在应用程序中调用（如Java、Python），推荐使用对应语言的参数化查询API，既能实现参数传递，又能避免SQL注入风险。

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