sql - 存储数据范围的颜色并在 SQL Server 中的 case 语句中使用

Question

我有一个数据集如下。小提琴网址

我需要使用上表中的运算符获取给定数字的颜色。

例如;

当我调用该函数getColor(6)时，应该返回PURPLE。

当我调用该函数getColor(0)时，应该返回RED。

我怎么能做到这一点？

标签： sqlsql-servertsqlcomparison-operators

Answer 1

CASE最简单的解决方案是使用表达式为每个运算符创建匹配条件：

DECLARE @val NUMERIC(10) = 6;

SELECT /* TOP 1 */ *
FROM #range_color
CROSS APPLY (SELECT @val - range_number) AS ca(diff)
WHERE CASE WHEN diff <  0 AND range_operator = '<'  THEN 1 END = 1
OR    CASE WHEN diff <= 0 AND range_operator = '<=' THEN 1 END = 1
OR    CASE WHEN diff >= 0 AND range_operator = '>=' THEN 1 END = 1
OR    CASE WHEN diff >  0 AND range_operator = '>'  THEN 1 END = 1
ORDER BY ABS(diff)