I have a SELECT with two columns I'd like to merge based on the condition that if the left one is null it should choose the right column but when the left column is not null it should always choose the left.

我有一个SELECT有两列，我想根据条件合并，如果左边的列为null，它应该选择右列，但是当左列不为空时，它应该总是选择左边。

scenario

What I have on the left is what I currently Have and I'd like to get the table on the right

我左边的是我现在拥有的东西，我想把桌子放在右边

AlphabetTable

+-------+------+    +---------+
| A     | B    |    | Desired |
+-------+------+    |---------+
| null  | 1    |    | 1       |
| null  | 1    |    | 1       |
|  2    | null |    | 2       |
|  2    | 3    |    | 2       |
|  2    | 5    |    |  2      |
|  null | null |    | null    |
+-------+------+    +---------+

SQL for left table above

SELECT 
    CAST((select sum(Total) from LettersTable LT
                                WHERE LT.ID = ID
                                AND LT.Count > 5) 
                                AS INT) as A
    CAST((select sum(Total) from LettersTable LT
                                WHERE LT.ID = ID
                                AND LT.Count > 5) 
                                AS INT) as B
From IDTable

Heres what I have so far

这是我到目前为止所拥有的

SELECT 
        CASE WHEN
            CAST((select sum(Total) from LettersTable LT
                        WHERE LT.ID = AT.ID
                        AND LT.Count > 5) 
                        AS INT) IS NULL             
            THEN 
                (select sum(Total) from LettersTable LT
                        WHERE LT.ID = AT.ID
                        AND LT.Count > 5) 
                        AS INT)
            ELSE  
                (select sum(Total) from LettersTable LT
                        WHERE LT.ID = AT.ID
                        AND LT.Count > 5) 
                        AS INT)
 FROM AlphabetTable AT

4 个解决方案

#1

You can achieve this with ISNULL Condition as shown below

您可以使用ISNULL Condition实现此目的，如下所示

    SELECT 
        CAST((select sum(Total) from LettersTable LT
                                    WHERE LT.ID = ID
                                    AND LT.Count > 5) 
                                    AS INT) as A,
        CAST((select sum(Total) from LettersTable LT
                                    WHERE LT.ID = ID
                                    AND LT.Count > 5) 
                                    AS INT) as B
    INTO #tmpIDTable
    From IDTable

    SELECT ISNULL(A,B) FROM #tmpIDTable

#2

here are a couple of options.. possibly

这里有几个选项..可能

;with mycte as (
select null as a, 1 as b
union all
select null as a, 1 as b
union all
select 2 as a, null as b
union all
select 2 as a, 3 as b
union all
select 2 as a, 5 as b
union all
select null as a, null as b

)

Select 
*,
CONCAT(a,case when b is not null and a is not null then (select null) else b end) case_statement
,COALESCE (a,b)  coalesce_statement
 from mycte

#3

Assuming that you have the columns A and B available, one of the below should work:

假设您有A列和B列可用，则以下其中一项应该有效：

SELECT 
CASE WHEN A IS NULL THEN B ELSE A END AS DESIRED_COL    
FROM
YOUR_TABLE;

Or as @NetMage suggested:

或者@NetMage建议：

SELECT 
CASE WHEN (A IS NOT NULL OR B IS NOT NULL) THEN COALESCE(A,B) ELSE NULL END AS DESIRED_COL
FROM YOUR_TABLE;

Since COALESCE will throw an error if both columns are NULL, you would still require a case when to deal with that condition.

由于如果两列都为NULL，COALESCE将抛出错误，您仍然需要处理该条件的情况。

#4

try this

尝试这个

;with mycte as (
select null as a, 1 as b
union all
select null as a, 1 as b
union all
select 2 as a, null as b
union all
select 2 as a, 3 as b
union all
select 2 as a, 5 as b
union all
select null as a, null as b

)
SELECT ISNULL(a,b) FROM mycte

#1