从case语句中选择值列表
从case语句中选择值列表
提问于 2016-10-25 13:33:21
我有一个变量myvalue，它是一个具有值"TRUE"或"TRUE,FALSE"的字符串参数。
当myvalue = "TRUE"时，我只想选择N/A。当myvalue = "TRUE,FALSE"时，我想要选择一个从0到23的值列表(代表一天中的离散小时)，并且每个值都有它自己的行。
这是我所拥有的-我在这里错过了什么？我希望能够做到这一点，而无需在表中存储任何东西(临时或真实的)。语法错误。
SELECT
CASE myvalue
WHEN "TRUE"
THEN "N/A"
WHEN "TRUE,FALSE" THEN (SELECT "0" FROM DUAL
UNION SELECT "1" FROM DUAL
UNION SELECT "2" FROM DUAL
UNION SELECT "3" FROM DUAL
UNION SELECT "4" FROM DUAL
UNION SELECT "5" FROM DUAL
UNION SELECT "6" FROM DUAL
UNION SELECT "7" FROM DUAL
UNION SELECT "8" FROM DUAL
UNION SELECT "9" FROM DUAL
UNION SELECT "10" FROM DUAL
UNION SELECT "11" FROM DUAL
UNION SELECT "12" FROM DUAL
UNION SELECT "13" FROM DUAL
UNION SELECT "14" FROM DUAL
UNION SELECT "15" FROM DUAL
UNION SELECT "16" FROM DUAL
UNION SELECT "17" FROM DUAL
UNION SELECT "18" FROM DUAL
UNION SELECT "19" FROM DUAL
UNION SELECT "20" FROM DUAL
UNION SELECT "21" FROM DUAL
UNION SELECT "22" FROM DUAL
UNION SELECT "23" FROM DUAL
)
END as optionz
FROM
DUAL错误消息：Error Code: 1242. Subquery returns more than 1 row
回答 6
Stack Overflow用户
回答已采纳
发布于 2016-10-25 13:56:03
以下是我要尝试的：
select x from (select myValue as y) t join
(select 'N/A' as x, 'TRUE' as y
union
select '1' as x, "TRUE,FALSE" as y
union
select '2' as x, "TRUE,FALSE" as y
/* keep adding your values here */
) t2
on t.y = t2.y;这使连接到一些东西有点像一个临时表，并将为'TRUE/FALSE‘大小写提供很多行，而对于'TRUE’情况只提供一行，这就是您想要的。
Stack Overflow用户
发布于 2016-10-25 13:43:15
CASE返回一个感叹而不是SELECT你不能返回一个UNION
这是OK
SELECT CASE WHEN 1=2
THEN '2'
ELSE (SELECT 1 as d FROM DUAL)
END as res
FROM DUAL;这个不是OK
SELECT CASE WHEN 1=2
THEN '2'
ELSE (SELECT 1 as d FROM DUAL UNION
SELECT 2 as d FROM DUAL
)
END as res
FROM DUAL;你想要演示
--SELECT @myValue := 'TRUE';
SELECT @myValue := 'TRUE,FALSE';
SELECT val
FROM (SELECT "TRUE" as parameter, "N/A" as val FROM DUAL UNION
SELECT "TRUE,FALSE", "1" FROM DUAL UNION
SELECT "TRUE,FALSE", "2" FROM DUAL UNION
SELECT "TRUE,FALSE", "3" FROM DUAL UNION
SELECT "TRUE,FALSE", "4" FROM DUAL UNION
SELECT "TRUE,FALSE", "5" FROM DUAL UNION
SELECT "TRUE,FALSE", "6" FROM DUAL UNION
SELECT "TRUE,FALSE", "7" FROM DUAL UNION
SELECT "TRUE,FALSE", "8" FROM DUAL UNION
SELECT "TRUE,FALSE", "9" FROM DUAL UNION
SELECT "TRUE,FALSE", "10" FROM DUAL
) t
WHERE t.parameter = @myValue ;Stack Overflow用户
发布于 2016-10-25 13:45:35
我觉得你的问题有点奇怪。但是，这可能会起作用(如果您正在使用Oracle SQL)：
SELECT HOURS_OF_DAY
from
(SELECT myvalue as myvalue FROM dual) a,
(SELECT '0' hours_of_day FROM DUAL
UNION SELECT '1' FROM DUAL
UNION SELECT '2' FROM DUAL
UNION SELECT '3' FROM DUAL
UNION SELECT '4' FROM DUAL
UNION SELECT '5' FROM DUAL
UNION SELECT '6' FROM DUAL
UNION SELECT '7' FROM DUAL
UNION SELECT '8' FROM DUAL
UNION SELECT '9' FROM DUAL
UNION SELECT '10' FROM DUAL
UNION SELECT '11' FROM DUAL
UNION SELECT '12' FROM DUAL
UNION SELECT '13' FROM DUAL
UNION SELECT '14' FROM DUAL
UNION SELECT '15' FROM DUAL
UNION SELECT '16' FROM DUAL
UNION SELECT '17' FROM DUAL
UNION SELECT '18' FROM DUAL
UNION SELECT '19' FROM DUAL
UNION SELECT '20' FROM DUAL
UNION SELECT '21' FROM DUAL
UNION SELECT '22' FROM DUAL
UNION SELECT '23' FROM DUAL
) b
WHERE myvalue = 'TRUE,FALSE'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40241431
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号