求一个高效简洁的sql语句
表如下：
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
[解决办法]
----------------------------
-- 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当前价格当前价格设置时间历史最高价格历史最高价格设置时间历史最低价格历史最高价格设置时间
10011002012-12-15 00:00:00.0001202012-11-13 00:00:00.000902012-12-01 00:00:00.000
*/
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
