http://www.sqlservercentral.com/articles/T-SQL/62159/
| 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 |
| vacuum cleaner | 219.99 | 189.99 | 2007-01-03 | 2007-02 |
| ItemId | PriceStartDate | Price |
|---|---|---|
| 1 | 2004-03-01 | 250.00 |
| 1 | 2005-06-15 | 219.99 |
| 1 | 2007-01-03 | 189.99 |
| 1 | 2007-02-03 | 200.00 |
| 2 | 2006-07-12 | 650.00 |
| 2 | 2007-01-03 | 550.00 |
| 3 | 2005-01-01 | 1.99 |
| 3 | 2006-01-01 | 1.79 |
| 3 | 2007-01-01 | 1.59 |
| 3 | 2008-01-01 | 1.49 |
| ;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 | 2004-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 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 |
| 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 |
'MSSQL > 성능' 카테고리의 다른 글
| 01. (성능)인덱스를 이용 못하는 경우(자바 유니코드 문제) (0) | 2016.01.18 |
|---|---|
| 같은 쿼리가 다른 머신에서 실행시간 차이가 있을경우 (0) | 2015.12.17 |
| MSSQL 튜닝 / DB단 (0) | 2015.12.17 |
| 이전 로우를 찾는 방법(MSSQL 2012 이후) (0) | 2015.12.17 |
| attach(연결)한 DB의 읽기전용 상태 해결 (0) | 2015.12.03 |