在实现这个,该怎么做(在线等待)

比如数据表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))

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus