选择不全为空的列 [英] Selecting columns that are not all null

问题描述

所以我有一个类似这样的查询-

So I have a query that looks like this -

select 
case when @subType = 1 or @subType = 2 then id end as Id,
case when  @subType = 3 then name end as name
case when @subType = 3 or @subType = 2 then address end as address
from 
table

所以我遇到的问题是，如果@subType为3，则名为ID的列将全部为空.然后，我不想返回整个专栏.相反，如果@subType为2，则name都将为null，所以我不希望整个列都如此.

So the issue I have is, that if @subType is 3, then the column named ID will all be null. I then don't want to return this entire column. Conversely to that, if @subType is 2, then name would all be null so I don't want that entire column.

推荐答案

您唯一的方法是使用动态SQL(如Gordon所述).如果这是一个查询视图，而不是函数视图，则可以执行以下操作:

The only way you could do this would be with dynamic SQL (as Gordon mentions). Provided this is a query, and not a function, view, then you could do this:

DECLARE @subType tinyint = 3;

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT ' +
           STUFF(CASE WHEN @subType IN (1,2) THEN N',' + NCHAR(13) + NCHAR(10) + N'       id' ELSE N'' END + 
                 CASE WHEN @subType = 3 THEN N',' + NCHAR(13) + NCHAR(10) + N'       [name]' ELSE N'' END + 
                 CASE WHEN @subType IN (3,2) THEN N',' + NCHAR(13) + NCHAR(10) + N'       [address]' ELSE N'' END, 1, 10,N'') + NCHAR(13) + NCHAR(10) +
           N'FROM YourTable;';

PRINT @SQL; --Your debugging best friend.
--EXEC sp_executesql @SQL; --Uncomment to run the statement

但是，如果查询来自表示层，那么实际上应该是在处理要显示的列，而不是SQL Server

But, if the query is coming from a presentation layer, then really that should be handling what columns are being displayed, not SQL Server

如果还要将参数传递给查询的WHERE，请确保参数化对sp_executesql的调用； 请勿将参数值插入动态语句中.

If you're passing parameters to the WHERE of your query as well, ensure that you parametrise the call to sp_executesql; do not inject the parameter values into the dynamic statement.

这篇关于选择不全为空的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！