现有表TABLE1：
Id，Money，Type，User
------------------------------------
1 1.0 1 test
2 52.0 3 test
3 30.0 2 test
4 22.0 2 zhang
5 32.0 2 zhang
6 11.1 3 test
------------------------------------
我现在想查询的是：
根据User字段，查询出该用户在3种Type下Money的合计
该存储过程返回的表应该为：
User，Type1Money，Type2Money，Type3Money
------------------------------------------------------------------
test 1.0 30.0 63.1
zhang 0.0 54.0 0.0
-------------------------------------------------------------------
希望指导一下，看下怎么写这个存储过程，谢谢了
group by + 竖表变横表，你去搜吧
sqlserver的存储过程可以这样写：
CREATE PROCEDURE [dbo].[CalcMoney]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @user varchar(50);
declare dep_cursor cursor for
select user from table1 group by user;
open dep_cursor;
fetch next from dep_cursor into @user;
while(@@fetch_status = 0)
begin
select @user,(select sum(money) from table1 where user = @user and type = 1),
(select sum(money) from table1 where user = @user and type = 2),
(select sum(money) from table1 where user = @user and type = 3);
fetch next from dep_cursor into @user;
end;
close dep_cursor;
deallocate dep_cursor;
END
如果只有固定的3种类型的话，很简单的一句查询就出来了
select user,sum(case when type=1 then money else 0 end) type1money,sum(case when type=2 then money else 0 end) type2money,
sum(case when type=3 then money else 0 end) type3money from table group by user
select a.user_id,
sum((case when a.type = 1 then a.mon else 0 end)) type_1 ,
sum((case when a.type = 2 then a.mon else 0 end)) type_2 ,
sum((case when a.type = 3 then a.mon else 0 end)) type_3
from TABLE1 a group by a.user_id;