如果去掉这个临时表中合计为0 的字段
我有一个临时表 ##temp,
字段
住院号,床位,应收金额,优惠金额1,优惠金额2,优惠金额3,优惠金额4.。。。。优惠金额N
我想把临时表中 优惠金额X 合计为0的字段去掉,如何去?
又或者,生成另一个没有 优惠金额X 合计为0字段的临时表。
谢谢!
[解决办法]
create table #temp(col1 int,col2 int,col3 int,col4 int,col5 int,col6 int)
insert into #temp values(1,2,0,0,8,0)
declare @s varchar(1000)
set @s=''
select top 1 @s=case when col1<>0 then ',col1' else '' end
+case when col2<>0 then ',col2' else '' end
+case when col3<>0 then ',col3' else '' end
+case when col4<>0 then ',col4' else '' end
+case when col5<>0 then ',col5' else '' end
+case when col6<>0 then ',col6' else '' end
from #temp
set @s=STUFF(@s,1,1,'')
select @s
exec ('select '+@s+ ' from #temp')
/*
col1col2col5
128
*/
--假設你有100個优惠金额,优惠金额1~优惠金额100
IF object_id('tempdb..#t1') is not null
DROP table #t1;
IF object_id('tempdb..##t2') is not null
DROP table ##t2;
CREATE TABLE #t1(zd VARCHAR(50))
DECLARE @i INT,@sql_1 VARCHAR(MAX),@sql_2 VARCHAR(MAX)
SELECT @i=1,@sql_1='select 住院号,床位,应收金额'
WHILE @i<=100
BEGIN
SET @sql_2='IF EXISTS(SELECT 1 FROM ##temp WHERE 优惠金额'+RTRIM(@i)+'>0) INSERT #t SELECT ''优惠金额'+RTRIM(@i)+''''
EXEC(@sql_2)
END
SELECT @sql_1=@sql_1+','+RTRIM(zd) FROM #t1
SET @sql_1=@sql_1+' into ##t2 from ##temp'
EXEC(@sql_1)
SELECT * FROM ##t2--這就是你想要的結果
--drop table ##temp
create table ##temp
(
住院号 varchar(20),
床位 varchar(20),
应收金额 numeric(20,3),
优惠金额1 numeric(20,3),
优惠金额2 numeric(20,3),
优惠金额3 numeric(20,3),
优惠金额4 numeric(20,3)
)
insert into ##temp
select '00000','111',1000, 0,0,0,10 union all
select '00001','112',1000, 0 ,0,0,0 union all
select '00002','113',1000, 0,0,0,0 union all
select '00003','114',1000, 0 ,0,0,20 union all
select '00004','115',1000, 0,2,0,3 union all
select '00005','116',1000, 0,0,0,0 union all
select '00006','117',1000, 0,0,0,0
go
declare @sql nvarchar(max);
declare @sql_delete_column nvarchar(max);
declare @tb table(column_name nvarchar(100),rownum int)
declare @count int;
declare @i int;
declare @return int;
declare @temp_name nvarchar(100);
declare @del_column nvarchar(100);
set @sql = '';
set @sql_delete_column = '';
--临时表名
set @temp_name = '##temp'
--需要删除的列名
set @del_column = '%优惠金额%';
insert into @tb
select --t.name,
c.name as column_name,
row_number() over(order by @@servername) as rownum
--c.column_id
from tempdb.sys.tables t
inner join tempdb.sys.columns c
on t.object_id = c.object_id
where t.name = @temp_name
and c.name like @del_column;
set @count = (select count(*) from @tb);
set @i = 1;
while @i <= @count
begin
set @sql = 'select @return=sum('+
(select column_name from @tb where rownum = @i) +
') from ' + @temp_name;
exec sp_executesql @sql,N'@return int output',@return output;
select @sql_delete_column =
@sql_delete_column +
case when @return <> 0 then ' '
else 'alter table '+@temp_name +
' drop column '+
(select column_name from @tb where rownum = @i) +
';'
end
set @i = @i +1
end
--动态生成的删除列语句
select @sql_delete_column
/*
(无列名)
alter table ##temp drop column 优惠金额1;
alter table ##temp drop column 优惠金额3;
*/
--删除列
exec(@sql_delete_column)
--查询数据
select * from ##temp;
/*
住院号床位应收金额优惠金额2优惠金额4
000001111000.0000.00010.000
000011121000.0000.0000.000
000021131000.0000.0000.000
000031141000.0000.00020.000
000041151000.0002.0003.000
000051161000.0000.0000.000
000061171000.0000.0000.000
*/