请问一个SQL

请教一个SQL
表a
user_id user_name card_no 
001 张三 K10001
002 李四 K10002
   
表b
user_id amount_type amount_time amount
001 1 2011-08-01 07:30:30 1000
001 2 2011-08-01 08:08:08 2000
001 2 2011-08-01 09:09:08 3000
001 3 2011-08-01 11:08:08 1500
001 3 2011-08-01 12:08:08 1200
001 3 2011-08-01 13:18:08 1700
002 1 2011-08-03 11:18:08 5000
002 3 2011-08-03 12:18:08 4000
其中amount_type的标记为1：初次充值，2：续存费用，3：消费金额
我想统计这样的结果
card_no user_id user_name amount_time total_in total_out
K10001 001 张三 2011-08-01 07:30:30 6000 4400
K10002 002 李四 011-08-03 11:18:08 5000 4000
其中amount_time为初次充值标记为1的时间，total_in为标记为1和2的金额总和，total_out为3的金额总和
我写的查询语句如下：
select a.card_no,a.user_id,a.user_name,b.amount_time,  
sum(case when b.amount_type in ('1','2') then amount else 0 end) as total_in,  
sum(case when b.amount_type in ('3') then amount else 0 end) as total_out  
from a inner join b on a.user_id= b.user_id
where b.amount_time between '2011-08-01 00:00:01' and '2011-08-31 23:59:59' 
group a.card_no,a.user_id,a.user_name,b.amount_time  
得出的结果是：
card_no user_id user_name amount_time total_in total_out
K10001 001 张三 2011-08-01 07:30:30 6000 4400
K10001 001 张三 2011-08-01 08:08:08 6000 4400
K10001 001 张三 2011-08-01 09:09:08 6000 4400
K10002 002 李四 011-08-03 11:18:08 5000 4000
变成有几次充值和续费记录，就有几条记录，请问该怎么修改？谢谢··

[解决办法]

SQL code

select a.card_no,a.user_id,a.user_name,min(b.amount_time),   sum(case when b.amount_type in ('1','2') then amount else 0 end) as total_in,   sum(case when b.amount_type in ('3') then amount else 0 end) as total_out   from a inner join b on a.user_id= b.user_idwhere b.amount_time between '2011-08-01 00:00:01' and '2011-08-31 23:59:59'  group a.card_no,a.user_id,a.user_name
[解决办法]
分组查询，聚合函数。楼主查一下资料
[解决办法]
SQL code
select a.card_no,a.user_id,a.user_name,min(b.amount_time),    sum(case when b.amount_type in ('1','2') then amount else 0 end) as total_in,    sum(case when b.amount_type in ('3') then amount else 0 end) as total_out   from a ,b where a.user_id= b.user_idand b.amount_time between '2011-08-01 00:00:01' and '2011-08-31 23:59:59'  group by a.card_no,a.user_id,a.user_name
[解决办法]
SQL code
select * from a inner join (select user_id,sum(case when b.amount_type in ('1','2') then amount else 0 end) as total_in,   sum(case when b.amount_type in ('3') then amount else 0 end) as total_outfrom b where b.amount_time between '2011-08-01 00:00:01' and '2011-08-31 23:59:59' group by user_id) twhere a.user_id=t.user_id
[解决办法]
SQL code
create table a([user_id] int,[user_name] varchar(10),card_no varchar(10))insert into aselect '001' ,'张三' ,'K10001' union allselect '002' ,'李四' ,'K10002'gocreate table b([user_id] int,amount_type int,amount_time datetime,amount int)insert into bselect '001', 1 ,'2011-08-01 07:30:30', 1000 union allselect '001', 2 ,'2011-08-01 08:08:08', 2000 union allselect '001', 2 ,'2011-08-01 09:09:08', 3000 union allselect '001', 3 ,'2011-08-01 11:08:08', 1500 union allselect '001', 3 ,'2011-08-01 12:08:08', 1200 union allselect '001', 3 ,'2011-08-01 13:18:08', 1700 union allselect '002', 1 ,'2011-08-03 11:18:08', 5000 union allselect '002', 3 ,'2011-08-03 12:18:08', 4000goselect a.card_no,a.[user_id],a.[user_name],   sum(case when b.amount_type in ('1','2') then b.amount else 0 end) as total_in,   sum(case when b.amount_type in ('3') then b.amount else 0 end) as total_out   from a inner join b on a.[user_id]= b.[user_id]where b.amount_time between '2011-08-01 00:00:01' and '2011-08-31 23:59:59'   group by a.card_no,a.[user_id],a.[user_name]drop table a,b/******************card_no    user_id     user_name  total_in    total_out---------- ----------- ---------- ----------- -----------K10001     1           张三         6000        4400K10002     2           李四         5000        4000(2 行受影响)