来个有点挑战性的题目

Question

昨天写程序的时候遇到个小问题，发出来与大家分享
我的程序中有一个表，是根据SQL动态生成的，SQL也是不固定的，有可能是
SELECT A,B,C FROM TB
也有可能是
SELECT D,E,F,G FROM TB2
而且列的类型和位置不一定，结果有可能是
A 1 B
C 2 D
也有可能是
1 A 2 B
3 C 4 D
现在能得到这个SQL语句，想用一个存储过程或者是别的什么对它进行处理，得到结果集中数字列的合计，字符串列设为空。
也就是说，对SELECT A,B,C FROM TB进行处理后得到
_ 3 _
对SELECT D,E,F,G FROM TB2进行处理后得到
4 _ 6 _
注意，这个SQL是很复杂的，SELECT出来的列都是多表计算出来的结果。问题大概就是这样了，虽然已经用别的方法实现了，但我还是在想如何用SQL实现。高手们能帮我想想如何实现吗？
欢迎讨论，祝大家周末愉快~~

Answer 1

既然这样，我就先来献个丑吧
--->测试数据
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(1),[col2] int,[col3] varchar(1),[col4] numeric(2,1))
insert [tb]
select 'a',1,'b',3 union all
select 'c',5,'d',3.5
 
--->查询
declare @sql varchar(8000)select 
  @sql=isnull(@sql+',','')+'sum(['+a.name+']) as ['+a.name+']' 
from 
  syscolumns a,systypes b 
where 
  a.xtype=b.xtype 
and 
  a.id=object_id('tb') 
and 
  b.name in('tinyint','smallint','decimal','int','real','money','float','bigint','numeric','smallmoney')
order by colidset @sql='select '+@sql+' from tb'exec(@sql)/**
col2        col4                                     
----------- ---------------------------------------- 
6           6.5
**/

Answer 2

字符行置为空 我实现不了
declare @str varchar(8000)
set @str = ''
select @str = @str+','+a.name+'=case when '''+c.name+''' = ''int'' then sum(isnull('+a.name+',0)) else ''''end'  from syscolumns a
inner join sysobjects  b on a.id = b.id
inner join systypes c on a.xusertype=c.xusertype
where b.name = 'tb' and c.name = 'int'
set @str = right(@str,len(@str)-1)
set @str = 'select '+@str+' from tb' 

print (@str)
exec (@str)
select * from tbID          Num         PID         
----------- ----------- ----------- 
45          4500        21ID          PID         Num         text       
----------- ----------- ----------- ---------- 
1           NULL        100         222
2           1           200         333
3           2           300         444
4           3           400         555
5           1           500         666
6           NULL        600         777
7           NULL        700         888
8           7           800         999
9           7           900         111

Answer 3

--数据表
create table tb(a int, b char(10), c decimal(18,3), d char(10))
insert tb
select 1, 'a', 2, 'b' union all
select 3, 'c', 4, 'd'
go--存储过程
create procedure usp1 @table varchar(10) as
declare @s varchar(8000)set @s = ''
select @s = @s + ',' + case when type in ('bigint', 'decimal', 'float', 'int', 'numeric', 'real', 'smallint', 'tinyint') then 'sum(' + name + ') as ' + name else 'null as ' + name end from 
(
  select top 100 percent a.name, b.name as type from syscolumns a join systypes b on a.xtype = b.xtype
    where a.id = object_id(@table) order by a.colid
) tset @s = 'select ' + substring(@s, 2, len(@s) - 1) + ' from ' + @table
exec(@s)
go--执行
exec usp1 'tb'drop table tb
drop procedure usp1
/*
a           b           c                                        d           
----------- ----------- ---------------------------------------- ----------- 
4           NULL        6.000                                    NULL
*/

Answer 4

树哥这个可以实现对单表的数字列合计查询，但是如果是这样的语句SELECT A.ID,SUM(B.PRICE+A.COL1) 'TOTAL'
FROM (SELECT ID,SUM(COL1) 'COL1' FROM TB GROUP BY ID) A
LEFT JOIN TB1 B ON A.ID=B.AID AND YEAR(B.DATE) LIKE 2009
GROUP BY A.ID呢？SYSCOLUMNS表里能取到这样的列吗？

Answer 5

谢谢大家的积极讨论！看来大家都想到了用SYSCOLUMNS来查询字段的类型，但是如同这样的查询SELECT A.ID,SUM(B.PRICE+A.COL1) 'TOTAL' 
FROM (SELECT ID,SUM(COL1) 'COL1' FROM TB GROUP BY ID) A 
LEFT JOIN TB1 B ON A.ID=B.AID AND YEAR(B.DATE) LIKE 2009 
GROUP BY A.ID 这只是我随便写的一个SQL，SELECT后面的是各种复杂计算的结果，FROM后面的也不是直接从数据库中读出的表，这样就不能从SYSCOLUMNS里找到对应列的值了我现在想到了用ISNUMERIC来判断SELECT后面的每一列然后用CASE WHEN来区分。

Answer 6

先插入一个中间表
declare @s varchar(8000), @tabe char(10)set @s = 'SELECT A.ID,SUM(B.PRICE+A.COL1) TOTAL into ' + @table +
  'FROM (SELECT ID,SUM(COL1) COL1 FROM TB GROUP BY ID) A ' +
  'LEFT JOIN TB1 B ON A.ID=B.AID AND YEAR(B.DATE) LIKE 2009 ' +
  'GROUP BY A.ID 'exec(@s)
exec usp1 @table

Answer 7

呵呵知道了，看来是我胡思乱想了。
当时的第一想法是用SELECT INTO生成#表，但#表的列类型不会获取，所以才有这个问题。