SqlServer2005连接之前的行
时间：2010-09-12 来源：PetterLiu
Common Table Expressions (CTEs)和Row_number()函数提供了我们一种优雅的解决方案．下面我们来假设一个常见的场景．零售商一张产品(Items)表，还有一张历史价格(PriceHistory)表．数据如下：
产品表
CREATE TABLE [dbo].[Items](
[ItemId] [int] NOT NULL,
[Item] [varchar](100) NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[ItemId] ASC
))
GO
CREATE TABLE [dbo].[PriceHistory](
[ItemId] [int] NOT NULL,
[PriceStartDate] [datetime] NOT NULL,
[Price] [decimal](10, 2) NOT NULL,
CONSTRAINT [PK_PriceHistory] PRIMARY KEY CLUSTERED
(
[ItemId] ASC,
[PriceStartDate] ASC
))
GO
ALTER TABLE [dbo].[PriceHistory] WITH CHECK ADD CONSTRAINT [FK_PriceHistory_Items] FOREIGN KEY([ItemId])
REFERENCES [dbo].[Items] ([ItemId])
测试数据:
GO
INSERT INTO Items VALUES (1, 'vacuum cleaner')
INSERT INTO Items VALUES (2, 'washing machine')
INSERT INTO Items VALUES (3, 'toothbrush')
INSERT INTO PriceHistory VALUES (1,'2004-03-01',250)
INSERT INTO PriceHistory VALUES (1,'2005-06-15',219.99)
INSERT INTO PriceHistory VALUES (1,'2007-01-03',189.99)
INSERT INTO PriceHistory VALUES (1,'2007-02-03',200.00)
INSERT INTO PriceHistory VALUES (2,'2006-07-12',650.00)
INSERT INTO PriceHistory VALUES (2,'2007-01-03',550.00)
INSERT INTO PriceHistory VALUES (3,'2005-01-01',1.99)
INSERT INTO PriceHistory VALUES (3,'2006-01-01',1.79)
INSERT INTO PriceHistory VALUES (3,'2007-01-01',1.59)
INSERT INTO PriceHistory VALUES (3,'2008-01-01',1.49)
ItemId PriceStartDate Price
1 2004-03-01 00:00:00.000 250.00
1 2005-06-15 00:00:00.000 219.99
1 2007-01-03 00:00:00.000 189.99
1 2007-02-03 00:00:00.000 200.00
2 2006-07-12 00:00:00.000 650.00
2 2007-01-03 00:00:00.000 550.00
3 2005-01-01 00:00:00.000 1.99
3 2006-01-01 00:00:00.000 1.79
3 2007-01-01 00:00:00.000 1.59
3 2008-01-01 00:00:00.000 1.49
现在用户希望出一张这样的报表：
| Item | Old Price | Range Price | Start Date | End Date |
|---|---|---|---|---|
| vacuum cleaner | 250.00 | 2004-03-01 | 2005-06-15 | |
| vacuum cleaner | 250.00 | 219.99 | 2005-06-15 | 2007-01-03 |
| vacuum cleaner | 219.99 | 189.99 | 2007-01-03 | 2007-02-03 |
| vacuum cleaner | 189.99 | 200.00 | 2007-02-03 | |
| washing machine | 650.00 | 2006-07-12 | 2007-01-03 | |
| washing machine | 650.00 | 550.00 | 2007-01-03 | |
| toothbrush | 1.99 | 2005-01-01 | 2006-01-01 | |
| toothbrush | 1.99 | 1.79 | 2006-01-01 | 2007-01-01 |
| toothbrush | 1.79 | 1.59 | 2007-01-01 | 2008-01-01 |
| toothbrush | 1.59 | 1.49 | 2008-01-01 |
第一步，我们首先用CTE来实现，按时间排名产品价格视图：
;WITH PriceCompare AS (
SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM Items i INNER JOIN PriceHistory ph
ON i.ItemId = ph.ItemId)
SELECT * FROM PriceCompare结果是：
| Item | ItemId | PriceStartDate | Price | rownum |
|---|---|---|---|---|
| vacuum cleaner | 1 | a2004-03-01 | 250.00 | 1 |
| vacuum cleaner | 1 | 2005-06-15 | 219.99 | 2 |
| vacuum cleaner | 1 | 2007-01-03 | 189.99 | 3 |
| vacuum cleaner | 1 | 2007-02-03 | 200.00 | 4 |
| washing machine | 2 | 2006-07-12 | 650.00 | 1 |
| washing machine | 2 | 2007-01-03 | 550.00 | 2 |
| toothbrush | 3 | 2005-01-01 | 1.99 | 1 |
| toothbrush | 3 | 2006-01-01 | 1.79 | 2 |
| toothbrush | 3 | 2007-01-01 | 1.59 | 3 |
| toothbrush | 3 | 2008-01-01 | 1.49 | 4 |
然后增加Select主句：
SELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate
FROM PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1
AND currow.ItemId = nextrow.ItemId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1
AND currow.ItemId = prevrow.ItemId
请注意，我使用的第一行Left Join 为一个项有没有以前的行，就像最后一行没有下一行。
最后我们把写成一个完整的视图吧：
CREATE VIEW [dbo].[PriceCompare] AS
WITH PriceCompare AS
(
SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM
Items i
INNER JOIN
PriceHistory ph
ON i.ItemId = ph.ItemId
)
SELECT
currow.Item,
prevrow.Price AS OldPrice,
currow.Price AS RangePrice,
currow.PriceStartDate AS StartDate,
nextrow.PriceStartDate AS EndDate
FROM
PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId
实现CTE后让T-SQL如此简洁．这项的场景很多的，例如我们需要存储某一个值历史记录，也可以用这种DB设计方式来实现．
相比传统建立历史表还需要一个结束时间，要少至少一个字段．
希望对您的开发有帮助！
作者：Petter Liu
出处：http://www.cnblogs.com/wintersun/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
该文章也同时发布在我的独立博客中-Petter Liu Blog。
相关阅读 更多 +
