例如有表PD:
product co1 co2 co3 ...
rb1 12 15 16
db2 29 86 46以上的product是固定有的字段,但co1,co2,co3,可能有30个以下,是不固定的,此数据表是执行select * into pd 的一段存储过程生成的.
现要对co1,co2,co3...进行求和,也就是
select product,sum(co1),sum(co2),sum(co3),.... from pd group by product请问各位此语句如何写呢?
product co1 co2 co3 ...
rb1 12 15 16
db2 29 86 46以上的product是固定有的字段,但co1,co2,co3,可能有30个以下,是不固定的,此数据表是执行select * into pd 的一段存储过程生成的.
现要对co1,co2,co3...进行求和,也就是
select product,sum(co1),sum(co2),sum(co3),.... from pd group by product请问各位此语句如何写呢?
INSERT TB
SELECT 'rb1', 12, 15, 16 UNION ALL
SELECT 'db2', 29, 86, 46DECLARE @STR VARCHAR(8000)
SET @STR=''
SELECT @STR=@STR+','+'SUM('+NAME+') AS '+NAME
FROM syscolumns
WHERE ID=OBJECT_ID('TB') AND NAME<>'product'EXEC('SELECT product'+@STR+' FROM TB GROUP BY product')DROP TABLE TB
/*
product co1 co2 co3
------- ----------- ----------- -----------
db2 29 86 46
rb1 12 15 16*/
GO
CREATE TABLE PD(product VARCHAR(10), co1 INT, co2 INT, co3 INT )
INSERT PD SELECT 'A',10,11,2
INSERT PD SELECT 'B',1,2,3
DECLARE @S VARCHAR(1000)
SELECT @S=ISNULL(@S+',','')+'SUM('+NAME+')' FROM SYSCOLUMNS WHERE ID=OBJECT_ID('PD') AND NAME!='product'
SET @S='SELECT product,'+@S+'FROM PD GROUP BY product'
EXEC(@S)
/*product
---------- ----------- ----------- -----------
A 10 11 2
B 1 2 3
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([product] varchar(3),[co1] int,[co2] int,[co3] int)
insert [tb]
select 'rb1',12,15,16 union all
select 'db2',29,86,46
---查询---
declare @col varchar(50),@sql varchar(8000)select @sql=isnull(@sql+',','')+'sum(['+name+']) as '+name from syscolumns where id=object_id('tb') and colorder>1set @sql='select product,'+@sql+' from tb group by product'exec (@sql)
---结果---
product co1 co2 co3
------- ----------- ----------- -----------
db2 29 86 46
rb1 12 15 16
CREATE TABLE T(product VARCHAR(15),col1 INT,col2 INT,col3 INT)
INSERT INTO T
SELECT 'rb1',12,15,16 UNION ALL
SELECT 'db2',29,86,46DECLARE @s VARCHAR(8000)
SELECT @s=''
SELECT @s=@s+',SUM('+name+')as '''+name+''' ' FROM syscolumns WHERE id=object_id('t')
AND name NOT IN('Product') ORDER BY name
EXEC('SELECT product '+@s+'FROM t GROUP BY product')/*
product col1 col2 col3
--------------- ----------- ----------- -----------
db2 29 86 46
rb1 12 15 16
*/
还有一个延伸的问题,如果',SUM('+name+')as '''+name+''' ' 中有两个已求和的字段还要求百份比,
例如 co13=sum(co12)/sum(co11),得到一个小数.
又例如: select sum(生产总数),sum(良品数),sum(不良品数),sum(不良率) from pd ,此句子中的不良率不能用sum(不良率)求和,是吧?而是select sum(生产总数),sum(良品数),sum(不良品数),sum(不良品数)/sum(生产总数) as 不良率 from pd ?那么请问',SUM('+name+')as '''+name+''' ' 如何改呢??谢谢
CREATE TABLE T(product VARCHAR(15),col1 INT,col2 INT,col3 INT)
INSERT INTO T
SELECT 'rb1',12,15,16 UNION ALL
SELECT 'db2',29,86,46DECLARE @s VARCHAR(8000)
SELECT @s=''
SELECT @s=@s+',SUM('+name+')as '''+name+''' ' FROM syscolumns WHERE id=object_id('t')
AND name NOT IN('Product') ORDER BY name
EXEC('SELECT product '+@s+'FROM t GROUP BY product')/*product col1 col2 col3
--------------- ----------- ----------- -----------
db2 29 86 46
rb1 12 15 16*/
--以上为以前答案
--下面假设SUM(col1)/(SUM(col1)+SUM(col2)+SUM(col3))不良率的情况DECLARE @s VARCHAR(8000),@sum VARCHAR(8000)
SELECT @s='',@sum=''
SELECT @s=@s+',SUM('+name+')as '''+name+''' ' FROM syscolumns WHERE id=object_id('t')
AND name NOT IN('Product') ORDER BY name
SELECT @sum=@sum+'+SUM('+name+') ' FROM syscolumns WHERE id=object_id('t')
AND name NOT IN('Product') ORDER BY name
SET @sum=stuff(@sum,1,1,'');
EXEC('SELECT product '+@s+',Ltrim(CAST(SUM(col1)*1.0/('+@sum+') AS NUMERIC(6,2))*100)+''%'' as ''百分比'' FROM t GROUP BY product')--这里我显示的引用 sum(col1),这个你可以用print @s查看要取哪个字段做除数
*/
product col1 col2 col3 百分比
--------------- ----------- ----------- ----------- -----------------------------------------
db2 29 86 46 18.00%
rb1 12 15 16 28.00%
*/