<tfoot id='p1pni'></tfoot>
        <i id='p1pni'><tr id='p1pni'><dt id='p1pni'><q id='p1pni'><span id='p1pni'><b id='p1pni'><form id='p1pni'><ins id='p1pni'></ins><ul id='p1pni'></ul><sub id='p1pni'></sub></form><legend id='p1pni'></legend><bdo id='p1pni'><pre id='p1pni'><center id='p1pni'></center></pre></bdo></b><th id='p1pni'></th></span></q></dt></tr></i><div id='p1pni'><tfoot id='p1pni'></tfoot><dl id='p1pni'><fieldset id='p1pni'></fieldset></dl></div>
      1. <legend id='p1pni'><style id='p1pni'><dir id='p1pni'><q id='p1pni'></q></dir></style></legend>

          <bdo id='p1pni'></bdo><ul id='p1pni'></ul>
      2. <small id='p1pni'></small><noframes id='p1pni'>

        如果列存在则选择 columnValue 否则为 null

        Select columnValue if the column exists otherwise null(如果列存在则选择 columnValue 否则为 null)

            <small id='vdeJV'></small><noframes id='vdeJV'>

                <tbody id='vdeJV'></tbody>
            1. <tfoot id='vdeJV'></tfoot>
              <i id='vdeJV'><tr id='vdeJV'><dt id='vdeJV'><q id='vdeJV'><span id='vdeJV'><b id='vdeJV'><form id='vdeJV'><ins id='vdeJV'></ins><ul id='vdeJV'></ul><sub id='vdeJV'></sub></form><legend id='vdeJV'></legend><bdo id='vdeJV'><pre id='vdeJV'><center id='vdeJV'></center></pre></bdo></b><th id='vdeJV'></th></span></q></dt></tr></i><div id='vdeJV'><tfoot id='vdeJV'></tfoot><dl id='vdeJV'><fieldset id='vdeJV'></fieldset></dl></div>
              • <legend id='vdeJV'><style id='vdeJV'><dir id='vdeJV'><q id='vdeJV'></q></dir></style></legend>
                  <bdo id='vdeJV'></bdo><ul id='vdeJV'></ul>
                • 本文介绍了如果列存在则选择 columnValue 否则为 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)(超出最大存储过程、函数、触发器或视图嵌套级别(限制 32))
                  How to View Oracle Stored Procedure using SQLPlus?(如何使用 SQLPlus 查看 Oracle 存储过程?)
                  How to debug stored procedure in VS 2015?(如何在 VS 2015 中调试存储过程?)
                  Set the variable result, from query(设置变量结果,来自查询)
                  What is dynamic SQL?(什么是动态 SQL?)
                  Mysql - How to quit/exit from stored procedure(Mysql - 如何退出/退出存储过程)

                      <bdo id='al7Ns'></bdo><ul id='al7Ns'></ul>

                          <legend id='al7Ns'><style id='al7Ns'><dir id='al7Ns'><q id='al7Ns'></q></dir></style></legend>
                          • <tfoot id='al7Ns'></tfoot>

                              <tbody id='al7Ns'></tbody>
                            <i id='al7Ns'><tr id='al7Ns'><dt id='al7Ns'><q id='al7Ns'><span id='al7Ns'><b id='al7Ns'><form id='al7Ns'><ins id='al7Ns'></ins><ul id='al7Ns'></ul><sub id='al7Ns'></sub></form><legend id='al7Ns'></legend><bdo id='al7Ns'><pre id='al7Ns'><center id='al7Ns'></center></pre></bdo></b><th id='al7Ns'></th></span></q></dt></tr></i><div id='al7Ns'><tfoot id='al7Ns'></tfoot><dl id='al7Ns'><fieldset id='al7Ns'></fieldset></dl></div>
                          • <small id='al7Ns'></small><noframes id='al7Ns'>