T-SQL查询学习笔记——分组因子的使用示例

IF OBJECT_ID('Stocks') IS NOT NULL
DROP TABLE Stocks;
GO

CREATE TABLE dbo.Stocks
(
dt DATETIME NOT NULL PRIMARY KEY,
price INT NOT NULL
);

INSERT INTO dbo.Stocks(dt, price) VALUES('20060801', 13);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060802', 14);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060803', 17);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060804', 40);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060805', 40);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060806', 52);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060807', 56);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060808', 60);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060809', 70);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060810', 30);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060811', 29);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060812', 29);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060813', 40);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060814', 45);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060815', 60);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060816', 60);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060817', 55);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060818', 60);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060819', 60);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060820', 15);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060821', 20);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060822', 30);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060823', 40);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060824', 20);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060825', 60);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060826', 60);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060827', 70);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060828', 70);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060829', 40);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060830', 30);
INSERT INTO dbo.Stocks(dt, price) VALUES('20060831', 10);

[color=blue]需求：找出股票价格大于或等于50的连续周期[/color]

解决方案：
[color=blue]方案一、子查询[/color]
SELECT MIN(dt) AS startrange, MAX(dt) AS endrange,
DATEDIFF(day, MIN(dt), MAX(dt)) + 1 AS numdays,
MAX(price) AS maxprice
FROM (SELECT dt, price,
(SELECT MIN(dt)
FROM dbo.Stocks AS S2
WHERE S2.dt > S1.dt
AND price < 50) AS grp
FROM dbo.Stocks AS S1
WHERE price >= 50) AS D
GROUP BY grp;

[color=blue]方案二、ROW_NUMBER函数：[/color]
SELECT MIN(dt) AS startrange, MAX(dt) AS endrange,
DATEDIFF(day, MIN(dt), MAX(dt)) + 1 AS numdays,
MAX(price) AS maxprice
FROM (SELECT dt, price,
dt - ROW_NUMBER() OVER(ORDER BY dt) AS grp
FROM dbo.Stocks AS S1
WHERE price >= 50) AS D
GROUP BY grp;
GO