请问一个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_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

------解决方案--------------------
分组查询，聚合函数。楼主查一下资料

------解决方案--------------------

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_id
and
 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_out
from b where b.amount_time between '2011-08-01 00:00:01' and '2011-08-31 23:59:59'
 group by user_id) t
where a.user_id=t.user_id

------解决方案--------------------

SQL code


create table a([user_id] int,[user_name] varchar(10),card_no varchar(10))
insert into a
select '001' ,'张三' ,'K10001' union all
select '002' ,'李四' ,'K10002'
go
create table b([user_id] int,amount_type int,amount_time datetime,amount int)
insert into b
select '001', 1 ,'2011-08-01 07:30:30', 1000 union all
select '001', 2 ,'2011-08-01 08:08:08', 2000 union all
select '001', 2 ,'2011-08-01 09:09:08', 3000 union all
select '001', 3 ,'2011-08-01 11:08:08', 1500 union all
select '001', 3 ,'2011-08-01 12:08:08', 1200 union all
select '001', 3 ,'2011-08-01 13:18:08', 1700 union all
select '002', 1 ,'2011-08-03 11:18:08', 5000 union all
select '002', 3 ,'2011-08-03 12:18:08', 4000
go

select 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        4400
K10002     2           李四         5000        4000

(2 行受影响)

相关推荐