如何在 SELECT 语句中忽略包含 NULL 值的列

Question

如何在 SELECT 语句中忽略包含 NULL 值的列

4

我有以下查询：

SELECT WC_WARD_CATEGORY,
   MAX(CASE WHEN months = 'JAN' THEN BOR END) JAN,
   MAX(CASE WHEN months = 'FEB' THEN BOR END) FEB,
   MAX(CASE WHEN months = 'MAR' THEN BOR END) MAR,
   MAX(CASE WHEN months = 'APR' THEN BOR END) APR,
   MAX(CASE WHEN months = 'MAY' THEN BOR END) MAY,
   MAX(CASE WHEN months = 'JUN' THEN BOR END) JUN,
   MAX(CASE WHEN months = 'JUL' THEN BOR END) JUL,
   MAX(CASE WHEN months = 'AUG' THEN BOR END) AUG,
   MAX(CASE WHEN months = 'SEP' THEN BOR END) SEP,
   MAX(CASE WHEN months = 'OCT' THEN BOR END) OCT,
   MAX(CASE WHEN months = 'NOV' THEN BOR END) NOV,
   MAX(CASE WHEN months = 'DEC' THEN BOR END) DEC
FROM bor2
GROUP BY WC_WARD_CATEGORY

它给我以下输出：

我希望的是NULL值消失，只显示具有正确数据的列。因此，我希望输出中包含OCT，NOV和DEC，但不包括其他列。使用SQL是否可能实现？

- Ashesh Das

MySQL还是SQL Server？ - Ullas

2

我不认为这是可能的，使用静态SQL。你必须在MySQL中使用带有预处理语句的动态SQL。 - Giorgos Betsos

1

在SQL Server中，您也应该使用动态SQL => 可能相关的问题SQL排除使用SELECT *除了columnA FROM tableA？ ;)。 - shA.t

1

在SQL中，要删除任何列之前，您必须拥有完整的查询结果，因此这种方法并不是非常有效。我更愿意在前端删除只包含空值的列。 - Shadow

你能同时添加所需的结果吗？（我无法理解你在请求什么...） - jarlh

显示剩余3条评论

3个回答

3

还可以使用动态SQL，但是要使用数据透视表：

declare @pc varchar(max)

select @pc = isnull(@pc + ',', '') + mnths
  from (
select distinct '[' + months + ']' mnths, convert(date, '1 ' + months + ' 1') ord
  from (select months
          from bor2
         group by months, WC_WARD_CATEGORY 
        having max(bor) is not null) as a ) as b order by ord

declare @sql varchar(max)
select @sql = '
select *
 from 
(
select WC_WARD_CATEGORY, months, bor
  from bor2
) as SourceTable 
PIVOT
(
max(bor) 
for months in (' + @pc + ')
) as PivotTable;'

execute(@sql)

- Sean

0

请检查一下这是否解决了您的问题。

SELECT * FROM WC_WARD_CATEGORY FOR XML PATH('NotNull')

这将为您提供一个XML文件，其中每行仅包含非空列，然后根据您的需要，您可以应用XSLT或脚本任务从此输入中获取结果作为表格格式。

- New

“WC_WARD_CATEGORY” 不是我的表名。此外，我不太确定如何使用将要创建的文件来生成JSON。 - Ashesh Das

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ullas · Accepted Answer

如果是sql-server，则只需将结果集复制到临时表中，然后尝试以下查询。

使用动态SQL。比较总行数与具有空值的总行数。如果两个计数相同，则排除该特定列，否则包含该列。

查询：

SELECT WC_WARD_CATEGORY,
   MAX(CASE WHEN months = 'JAN' THEN BOR END) JAN,
   MAX(CASE WHEN months = 'FEB' THEN BOR END) FEB,
   MAX(CASE WHEN months = 'MAR' THEN BOR END) MAR,
   MAX(CASE WHEN months = 'APR' THEN BOR END) APR,
   MAX(CASE WHEN months = 'MAY' THEN BOR END) MAY,
   MAX(CASE WHEN months = 'JUN' THEN BOR END) JUN,
   MAX(CASE WHEN months = 'JUL' THEN BOR END) JUL,
   MAX(CASE WHEN months = 'AUG' THEN BOR END) AUG,
   MAX(CASE WHEN months = 'SEP' THEN BOR END) SEP,
   MAX(CASE WHEN months = 'OCT' THEN BOR END) OCT,
   MAX(CASE WHEN months = 'NOV' THEN BOR END) NOV,
   MAX(CASE WHEN months = 'DEC' THEN BOR END) DEC 
INTO #temp
FROM bor2 
GROUP BY   WC_WARD_CATEGORY;

那么，

declare @strsql varchar(max)
  set @strsql = 'select '
  set @strsql += 
  (select case when  (select COUNT(*) from #temp where JAN is null ) 
  <> (select count(*) from #temp ) then 'JAN, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where FEB is null) 
  <> (select count(*) from #temp ) then 'FEB, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where MAR is null) 
  <> (select count(*) from #temp ) then 'MAR, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where APR is null) 
  <> (select count(*) from #temp ) then 'APR, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where MAY is null) 
  <> (select count(*) from #temp ) then 'MAY, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where JUN is null) 
  <> (select count(*) from #temp ) then 'JUN, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where JUL is null) 
  <> (select count(*) from #temp ) then 'JUL, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where AUG is null) 
  <> (select count(*) from #temp ) then 'AUG, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where SEP is null) 
  <> (select count(*) from #temp ) then 'SEP, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where OCT is null) 
  <> (select count(*) from #temp ) then 'OCT, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where NOV is null) 
  <> (select count(*) from #temp ) then 'NOV, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where DEC is null) 
  <> (select count(*) from #temp ) then 'DEC, ' else '' end)
  set @strsql = LEFT(@strsql,len(@strsql) -1)
  set @strsql += ' from #temp'

  exec (@strsql)