I have very simple table like below

CREATE TABLE `tbl_data` (
  `id` int(11) NOT NULL,
  `won` tinyint(4) NOT NULL,
  `time` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_data`
--

INSERT INTO `tbl_data` (`id`, `won`, `time`) VALUES
(1, 1, '2022-10-18 05:21:37'),
(2, 2, '2022-10-18 05:21:37'),
(5, 0, '2022-10-18 05:22:02'),
(6, 2, '2022-10-18 05:22:02'),
(7, 2, '2022-10-18 05:22:18'),
(8, 1, '2022-10-18 05:22:18');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_data`
--
ALTER TABLE `tbl_data`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_data`
--
ALTER TABLE `tbl_data`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
COMMIT;

What I am trying to achieve is select all rows which contains pattern of won like 0,2,2 so it will select id called 5,6,7 in above example. if my pattern is like 1,2 it should select ids called 1 and 2 like this.

Database Fiddle is here

https://www.db-fiddle.com/#&togetherjs=RDJuie555L

I am finding solution from last hour but not able to achieve the goal. Let me know if any expert here can help me for same.

Thanks!

CodePudding user response：

WITH cte AS (
  SELECT id id0,
         LEAD(id) OVER w id1,
         LEAD(id,2) OVER w id2,
         CONCAT_WS(',',
                   won,
                   LEAD(won) OVER w,
                   LEAD(won,2) OVER w) won_list
  FROM tbl_data
  WINDOW w AS (ORDER BY id)
  )
SELECT tbl_data.*
FROM tbl_data
JOIN cte ON tbl_data.id IN (cte.id0, cte.id1, cte.id2)
WHERE cte.won_list = '0,2,2'

fiddle

PS. If there exists more than one copy of needed pattern then the rows will mix. Add ORDER BY id clause to the outer query for to sort output rows and any of cte.idN (N=0..2) to its output list for to see separate pattern groups.