日期:2014-05-18 浏览次数:20434 次
USE TEMPDB GO IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB( COL1 INT ,COL2 FLOAT ,COL3 VARCHAR(10) ) INSERT INTO TB SELECT 1,2.2,'A' UNION ALL SELECT 2,3.3,'B' GO DECLARE @SQL VARCHAR(MAX) SELECT @SQL=ISNULL(@SQL+' UNION ALL ','')+'SELECT '''+COL.NAME+''' AS [列名],SUM('+COL.NAME+') AS [总和],AVG('+COL.NAME+') AS [平均数] FROM TB ' FROM SYS.COLUMNS COL INNER JOIN SYS.TYPES TYPES ON COL.SYSTEM_TYPE_ID=TYPES.SYSTEM_TYPE_ID WHERE OBJECT_NAME(OBJECT_ID)='TB' AND TYPES.NAME IN ('tinyint','SMALLINT','INT','BIGINT','REAL','MONEY','NUMERIC','FLOAT','DECIMAL') EXEC (@SQL) /* 列名 总和 平均数 COL1 3 1 COL2 5.5 2.75 */
------解决方案--------------------
create table chu(id char(3),sname char(5),sno int,sage int) insert into chu select '001','lx',5,17 union all select '002','su',6,18 union all select '003','wg',8,20 union all select '004','hj',9,21 select * from chu id sname sno sage ---- ----- ----------- ----------- 001 lx 5 17 002 su 6 18 003 wg 8 20 004 hj 9 21 declare @sql varchar(6000)='' select @sql=@sql +' union all select '''+b.name+''' ''列名'',sum('+b.name+') ''总和'',avg('+b.name+') ''平均数'' from chu' from sys.objects a inner join sys.columns b on a.object_id=b.object_id inner join sys.types c on b.system_type_id=c.system_type_id where a.[type]='U' and a.name='chu' and c.name in ('int','decimal') select @sql=stuff(@sql,1,11,'') exec(@sql) 列名 总和 平均数 ---- ----------- ----------- sno 28 7 sage 76 19 (2 row(s) affected)