SQL SQL中的case语句，如何返回多个变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8358642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
case statement in SQL, how to return multiple variables?
提问by yzhang
I would like to return multiple values in my case statement, such as :
我想在我的 case 语句中返回多个值，例如：
SELECT
CASE
WHEN <condition 1> THEN <value1=a1, value2=b1>
WHEN <condition 2> THEN <value1=a2, value2=b2>
ELSE <value1=a3, value3=b3>
END
FROM <table>
Of course I can write the case condition multiple times, each time return one value. However, as I have many condition need to fit, say 100. It is not good to repeat case condition again and again.
当然我可以多次写case条件，每次返回一个值。但是，因为我有很多条件需要适应，比如100。一次又一次地重复案例条件是不好的。
Another question I would like to ask, what happend if one record fit multiple condition? does that mean it will return all of them or just the last one? e.g. condition 1 may become a subset of condition 2. what will happen?
我想问的另一个问题，如果一个记录符合多个条件会发生什么？这是否意味着它将返回所有这些或仅返回最后一个？例如，条件 1 可能成为条件 2 的子集。会发生什么？
采纳答案by MatBailie
The basic way, unfortunately, is to repeat yourself.
不幸的是，基本的方法是重复自己。
SELECT
CASE WHEN <condition 1> THEN <a1> WHEN <condition 2> THEN <a2> ELSE <a3> END,
CASE WHEN <condition 1> THEN <b1> WHEN <condition 2> THEN <b2> ELSE <b3> END
FROM
<table>
Fortunately, most RDBMS are clever enough to NOT have to evaluate the conditions multiple times. It's just redundant typing.
幸运的是，大多数 RDBMS 足够聪明，不必多次评估条件。这只是多余的打字。
In MS SQL Server (2005+) you could possible use CROSS APPLY as an alternative to this. Though I have no idea how performant it is...
在 MS SQL Server (2005+) 中，您可以使用 CROSS APPLY 作为替代方案。虽然我不知道它的性能如何......
SELECT
*
FROM
<table>
CROSS APPLY
(
SELECT a1, b1 WHERE <condition 1>
UNION ALL
SELECT a2, b2 WHERE <condition 2>
UNION ALL
SELECT a3, b3 WHERE <condition 3>
)
AS case_proxy
The noticable downside here is that there is no ELSE equivalent and as all the conditions couldall return values, they need to be framed such that only one can ever be true at a time.
这里值得注意的缺点是没有 ELSE 等价物，并且由于所有条件都可能返回值，因此需要将它们设置为一次只有一个为真。
EDIT
编辑
If Yuck's answer is changed to a UNION rather than JOIN approach, it becomes very similar to this. The main difference, however, being that this only scans the input data set once, rather than once per condition (100 times in your case).
如果 Yuck 的答案更改为 UNION 而不是 JOIN 方法，它会变得与此非常相似。但是，主要区别在于这仅扫描输入数据集一次，而不是每个条件扫描一次（在您的情况下为 100 次）。
EDIT
编辑
I've also noticed that you maymean that the values returned by the CASE statements are fixed. All records that match the same condition get the exact sames valuesin value1 and value2. This could be formed like this...
我还注意到您的意思可能是 CASE 语句返回的值是固定的。匹配相同条件的所有记录在 value1 和 value2 中获得完全相同的值。这可以像这样形成......
WITH
checked_data AS
(
SELECT
CASE WHEN <condition1> THEN 1
WHEN <condition2> THEN 2
WHEN <condition3> THEN 3
...
ELSE 100
END AS condition_id,
*
FROM
<table>
)
,
results (condition_id, value1, value2) AS
(
SELECT 1, a1, b1
UNION ALL
SELECT 2, a2, b2
UNION ALL
SELECT 3, a3, b3
UNION ALL
...
SELECT 100, a100, b100
)
SELECT
*
FROM
checked_data
INNER JOIN
results
ON results.condition_id = checked_data.condition_id
回答by Yuck
A CASEstatement can return only one value.
一条CASE语句只能返回一个值。
You may be able to turn this into a subquery and then JOINit to whatever other relations you're working with. For example (using SQL Server 2K5+ CTEs):
您可以将其转换为子查询，然后将JOIN其转换为您正在使用的任何其他关系。例如（使用 SQL Server 2K5+ CTE）：
WITH C1 AS (
SELECT a1 AS value1, b1 AS value2
FROM table
WHERE condition1
), C2 AS (
SELECT a2 AS value1, b2 AS value2
FROM table
WHERE condition2
), C3 AS (
SELECT a3 AS value1, b3 AS value2
FROM table
WHERE condition3
)
SELECT value1, value2
FROM -- some table, joining C1, C2, C3 CTEs to get the cased values
;
回答by JNK
CASEby definition only returns a single value. Ever.
CASE根据定义只返回一个值。曾经。
It also (almost always) short circuits, which means if your first condition is met no other checks are run.
它还（几乎总是）短路，这意味着如果满足您的第一个条件，则不会运行其他检查。
回答by HLGEM
In your case you would use two case staements, one for each value you want returned.
在您的情况下，您将使用两个 case staements，一个用于您要返回的每个值。
回答by Morteza ebrahim nejad
You can return multiple value inside a xml data type in "case" expression, then extract them, also "else" block is available
您可以在“case”表达式中的 xml 数据类型中返回多个值，然后提取它们，“else”块也可用
SELECT
xmlcol.value('(value1)[1]', 'NVARCHAR(MAX)') AS value1,
xmlcol.value('(value2)[1]', 'NVARCHAR(MAX)') AS value2
FROM
(SELECT CASE
WHEN <condition 1> THEN
CAST((SELECT a1 AS value1, b1 AS value2 FOR XML PATH('')) AS XML)
WHEN <condition 2> THEN
CAST((SELECT a2 AS value1, b2 AS value2 FOR XML PATH('')) AS XML)
ELSE
CAST((SELECT a3 AS value1, b3 AS value2 FOR XML PATH('')) AS XML)
END AS xmlcol
FROM <table>) AS tmp
回答by yvtong
or you can
或者你可以
SELECT
String_to_array(CASE
WHEN <condition 1> THEN a1||','||b1
WHEN <condition 2> THEN a2||','||b2
ELSE a3||','||b3
END, ',') K
FROM <table>
回答by Angelo Fuchs
You could use a subselect combined with a UNION. Whenever you can return the same fields for more than one condition use OR with the parenthesis as in this example:
您可以将子选择与 UNION 结合使用。每当您可以为多个条件返回相同的字段时，请使用 OR 和括号，如本例所示：
SELECT * FROM
(SELECT val1, val2 FROM table1 WHERE (condition1 is true)
OR (condition2 is true))
UNION
SELECT * FROM
(SELECT val5, val6 FROM table7 WHERE (condition9 is true)
OR (condition4 is true))
回答by Angelo Fuchs
In a SQL CASE clause, the first successfully matched condition is applied and any subsequent matching conditions are ignored.
在 SQL CASE 子句中，应用第一个成功匹配的条件，并忽略任何后续匹配条件。
