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

查看:109
本文介绍了选择不全为空的列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆