选择不全为空的列 [英] 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屋!