有如此查询结果bcl bzl bqzs year
100 200 300 2009
查询语句如下:select avg(bcl_jqpj) as bcl1,avg(bzl_jqpj) as bzl,avg(bqzs_jqpj) as bqzs,2009 as year from GX_HB_NZWZYBHTJDCQKB_D where recordid in (
select recordid from GX_HB_NZWZYBHTJDCQKB where sxbm in ('452001','452002','452003')
) and dcrq >= timestamp '2009-11-01 00:00:00' and dcrq <= timestamp '2009-12-15 00:00:00'bcl bzl bqzs year
311 14 6 2008
查询语句如下:select avg(bcl_jqpj) as bcl2,avg(bzl_jqpj) as bzl,avg(bqzs_jqpj) as bqzs,2008 as year from GX_HB_NZWZYBHTJDCQKB_D where recordid in (
select recordid from GX_HB_NZWZYBHTJDCQKB where sxbm in ('452001','452002','452003')
) and dcrq >= timestamp '2008-11-01 00:00:00' and dcrq <= timestamp '2008-12-15 00:00:00'bcl bzl bqzs year
89 1098 45 2007
查询语句如下:select avg(bcl_jqpj) as bcl3,avg(bzl_jqpj) as bzl,avg(bqzs_jqpj) as bqzs,2007 as year from GX_HB_NZWZYBHTJDCQKB_D where recordid in (
select recordid from GX_HB_NZWZYBHTJDCQKB where sxbm in ('452001','452002','452003')
) and dcrq >= timestamp '2007-11-01 00:00:00' and dcrq <= timestamp '2007-12-15 00:00:00'现在我想从以上三个查询结果中得到这样的结果。bcl_max year bzl_max year bqzs_max year
311 2008 1098 2007 300 2009
就是说, 取每个字段最大值及所对应的年份。求sql
100 200 300 2009
查询语句如下:select avg(bcl_jqpj) as bcl1,avg(bzl_jqpj) as bzl,avg(bqzs_jqpj) as bqzs,2009 as year from GX_HB_NZWZYBHTJDCQKB_D where recordid in (
select recordid from GX_HB_NZWZYBHTJDCQKB where sxbm in ('452001','452002','452003')
) and dcrq >= timestamp '2009-11-01 00:00:00' and dcrq <= timestamp '2009-12-15 00:00:00'bcl bzl bqzs year
311 14 6 2008
查询语句如下:select avg(bcl_jqpj) as bcl2,avg(bzl_jqpj) as bzl,avg(bqzs_jqpj) as bqzs,2008 as year from GX_HB_NZWZYBHTJDCQKB_D where recordid in (
select recordid from GX_HB_NZWZYBHTJDCQKB where sxbm in ('452001','452002','452003')
) and dcrq >= timestamp '2008-11-01 00:00:00' and dcrq <= timestamp '2008-12-15 00:00:00'bcl bzl bqzs year
89 1098 45 2007
查询语句如下:select avg(bcl_jqpj) as bcl3,avg(bzl_jqpj) as bzl,avg(bqzs_jqpj) as bqzs,2007 as year from GX_HB_NZWZYBHTJDCQKB_D where recordid in (
select recordid from GX_HB_NZWZYBHTJDCQKB where sxbm in ('452001','452002','452003')
) and dcrq >= timestamp '2007-11-01 00:00:00' and dcrq <= timestamp '2007-12-15 00:00:00'现在我想从以上三个查询结果中得到这样的结果。bcl_max year bzl_max year bqzs_max year
311 2008 1098 2007 300 2009
就是说, 取每个字段最大值及所对应的年份。求sql
to_char(max(dcrq)keep(dense_rank last order by bcl),'yyyy')year1,
max(bzl)bzl_max,
to_char(max(dcrq)keep(dense_rank last order by bzl),'yyyy')year2,
max(bqzs)bqzs_max,
to_char(max(dcrq)keep(dense_rank last order by bqzs),'yyyy')year3
from GX_HB_NZWZYBHTJDCQKB where sxbm in ('452001','452002','452003')