iT邦幫忙

0

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

  • 分享至 

  • xImage

大家好,我又遇到難題了。
我想查詢的是,假如有符合條件的話,要往上取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
假定你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 }} |
{{ formatDate(item.duration) }}
直播中
0
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

希望對你有幫助 ^^

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

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

0
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
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 檢舉

感謝

1
rogeryao
iT邦超人 7 級 ‧ 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

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

謝謝回答

我要發表回答

立即登入回答