--建立测试环境
Create Table T
(TID Int Identity(1,1),
item1 Int,
item2 Int,
item3 Int,
item4 Int)Create Table Q
(item Varchar(10),
count Int)
--插入数据
Insert T Values(1, 0, 0, 1)
Insert T Values(0, 1, 1, 0)
Insert T Values(0, 0, 0, 1)
--测试
Insert Q
Select 'item1',SUM(item1) from T
Union All
Select 'item2',SUM(item2) from T
Union All
Select 'item3',SUM(item3) from T
Union All
Select 'item4',SUM(item4) from TSelect * from Q
--删除测试环境
Drop Table T,Q
--结果
/*
item count
item1 1
item2 1
item3 1
item4 2
*/
Create Table T
(TID Int Identity(1,1),
item1 Int,
item2 Int,
item3 Int,
item4 Int)Create Table Q
(item Varchar(10),
count Int)
--插入数据
Insert T Values(1, 0, 0, 1)
Insert T Values(0, 1, 1, 0)
Insert T Values(0, 0, 0, 1)
--测试
Insert Q
Select 'item1',SUM(item1) from T
Union All
Select 'item2',SUM(item2) from T
Union All
Select 'item3',SUM(item3) from T
Union All
Select 'item4',SUM(item4) from TSelect * from Q
--删除测试环境
Drop Table T,Q
--结果
/*
item count
item1 1
item2 1
item3 1
item4 2
*/
if object_id('t') is not null drop table t
go
Create Table T
(TID Int Identity(1,1),
item1 Int,
item2 Int,
item3 Int,
item4 Int)
Insert T Values(1, 0, 0, 1)
Insert T Values(0, 1, 1, 0)
Insert T Values(0, 0, 0, 1)if object_id('q') is not null drop table q
go
create table q(item varchar(10), counts int)
select* from t
--定义一个游标进行逐列处理, 具体使用时,将t, q改为实际的表名就可以了
declare @colname varchar(10)
declare @str varchar(100)
declare cur cursor
for select name from syscolumns
where id=object_id('t') and colid>1
set @str='insert q select '+char(0x27)+@colname+char(0x27)+
',sum('+@colname+') from t '
open cur
fetch cur into @colname
while @@fetch_status=0
begin
set @str='insert q select '+char(0x27)+@colname+char(0x27)+
',sum('+@colname+') from t '
-- print @str
exec(@str)
fetch cur into @colname
end
close cur
deallocate curselect* from q
是不是说,对于sum值小于2的列,不插入到Q表中?
Create Table T
(TID Int Identity(1,1),
item1 Int,
item2 Int,
item3 Int,
item4 Int)Create Table Q
(item Varchar(10),
count Int)
--插入数据
Insert T Values(1, 0, 0, 1)
Insert T Values(0, 1, 1, 0)
Insert T Values(0, 0, 0, 1)
--测试
Declare @S Varchar(1000)
Set @S='Insert Q Select * from ('
Select @S=@S+'Select '''+Name+''' As Item,SUM('+Name+') As Count from T Union All '
from SysColumns Where ID=OBJECT_ID('T') And ColID>1
Select @S=Left(@S,Len(@S)-10)+' ) A'
--Select @S=Left(@S,Len(@S)-10)+' ) A Where Count>=2' --如果要加上条件,就在这里加
EXEC(@S)Select * from Q
--删除测试环境
Drop Table T,Q
--结果
/*
item count
item1 1
item2 1
item3 1
item4 2
*/
to filebat(Mark) :谢谢你的回复,可是我对游标很不熟悉,说实话你的回复我好多都看不懂,所以对我不太实用.不过非常感谢你给我回复
to 杰娜:看看这个可能对你有帮助
select sum(item1)
from t
having sum(item1)>1