一条未解决的SQL,请帮帮忙
tbale:a
(id,name)
ID NAME
1 AA
tbale:b(
id,
a_id,//表a的ID
a_date,//日期
times,//次数
type)//类型（有1和2）
ID A_ID A_DATE TIMES TYPE
1 1 2007-1-31 33 1
2 1 2007-1-31 23 2
3 1 2007-1-30 65 1
4 1 2007-1-30 34 2
5 1 2007-1-29 32 1
6 1 2007-1-29 88 2
根据A表的ID查询如下结果
ID NAME TYPE1_TODAY TYPE1_YESTERDAY TYPE2_TODAY TYPE2_YESERDAY TYPE1_SUM TYPE2_SUM
1 AA 33 65 23 34 130 145
[解决办法]
select a.* , sum(case when a.type=1 and a.date=to_char(sysdate, 'yyyy-mm-dd ') then b.times end) TYPE1_TODAY ,sum(case when a.type=1 and a.date=to_char(sysdate-1, 'yyyy-mm-dd ') then b.times end) TYPE1_YESTERDAY ,sum(case when a.type=2 and a.date=to_char(sysdate, 'yyyy-mm-dd ') then b.times end) TYPE2_TODAY ,sum(case when a.type=2 and a.date=to_char(sysdate-1, 'yyyy-mm-dd ') then b.times end) TYPE2_YESTERDAY ,sum(case when a.type=1 then b.times end) TYPE1_SUM ,sum(case when a.type=2 then b.times end) TYPE2_SUM from a , b where a.id=b.aid group by a.id,a.name
