[Id] [int] IDENTITY(1,1) NOT NULL,

[Value] [int] NULL,

[StandardDeviation] [int] NULL,

[小於標準差] [int] NULL,

[連續8次] [nvarchar](10) NULL,

[連續2次] [nvarchar](10) NULL,

[五筆內有四筆以上] [nvarchar](10) NULL

) ON [PRIMARY]

;

SET IDENTITY_INSERT [dbo].[Test] ON

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (1, 5, 10, 1, N'NO', N'NO', N'NO')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (2, 5, 10, 1, N'NO', N'YES', N'NO')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (3, 5, 10, 1, N'NO', N'YES', N'NO')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (4, 5, 0, 0, N'NO', N'NO', N'NO')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (5, 5, 10, 1, N'NO', N'NO', N'YES')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (6, 5, 10, 1, N'NO', N'YES', N'YES')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (7, 5, 10, 1, N'NO', N'YES', N'YES')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (8, 5, 10, 1, N'NO', N'YES', N'YES')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (9, 5, 10, 1, N'NO', N'YES', N'YES')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (10, 5, 10, 1, N'NO', N'YES', N'YES')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (11, 5, 10, 1, N'NO', N'YES', N'YES')

;

INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (12, 5, 10, 1, N'YES', N'YES', N'YES')

;

SUM(CASE WHEN [小於標準差] = 0 THEN 1 ELSE 0 END)

OVER (ORDER BY ID ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) ST_X_0,

CASE WHEN SUM(CASE WHEN [小於標準差] = 0 THEN 1 ELSE 0 END)

OVER (ORDER BY ID ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) >=8 THEN N'0連續8次'

ELSE N'0沒有8次' END ST_X_0_EX,

SUM(CASE WHEN [小於標準差] > 0 THEN 1 ELSE 0 END)

OVER (ORDER BY ID ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) ST_X_1,

CASE WHEN SUM(CASE WHEN [小於標準差] > 0 THEN 1 ELSE 0 END)

OVER (ORDER BY ID ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) >=8 THEN N'1連續8次'

ELSE N'1沒有8次' END ST_X_1_EX,

SUM(CASE WHEN [小於標準差] > 0 THEN 1 ELSE 0 END)

OVER (ORDER BY ID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ST_Y,

CASE WHEN SUM(CASE WHEN [小於標準差] > 0 THEN 1 ELSE 0 END)

OVER (ORDER BY ID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) >=2 THEN N'1連續2次'

ELSE N'1沒有2次' END ST_Y_EX,

SUM(CASE WHEN [小於標準差] > 0 THEN 1 ELSE 0 END)

OVER (ORDER BY ID ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) ST_Z,

CASE WHEN SUM(CASE WHEN [小於標準差] > 0 THEN 1 ELSE 0 END)

OVER (ORDER BY ID ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) >=4 THEN N'1四筆以上'

ELSE N'1沒有四筆' END ST_Z_EX

FROM TEST