SQL查詢問題，假如符合條件要往上取資料

sql mssql

CooperWu 2021-10-06 15:49:10 ‧ 1557 瀏覽

大家好，我又遇到難題了。
我想查詢的是，假如有符合條件的話，要往上取2筆資料。
另外一排也是做相同的事，但是是取1筆，如下圖

最後查詢出的結果就是有被選到的資料要顯示

麻煩大神抽空解答，感謝
不好意思，忘記放Table的指令碼了

GO
/****** Object:  Table [dbo].[Test2]    Script Date: 2021/10/6 下午 04:49:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test2](
	[Id] [int] NOT NULL,
	[Value] [int] NOT NULL,
	[是否符合1] [bit] NULL,
	[是否符合2] [bit] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (1, 5, 0, 0)
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (2, 5, 0, 0)
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (3, 5, 0, 1)
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (4, 5, 0, 0)
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (5, 5, 1, 1)
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (6, 5, 0, 0)
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (7, 5, 1, 0)
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (8, 5, 0, 1)
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (9, 5, 0, 0)
GO
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (10, 5, 0, 0)
GO

leonliu777 iT邦新手 5 級 ‧ 2021-10-06 16:09:12 檢舉

假定你Id是int
SELECT distinct B.Id
FROM (
SELECT* FROM [dbo].[table] where [是否符合1] = 1) A
LEFT JOIN [dbo].[table] B on A.Id - B.Id <= 2 and A.Id - B.Id >= 0

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

change

iT邦新手 4 級 ‧ 2021-10-06 17:21:17

最佳解答

select B.*
  from #Tmp A
  join #Tmp B on B.Id < A.Id and B.Id >= A.Id-2
 where A.[是否符合1] = 1
union
select B.*
  from #Tmp A
  join #Tmp B on B.Id = A.Id - 1
 where A.[是否符合2] = 1
union
select *
  from #Tmp
 where ([是否符合1] = 1 or [是否符合2] = 1)
order by Id

希望對你有幫助 ^^

回應 1
分享
檢舉

CooperWu iT邦新手 3 級 ‧ 2021-10-06 17:37:20 檢舉

感謝你的解答，很有幫助，但我希望可以多看看其他人的解答

登入發表回應

allenlwh

iT邦高手 1 級 ‧ 2021-10-06 16:27:12

因為我不確定id是否會跳號，所以我的做法就是先找到符合的id，
再以id做降冪，取前二筆
如果符合的id=5，條件就是小於5，取回降冪後的前二筆，那就是4,3
如果符合的id=7，條件就是小於7，取回降冪後的前二筆，那就是6,5
...餘類推
希望這個想法，可以幫到您

select top 2 t0.* from [yourTable] t0 
    where t0.id< 
        (select t1.id from [yourTable] t1 where t1.[是否符合1] = 1)
   order by t0.id desc
   
union all

select top 2 t0.* from [yourTable] t0 
    where t0.id<
        (select t1.id from [yourTable] t1 where t1.[是否符合2] = 1)
   order by t0.id desc

回應 3
分享
檢舉

CooperWu iT邦新手 3 級 ‧ 2021-10-06 16:51:43 檢舉

這邊有遇到一個問題是子查詢傳回不只一個值，我有補上Table的資料了，再麻煩您試試看

allenlwh iT邦高手 1 級 ‧ 2021-10-06 17:11:37 檢舉

declare @id int

CREATE TABLE #MyTempTable(
	[Id] [int] NOT NULL,
	[Value] [int] NOT NULL,
	[是否符合1] [bit] NULL,
	[是否符合2] [bit] NULL
) ON [PRIMARY]

--[是否符合1]
declare myCursor cursor for  	
	select t1.id from [Test2] t1 where t1.[是否符合1] = 1
open myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 
BEGIN 
	insert into #MyTempTable
		select top 3 t0.* from [Test2] t0 
			where t0.id<=@id        
		order by t0.id desc

	FETCH NEXT FROM myCursor INTO @id

END 
CLOSE myCursor 
DEALLOCATE myCursor

--[是否符合2]
declare myCursor cursor for  	
	select t1.id from [Test2] t1 where t1.[是否符合2] = 1
open myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 
BEGIN 
	insert into #MyTempTable
		select top 2 t0.* from [Test2] t0 
			where t0.id<=@id        
		order by t0.id desc

	FETCH NEXT FROM myCursor INTO @id

END 
CLOSE myCursor 
DEALLOCATE myCursor

select distinct * from #MyTempTable order by Id

drop table #MyTempTable

CooperWu iT邦新手 3 級 ‧ 2021-10-08 15:57:55 檢舉

感謝

登入發表回應

rogeryao

iT邦超人 8 級 ‧ 2021-10-06 19:43:50

想詢問SQL查詢連續資料的問題
用上面那篇的解法在變形一下 :

CREATE TABLE [dbo].[Test2](
  [Id] [int] NOT NULL,
  [Value] [int] NOT NULL,
  [是否符合1] [bit] NULL,
  [是否符合2] [bit] NULL
) ON [PRIMARY];

INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (1, 5, 0, 0);
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (2, 5, 0, 0);
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (3, 5, 0, 1);
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (4, 5, 0, 0);
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (5, 5, 1, 1);
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (6, 5, 0, 0);
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (7, 5, 1, 0);
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (8, 5, 0, 1);
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (9, 5, 0, 0);
INSERT [dbo].[Test2] ([Id], [Value], [是否符合1], [是否符合2]) VALUES (10, 5, 0, 0);

SELECT *
FROM (
SELECT *,
CASE WHEN SUM(CASE WHEN [是否符合1] > 0 THEN 1 ELSE 0 END)
OVER (ORDER BY ID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) >=1 THEN N'Y' 
ELSE '' END ST_Z_EX,
CASE WHEN SUM(CASE WHEN [是否符合2] > 0 THEN 1 ELSE 0 END)
OVER (ORDER BY ID ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) >=1 THEN N'Y' 
ELSE '' END ST_W_EX
FROM Test2) AS M
WHERE ST_Z_EX='Y' OR ST_W_EX='Y'

Demo