求一个高效简洁的sql语句

Question

表如下：
GUID 商品ID 价格 设置时间
XXX   1001  100  2012-12-05
XXX   1001  90   2012-12-01
XXX   1001  95   2012-11-25
XXX   1001  120  2012-11-13
……
……
其中GUID是唯一的主键uniqueidentifier。
要求查出如下的结果
商品ID 当前价格 当前价格设置时间 历史最高价格，最高价格设置时间，历史最低价格，最低价格设置时间 
1001   100    2012-12-05     120        201-11-13       90        2012-12-01

Answer 1

----------------------------------------------------------------
-- Author  :TravyLee(物是人非事事休，欲语泪先流！)
-- Date    :2012-12-15 15:31:35
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
go 
create table [test]
(
[GUID] varchar(3),
[商品ID] int,
[价格] int,
[设置时间] datetime
)
insert [test]
select 'XXX',1001,100,'2012-12-15' union all
select 'XXX',1001,90,'2012-12-01' union all
select 'XXX',1001,95,'2012-11-25' union all
select 'XXX',1001,120,'2012-11-13'
go
with t
as(
select
px1=ROW_NUMBER()over(partition by [商品ID] order by [设置时间] desc),
px2=ROW_NUMBER()over(partition by [商品ID] order by [价格] desc),
px3=ROW_NUMBER()over(partition by [商品ID] order by [价格] asc),
[商品ID],
[价格],
[设置时间]
from
test
)
select
a.商品ID,
a.价格 as 当前价格,
a.设置时间 as 当前价格设置时间,
b.价格 as 历史最高价格,
b.设置时间 as 历史最高价格设置时间,
c.价格 as 历史最低价格,
c.设置时间 as 历史最高价格设置时间
from
t a
inner join
t b
on a.px1=b.px2 and a.商品ID=b.商品ID
inner join
t c
on 
a.px1=c.px3 and a.商品ID=c.商品ID
where 
a.px1=1/*
商品ID 当前价格 当前价格设置时间 历史最高价格 历史最高价格设置时间 历史最低价格 历史最高价格设置时间
1001 100 2012-12-15 00:00:00.000 120 2012-11-13 00:00:00.000 90 2012-12-01 00:00:00.000
*/

Answer 2

with tb(GUID,商品ID,价格,设置时间)
as(
select 'XXX',1001,100,'2012-12-15' union all
select 'XXX',1001,90,'2012-12-01' union all
select 'XXX',1001,95,'2012-11-25' union all
select 'XXX',1001,120,'2012-11-13'
)
select 商品ID,(select top 1 价格 from tb tb2 where tb2.商品ID=tb1.商品ID order by tb2.设置时间 desc) 当前价格,
(select max(设置时间) from tb tb2 where tb2.商品ID=tb1.商品ID) 当前价格设置时间,
(select max(价格) from tb tb2 where tb2.商品ID=tb1.商品ID) 历史最高价格,
(select top 1 设置时间 from tb tb2 where tb2.商品ID=tb1.商品ID and tb2.价格=(select max(价格) from tb tb3 where tb3.商品ID=tb1.商品ID)) 最高价格设置时间,
(select min(价格) from tb tb2 where tb2.商品ID=tb1.商品ID) 历史最低价格,
(select top 1 设置时间 from tb tb2 where tb2.商品ID=tb1.商品ID and tb2.价格=(select min(价格) from tb tb3 where tb3.商品ID=tb1.商品ID)) 最低价格设置时间 
from tb tb1 group by tb1.商品ID

Answer 3

总感觉sql语句还是有缺陷的。像这么一个简单的要求，却要一大堆语句才能实现。我想过在插数据的时候用触发器，得到最大和最小值直接写到某个表或者本表里。但是随着数据的增加，性能明显下降。哎……