有如此查询结果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
解决方案 »
- 用microsoft的JDBC驱动访问SQL数据库,为什么数据集的一些功能用不了了?
- form中加enctype="multipart/form-data",出现乱码的问题
- 大家帮帮忙,,js代码调用JSP时,想使用STRUTS技术,这样的问题应该怎么解决,感谢
- 这是什么错误?
- 怎么样用java写程序将主从数据库(oracle)实现数据传输和下载?请各位大侠帮忙!!!
- 闪烁问题*高分请教,分不够可以加
- 怎样让Eclipse的Navigator框口的工程下面只显示.java文件,而不要显示其他的.class文件?
- socket编写ftp程序,代理服务器
- 初学者的问题
- 请问有没有人熟悉Java调用本地程序?
- 问个简单的问题
- 菜鸟咨询下 public 后面加了个泛型 是什么意思?
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