求一个sql

[zhangfengsummer]

有如此查询结果

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

[bayougeng]

只能是先在一段SQL里把max找出来，然后再拿这个max去匹配原来的数据。

[hchjjun]

先找出来插入临时表，然后select出来

[hchjjun]

先找出来插入临时表，然后select出来

[zhangfengsummer]

临时表效率会低，想求有没有更好的解决办法

[叶涛网站推广优化]

zhangfengsummer 去SQL区问吧

[bayougeng]

那就用union，写三段sql，取三条数据，再union起来。

[justinavril]

[Quote=引用 6 楼 bayougeng 的回复:]
那就用union，写三段sql，取三条数据，再union起来。
[/Quote]
Union不行 因为Union的本质是增加rows的长度 而不是增加column的长度。

[justinavril]

--构造测试环境
USE master

IF EXISTS 
	(SELECT 1 
		FROM sys.databases
		WHERE name = 'Test')
	DROP DATABASE Test
GO

CREATE DATABASE Test
GO

use Test

create 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

[bayougeng]

[Quote=引用 7 楼 justinavril 的回复:]
引用 6 楼 bayougeng 的回复:
那就用union，写三段sql，取三条数据，再union起来。

Union不行 因为Union的本质是增加rows的长度 而不是增加column的长度。


[/Quote]
是的，我的本意，就是让楼主取得三条数据，而不是在一条数据里分成6列显示。

[jichunweistar]

个人认为可以做到，给你个思路吧，可以增加一个附加列，做为最后连接的条件！