欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

100分较为复杂的查询语句

更新时间: 2014-01-05 02:31:05 责任编辑: Author_N1

 

100分求一个较为复杂的查询语句
比如有一张表(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
上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:31:05
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/10822/
本文WWW.DOC100.NET DOC100.NET版权所有。