比如数据表user里有二个字段
name count
1 1
1 2
2 3
3 4
3 5
我要得到的结果是
1 2 3
3 3 9
是把user数据表name 这一列的值,当作另一个表的字段,然后分别把user数据表的name =1里count累加起来,name =2里count累加起来,name =3里count累加起来,得到上面所说的结果,能不能实现,谢谢了
---------------------------------------------------------------
你是不是写错了?
应该是 "3 2 9"
试试这个语句:
select name,count(name),sum(count) from dbo.test1 group by name
---------------------------------------------------------------
如果NAME取值可知:
select sum(case when name='1' then [count] else 0 end) as '1',
sum(case when name='2' then [count] else 0 end) as '2',
sum(case when name='3' then [count] else 0 end) as '3'
from user
---------------------------------------------------------------
如果NAME只有几个固定的
SELECT
(SELECT SUM(COUNT) FROM TABLE1 WHERE NAME=1 GROUP BY NAME) AS '1',
(SELECT SUM(COUNT) FROM TABLE1 WHERE NAME=2 GROUP BY NAME) AS '2',
(SELECT SUM(COUNT) FROM TABLE1 WHERE NAME=3 GROUP BY NAME) AS '3'
如果不固定,则要用游标
declare @name int---假使name为整形
declare @sql varchar(8000)
declare cr CURSOR for select name from Table1 group by name order by name
set @sql='SELECT '
OPEN cr
FETCH NEXT FROM cr INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql=@sql+
'(SELECT SUM(COUNT) FROM TABLE1 WHERE NAME=@name GROUP BY NAME) AS '+@name+','
END
CLOSE cr
DEALLOCATE cr
EXEC(LEFT(@sql,len(@sql)-1))