iT邦幫忙

0

想詢問SQL查詢連續資料的問題

ms sql sql
CooperWu 2021-10-04 15:18:58 ‧ 1567 瀏覽

  • 分享至 

  • xImage

各位不好意思，打擾了
我想詢問一下查詢連續資料的問題
有一個欄位是判斷數值有沒有小於標準差，

接著我有三個條件想要查詢：

  • 連續小於八次
  • 連續小於兩次
  • 五筆內有四筆小於

再麻煩各位幫忙一下，底下是測試資料

GO
/****** Object:  Table [dbo].[Test]    Script Date: 2021/10/4 下午 03:08:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
	[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]
GO
SET IDENTITY_INSERT [dbo].[Test] ON 
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (1, 5, 10, 1, N'NO', N'NO', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (2, 5, 10, 1, N'NO', N'YES', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (3, 5, 10, 1, N'NO', N'YES', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (4, 5, 0, 0, N'NO', N'NO', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (5, 5, 10, 1, N'NO', N'NO', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (6, 5, 10, 1, N'NO', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (7, 5, 10, 1, N'NO', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (8, 5, 10, 1, N'NO', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (9, 5, 10, 1, N'NO', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (10, 5, 10, 1, N'NO', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (11, 5, 10, 1, N'NO', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (12, 5, 10, 1, N'YES', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (13, 5, 10, 1, N'YES', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (14, 5, 10, 1, N'YES', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (15, 5, 10, 1, N'YES', N'YES', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (16, 5, 0, 0, N'NO', N'NO', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (17, 5, 0, 0, N'NO', N'NO', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (18, 5, 0, 0, N'NO', N'NO', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (19, 5, 0, 0, N'NO', N'NO', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (20, 5, 10, 1, N'NO', N'NO', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (21, 5, 10, 1, N'NO', N'YES', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (22, 5, 10, 1, N'NO', N'YES', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (23, 5, 0, 0, N'NO', N'NO', N'NO')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (24, 5, 10, 1, N'NO', N'NO', N'YES')
GO
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (25, 5, 10, 1, N'NO', N'YES', N'YES')
GO
SET IDENTITY_INSERT [dbo].[Test] OFF
GO
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

0
純真的人
iT邦大師 1 級 ‧ 2021-10-04 16:25:46
最佳解答

大概是這樣吧~
前提號碼要先連續~才可以用子查詢~

select *
,(
	select Sum(case when StandardDeviation > 0 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 7 and a.Id
) Num1
,(
case when (
	select Sum(case when StandardDeviation > 0 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 7 and a.Id
) >= 8 
then '連續8次' 
else '沒有8次'
end
) Num1Ans
,(
	select Sum(case when StandardDeviation > 0 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 1 and a.Id
) Num2
,(
case when (
	select Sum(case when StandardDeviation > 0 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 1 and a.Id
) >= 2 
then '連續2次' 
else '沒有2次'
end
) Num2Ans
,(
	select Sum(case when StandardDeviation > 0 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 4 and a.Id
) Num3
,(
case when (
	select Sum(case when StandardDeviation > 0 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 4 and a.Id
) >= 4 
then '四筆以上' 
else '沒有四筆'
end
) Num3Ans
from Test a

https://ithelp.ithome.com.tw/upload/images/20211004/2006136942gg36Ik3a.png

看更多先前的回應...收起先前的回應...
CooperWu iT邦新手 3 級 ‧ 2021-10-04 16:41:15 檢舉

非常感謝您，號碼的部份我再用ROW Number去處理就可以了，感恩

CooperWu iT邦新手 3 級 ‧ 2021-10-04 16:59:20 檢舉

不好意思，我漏了一個地方，想請問您連續八次的部份，假如是0連續八次或1連續八次都要判斷的話，請問該怎麼處理比較好

純真的人 iT邦大師 1 級 ‧ 2021-10-04 17:07:15 檢舉

就改變加總對象就好~

,(
	select Sum(case when [小於標準差] = 0 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 7 and a.Id
) Num4
,(
case when (
	select Sum(case when [小於標準差] = 0 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 7 and a.Id
) >= 8 
then '連續8次' 
else '沒有8次'
end
) Num4Ans
,(
	select Sum(case when [小於標準差] = 1 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 7 and a.Id
) Num5
,(
case when (
	select Sum(case when [小於標準差] = 1 then 1 else 0 end)
	from Test b
	where b.Id between a.Id - 7 and a.Id
) >= 8 
then '連續8次' 
else '沒有8次'
end
) Num5Ans
CooperWu iT邦新手 3 級 ‧ 2021-10-05 16:19:47 檢舉

感謝你

登入發表回應
1
rogeryao
iT邦超人 8 級 ‧ 2021-10-05 08:52:38

玩一下

CREATE TABLE [dbo].[Test](
	[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')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (13, 5, 10, 1, N'YES', N'YES', N'YES')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (14, 5, 10, 1, N'YES', N'YES', N'YES')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (15, 5, 10, 1, N'YES', N'YES', N'YES')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (16, 5, 0, 0, N'NO', N'NO', N'YES')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (17, 5, 0, 0, N'NO', N'NO', N'NO')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (18, 5, 0, 0, N'NO', N'NO', N'NO')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (19, 5, 0, 0, N'NO', N'NO', N'NO')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (20, 5, 10, 1, N'NO', N'NO', N'NO')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (21, 5, 10, 1, N'NO', N'YES', N'NO')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (22, 5, 10, 1, N'NO', N'YES', N'NO')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (23, 5, 0, 0, N'NO', N'NO', N'NO')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (24, 5, 10, 1, N'NO', N'NO', N'YES')
;
INSERT [dbo].[Test] ([Id], [Value], [StandardDeviation], [小於標準差], [連續8次], [連續2次], [五筆內有四筆以上]) VALUES (25, 5, 10, 1, N'NO', N'YES', N'YES')
;
SET IDENTITY_INSERT [dbo].[Test] OFF
;

SELECT *,
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

Demo

CooperWu iT邦新手 3 級 ‧ 2021-10-05 16:20:45 檢舉

謝謝你

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙