如果列存在则选择 columnValue 否则为 null [英] Select columnValue if the column exists otherwise null

问题描述

我想知道是否可以在列存在的情况下选择列的值，否则就选择 null.换句话说，当列不存在时，我想提升"select 语句来处理这种情况.

SELECT uniqueId, 第二列,/*WHEN columnThree 存在 THEN columnThree ELSE NULL END*/AS columnThreeFROM (子查询) s

请注意，我正在巩固我的数据模型和设计.我希望在接下来的几周内排除这种逻辑，但我真的很想解决这个问题，因为数据模型修复是一项比我现在想要解决的更耗时的工作.

另请注意，我希望能够在一个查询中完成此操作.所以我不是在寻找像

这样的答案<块引用>

首先检查您的子查询中有哪些列.然后修改你的查询以适当处理子查询中的列.

解决方案

您无法使用简单的 SQL 语句完成此操作.除非表中的所有表和列引用都存在，否则不会编译 SQL 查询.

如果子查询"不可用，您可以使用动态 SQL 执行此操作.是表引用或视图.

在动态 SQL 中，您可以执行以下操作:

声明@sql nvarchar(max) = 'SELECT uniqueId, columnTwo, '+(存在时的情况(选择*来自 INFORMATION_SCHEMA.COLUMNS其中 tablename = @TableName 和columnname = 'ColumnThree' -- 如果您愿意，还有模式名称)然后是第三列"否则 'NULL 作为 ColumnThree'结束) + 'FROM (select * from '+@SourceName+' s';exec sp_executesql @sql;

对于实际的子查询，您可以通过检查子查询是否返回具有该列名的内容来近似相同的内容.一种方法是运行查询:select top 0 * into #temp from () s 然后检查 #temp 中的列.

我通常不会更新这些老问题，而是基于下面的评论.如果子查询"中的每一行都有唯一标识符，则可以运行以下命令:

选择...., -- 除了第三列之外的所有内容(选择 column3 - 不合格！从 t t2其中 t2.pk = t.pk) 作为第 3 列从 t 交叉连接(值 (NULL)) v(columnthree);

如果外部查询不存在，子查询将从外部查询中选取 column3.然而，这关键取决于每一行都有一个唯一的标识符.该问题明确与子查询有关，没有理由期望行可以轻松唯一标识.

I'm wondering if I can select the value of a column if the column exists and just select null otherwise. In other words I'd like to "lift" the select statement to handle the case when the column doesn't exist.

SELECT uniqueId
    ,  columnTwo
    ,  /*WHEN columnThree exists THEN columnThree ELSE NULL END*/ AS columnThree
FROM (subQuery) s

Note, I'm in the middle to solidifying my data model and design. I hope to exclude this logic in the coming weeks, but I'd really like to move beyond this problem right because the data model fix is a more time consuming endeavor than I'd like to tackle now.

Also note, I'd like to be able to do this in one query. So I'm not looking for an answer like

check what columns are on your sub query first. Then modify your query to appropriately handle the columns on your sub query.

解决方案

You cannot do this with a simple SQL statement. A SQL query will not compile unless all table and column references in the table exist.

You can do this with dynamic SQL if the "subquery" is a table reference or a view.

In dynamic SQL, you would do something like:

declare @sql nvarchar(max) = '
SELECT uniqueId, columnTwo, '+
    (case when exists (select *
                       from INFORMATION_SCHEMA.COLUMNS 
                       where tablename = @TableName and
                             columnname = 'ColumnThree' -- and schema name too, if you like
                      )
          then 'ColumnThree'
          else 'NULL as ColumnThree'
     end) + '
FROM (select * from '+@SourceName+' s
';

exec sp_executesql @sql;

For an actual subquery, you could approximate the same thing by checking to see if the subquery returned something with that column name. One method for this is to run the query: select top 0 * into #temp from (<subquery>) s and then check the columns in #temp.

EDIT:

I don't usually update such old questions, but based on the comment below. If you have a unique identifier for each row in the "subquery", you can run the following:

select t.. . .,  -- everything but columnthree
       (select column3   -- not qualified!
        from t t2
        where t2.pk = t.pk
       ) as column3
from t cross join
     (values (NULL)) v(columnthree);

The subquery will pick up column3 from the outer query if it doesn't exist. However, this depends critically on having a unique identifier for each row. The question is explicitly about a subquery, and there is no reason to expect that the rows are easily uniquely identified.

这篇关于如果列存在则选择 columnValue 否则为 null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！