从select语句中选择非零多列 [英] Select non zero multiple columns from select statement

问题描述

下面是Sql ResultSet表

Below is Sql ResultSet Table

Dates         Col1       Col2       Col3

17-2-2017      0          1          0  
18-2-2017      1          1          0
19-2-2017      1          1          0

我得到如下结果

I have get result like this below

Dates        col1     col2

17-2-2017     0         1
18-2-2017     1         1
19-2-2017     1         1

我的尝试：

What I have tried:

SELECT p.*
FROM Data p 
INNER JOIN (SELECT A.Date 
            FROM (SELECT p.Date,p.Col1,p.Col2 
 ) As A
            GROUP BY A.Date 
            HAVING COUNT(*) > 1

推荐答案

这不是一个完美的解决方案，但它有效



创建临时表并将实际数据复制到临时表，检查列是否全部为零，如果是，则从临时表中删除列

This is not a perfect solution, but it works

Create a temp table and copy the actual data to the temp table, check if the column has all zeros, if so then drop the column from temp table

if exists (     select  * from tempdb.dbo.sysobjects o    where o.xtype in ('U')    and o.id = object_id(N'tempdb..#tbltemp'))
DROP TABLE #tbltemp; 
CREATE TABLE #tbltemp( Dates varchar(9),col1 int, col2 int, col3 int)
insert   into #tbltemp (dates,col1,col2,col3     )   select  dates,col1,col2,col3 from Table1 ;
 
 
 if not exists ( select * from #tbltemp where col3 <> 0 )
 ALTER TABLE #tbltemp DROP COLUMN col3
 if not exists ( select * from #tbltemp where col2 <> 0 )
 ALTER TABLE #tbltemp DROP COLUMN col2
 if not exists ( select * from #tbltemp where col1 <> 0 )
 ALTER TABLE #tbltemp DROP COLUMN col1

 

  select * from #tbltemp

这篇关于从select语句中选择非零多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！