比如有一张表（table）有如下几个字段：
columnA columnB columnC columnD columnE
数据如下：
testA 200 2009-07-21 12:34:56 500 2009-07-31 12:34:56
testA 5000 2009-07-11 12:34:56 220 2009-07-11 12:34:56
testB 2000 2009-07-21 12:34:56 330 2009-08-01 12:34:56
testB 1000 2009-07-21 12:34:56 120 2009-07-21 12:34:56
testC 8000 2009-08-01 12:34:56 110 2009-08-01 12:34:56
testD 6000 2009-09-01 12:34:56 20 2009-09-01 12:34:56
查询结果要求：
统计09年7月份的数据，显示为：
columnA columnB columnD
testA 5200 720
testB 3000 120
统计09年8月份的数据，显示为：
columnA columnB columnD
testB 0 330
testC 8000 110
统计09年9月份的数据，显示为：
testD 6000 20
请问sql语句怎么写？
------解决方案--------------------------------------------------------
- SQL code
select isnull(a.groupMonth,b.groupMonth) as groupMonth,isnull(a.columnA,b.columnA),isnull(a.columnB,0),isnull(b.columnD,0) from (select year(columnC)*16+month(columnC) as groupMonth,columnA,sum(columnB) as columnB from test group by year(columnC)*16+MONTH(columnC),columnA) a full join (select year(columnE)*16+month(columnE) as groupMonth,columnA,sum(columnD) as columnD from test group by year(columnE)*16+MONTH(columnE),columnA) b on a.groupMonth=b.groupMonth and a.columnA=b.columnA order by groupMonth
------解决方案--------------------------------------------------------
如果是informix， 我会用
select columnA, sum（case when columnC bewteen start_date and end_date then columnB else 0 end), sum(columnD) from table where columnE between start_date and end_date
group by 1.
------解决方案--------------------------------------------------------
来个ORACLE的写法:
select decode(a.columna, null, b.columna,a.columna,b.columna, null, a.columna,b.columna) c1,
decode(a.columnb, null, null, a.columnb) c2,
decode(b.columnd, null, null,b.columnd) c3
from (select columna, sum(columnb) columnb
from t_table
where to_char(columnc, 'MM') = '07'
group by columna
order by 1) a
full join (select columna, sum(columnd) columnd
from t_table
where to_char(columne, 'MM') = '07'
group by columna
order by 1) b
on a.columna = b.columna