添加列值的Case语句

I need to write a case statement that will return

我需要编写一个将返回的case语句

“1 of 3” if someone voted in one of three elections, 
“2 of 3” if someone voted in two of three elections, 
“3 of 3” if someone voted in three of three elections,

The problem is that some of the values are a varchar and some are null and I cannot add them together. This is my idea but I cannot get it to work.

问题是有些值是varchar，有些是null，我不能将它们加在一起。这是我的想法，但我不能让它工作。

select 
id,
CASE 
     WHEN race1 + race2 + race3 = 0 then '0-3'
     WHEN race1 + race2 + race3 = 1 then '1-3'
     WHEN race1 + race2 + race3 = 2 then '2-3'
     WHEN race1 + race2 + race3 = 3 then '3-3'
     WHEN race1 + race2 + race3 is null then 'Unknown'
 END AS General_Turnout
from test4

http://sqlfiddle.com/#!3/cac66/3

3 个解决方案

#1

This should work:

这应该工作：

select 
id,
    CAST( (CASE WHEN race1 IS NOT NULL THEN 1 ELSE 0 END)
   +(CASE WHEN race2 IS NOT NULL THEN 1 ELSE 0 END)
   +(CASE WHEN race3 IS NOT NULL THEN 1 ELSE 0 END) AS CHAR) + '-3'
AS General_Turnout
from test4

#2

use COELESCE(value,0) so that null is taken as zero

使用COELESCE（value，0）使null为零

#3

From what you posted on SQLfiddle, I assume NULL means did not vote and any string means did vote. You can't add the non-numeric strings you have in columns race1, race2, and race3, but this should work:

根据你在SQLfiddle上发布的内容，我假设NULL意味着没有投票，任何字符串意味着投票。您不能在race1，race2和race3列中添加非数字字符串，但这应该有效：

SELECT 
  id,
  CAST(
    CASE WHEN race1 IS NOT NULL then 1 ELSE 0 END +
    CASE WHEN race2 IS NOT NULL then 1 ELSE 0 END +
    CASE WHEN race3 IS NOT NULL then 1 ELSE 0 END
  AS CHAR) + '-3' AS General_Turnout
FROM test4

#1