SQL中的case语句，如何返回多个变量? [英] case statement in SQL, how to return multiple variables?

问题描述

我想在我的 case 语句中返回多个值，例如:

SELECT案件当<条件1>THEN <value1=a1, value2=b1>当<条件2>然后<value1=a2，value2=b2>ELSE<value1=a3，value3=b3>结尾发件人<表>

当然我可以多次写case条件，每次返回一个值.但是，因为我有很多条件需要适应，比如说100.一次又一次地重复case条件是不好的.

我想问的另一个问题，如果一个记录符合多个条件会发生什么?这是否意味着它将返回所有这些或仅返回最后一个?例如条件 1 可能成为条件 2 的子集.会发生什么?

解决方案

不幸的是，基本的方法是重复自己.

SELECT当<条件1>时的情况然后<a1>当<条件2>然后<a2>其他<a3>结尾，当<条件1>时的情况然后<b1>当<条件2>然后<b2>其他<b3>结尾从<表格>

幸运的是，大多数 RDBMS 足够聪明，不必多次评估条件.这只是多余的打字.

在 MS SQL Server (2005+) 中，您可以使用 CROSS APPLY 作为替代方案.虽然我不知道它的性能如何...

SELECT*从<表格>交叉申请(SELECT a1, b1 WHERE <condition 1>联合所有SELECT a2, b2 WHERE <condition 2>联合所有SELECT a3, b3 WHERE <condition 3>)AS case_proxy

这里值得注意的缺点是没有 ELSE 等价物，并且由于所有条件可能都返回值，因此需要将它们设置为一次只有一个为真.>

编辑

如果将 Yuck 的答案改为 UNION 而不是 JOIN 方法，它会变得与此非常相似.但是，主要区别在于这仅扫描输入数据集一次，而不是每个条件扫描一次(在您的情况下为 100 次).

编辑

我还注意到您可能表示 CASE 语句返回的值是固定的.匹配相同条件的所有记录在 value1 和 value2 中获得完全相同的值.这可以像这样形成......

WITH已检查数据 AS(选择当 <condition1> 时的情况那么 1当<条件2>那么 2当<条件3>然后 3...其他 100END AS condition_id,*从<表格>),结果 (condition_id, value1, value2) AS(选择 1、a1、b1联合所有选择 2、a2、b2联合所有选择 3、a3、b3联合所有...选择 100、a100、b100)选择*从检查数据内部联接结果ON results.condition_id = checked_data.condition_id

I would like to return multiple values in my case statement, such as :

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.

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?

解决方案

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.

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...

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 could all return values, they need to be framed such that only one can ever be true at a time.

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).

EDIT

I've also noticed that you may mean that the values returned by the CASE statements are fixed. All records that match the same condition get the exact sames values in value1 and value2. This could be formed like this...

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

这篇关于SQL中的case语句，如何返回多个变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！