现有一表如
id 姓名 考试1 考试2 考试3 。。。。。（此列名可动态添加）
1 张三 100 98 89 。。。。。
2 李四 79 77 85 。。。。。
3 王五 68 72 75 。。。。。
4 张三 97 95 98 。。。。。
5 王五 75 70 77 。。。。。
6 王五 80 76 73 。。。。。
请问如何能够计算每列的和，实现以下结果
id 姓名 考试1 考试2 考试3
1 张三 197 193 187 。。。。。
2 李四 79 77 85 。。。。。
3 王五 223 218 225 。。。。。
请问如何实现啊 主要是 列数和列名不定
只能先读取表中所有的列名 然后在计算列中数值的和
------解决方案--------------------
- SQL code
if object_id ('dbo.table5') is not null drop table dbo.table5gocreate table dbo.table5 ( id int not null, name varchar (32) null, k1 int null, k2 int null, k3 int null )goinsert into table5select '1','张三','100','98','89' union allselect '2','李四','79','77','85'union allselect '3','王五','68','72','75'union allselect '4','张三','97','95','98'union allselect '5','王五','75','70','77'union allselect '6','王五','80','76','73'declare @c varchar(500)=''declare @sql varchar(2000)=''select @c= isnull(',sum('+c.name+')','')[email protected] from sys.columns c join sys.objects oon c.object_id=o.object_idand o.name='table5' where c.name like 'k%'select @sql =stuff(@c,1,1,'') from t exec ('select name,[email protected]+' from table5 group by name')
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( Id INT, Name VARCHAR(100), col1 INT, col2 INT, col3 INT)GOINSERT INTO tbaSELECT 1,'张三',100,89,48 UNIONSELECT 2,'李四',89,88,48 UNIONSELECT 3,'王五',87,87,43 UNIONSELECT 4,'张三',79,86,47 UNIONSELECT 5,'李四',58,85,46 UNIONSELECT 6,'王五',89,84,55 GODECLARE @sql VARCHAR(1000) = ''DECLARE @Total VARCHAR(1000) = ''SET @sql = 'SELECT Name'SELECT @sql = @sql + ',' + 'SUM(' + name + ') AS ' + name,@Total = @Total + '+' + 'SUM(' + name + ')'FROM Sys.columnsWHERE object_name(object_id) = 'tba' and name NOT IN ('ID','Name')SET @sql = @sql + ',' + RIGHT(@Total,LEN(@Total) - 1) + ' AS Total' + ' FROM tba GROUP BY Name'EXEC (@Sql)Name col1 col2 col3 Total李四 147 173 94 414王五 176 171 98 445张三 179 175 95 449
------解决方案--------------------
- SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([id] int,[姓名] varchar(4),[考试1] int,[考试2] int,[考试3] int)goinsert [test]select 1,'张三',100,98,89 union allselect 2,'李四',79,77,85 union allselect 3,'王五',68,72,75 union allselect 4,'张三',97,95,98 union allselect 5,'王五',75,70,77 union allselect 6,'王五',80,76,73godeclare @str varchar(2000)set @str=''select @[email protected]+',sum('+name+') as '+namefrom syscolumns where id=object_id('test')and name not in('id','姓名')declare @col varchar(2000)set @col=''select @[email protected]+'+sum('+name+')'from syscolumns where id=object_id('test')and name not in('id','姓名')print @colset @str='select row_number()over(order by getdate()) as id,姓名'[email protected]+',(select '+RIGHT(@col,LEN(@col)-1)+' from test b where a.姓名=b.姓名)as 合计 from test a group by 姓名 'exec( @str)/*id 姓名 考试1 考试2 考试3 合计1 李四 79 77 85 2412 王五 223 218 225 6663 张三 197 193 187 577*/