有如此查询结果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
USE masterIF EXISTS
(SELECT 1
FROM sys.databases
WHERE name = 'Test')
DROP DATABASE Test
GOCREATE DATABASE Test
GOuse Testcreate table dbo.statistic (date char(4), bcl int, bzl int, bqzs int)insert into statistic (date, bcl, bzl, bqzs) values ('2000', 100, 200, 300)
insert into statistic (date, bcl, bzl, bqzs) values ('2001', 150, 250, 350)
insert into statistic (date, bcl, bzl, bqzs) values ('2002', 300, 270, 200)
insert into statistic (date, bcl, bzl, bqzs) values ('2003', 500, 600, 100)
insert into statistic (date, bcl, bzl, bqzs) values ('2004', 400, 900, 300)
select * from statistic
输出:
date bcl bzl bqzs
---- ----------- ----------- -----------
2000 100 200 300
2001 150 250 350
2002 300 270 200
2003 500 600 100
2004 400 900 300
SQL语句:
select * from
(select top 1 bcl, date from statistic order by bcl desc) a,
(select top 1 bzl, date from statistic order by bzl desc) b,
(select top 1 bqzs, date from statistic order by bqzs desc) c
结果:
bcl date bzl date bqzs date
----------- ---- ----------- ---- ----------- ----
500 2003 900 2004 350 2001