日期:2014-05-18 浏览次数:20597 次
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
------解决方案--------------------
分组查询,聚合函数。楼主查一下资料
------解决方案--------------------
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
------解决方案--------------------
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
------解决方案--------------------
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