大家好,我又遇到難題了。
我想查詢的是,假如有符合條件的話,要往上取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
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
希望對你有幫助 ^^
因為我不確定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
這邊有遇到一個問題是子查詢傳回不只一個值,我有補上Table的資料了,再麻煩您試試看
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
感謝
想詢問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'