表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
变成有几次充值和续费记录,就有几条记录,请问该怎么修改?谢谢··
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
变成有几次充值和续费记录,就有几条记录,请问该怎么修改?谢谢··
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
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
不行啊,结果变成
card_no user_id user_name amount_time total_in total_out
K10001 001 张三 2011-08-01 07:30:30 18000 13200
K10002 002 李四 011-08-03 11:18:08 5000 4000
变成把重复记录的总金额加起来了···求教啊··
一样不行啊
结果变成
card_no user_id user_name amount_time total_in total_out
K10001 001 张三 2011-08-01 07:30:30 18000 13200
K10002 002 李四 011-08-03 11:18:08 5000 4000
变成把重复记录的总金额加起来了···求教啊··
结果变成
card_no user_id user_name amount_time total_in total_out
K10001 001 张三 2011-08-01 07:30:30 18000 13200
K10002 002 李四 011-08-03 11:18:08 5000 4000
变成把重复记录的总金额加起来了···求教啊··
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 a.card_no,a.user_id,a.user_name
(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
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
goselect 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 行受影响)