获取给定值位于具有给定条件的两行之间的行 [英] Fetching rows whose value is given which lies between two rows with given condition

问题描述

我有带有值的 mysql 架构

I have mysql schema with values

示例架构和数据

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `items` varchar(25) DEFAULT NULL,
  `itemtype` tinyint(1) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=latin1;

       insert into `products` (`id`, `items`, `itemtype`, `category`) values('1','APPLE','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('2','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('3','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('4','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('5','APPLE','1','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('6','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('7','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('8','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('9','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('10','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('11','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('12','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('13','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('14','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('15','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('16','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('17','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('18','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('19','APPLE','1','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('20','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('21','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('22','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('23','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('24','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('25','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('26','APPLE','1','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('27','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('28','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('29','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('30','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('31','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('32','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('33','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('34','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('35','APPLE','1','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('36','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('37','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('38','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('39','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('40','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('41','APPLE','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('42','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('43','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('44','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('45','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('46','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('47','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('48','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('49','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('50','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('51','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('52','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('53','APPLE','1','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('54','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('55','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('56','MANGO','0','FM1010');
insert into `products` (`id`, `items`, `itemtype`, `category`) values('57','APPLE','1','FM1010');

问题

要求是我需要获取 item=APPLE 和 itemtype=1 位于具有 item=APPLE 的行之间的所有行的 id, itemtype=0 和 item=APPLE, itemtype=1

Requirement is that i need to fetch id of all the rows where item=APPLE and itemtype=1 which lies between the rows which has item=APPLE, itemtype=0 and item=APPLE, itemtype=1

因此，在示例数据中，在 id=5 (Apple,1) 和 'id=41' (Apple,0) 之间，有 3 行包含项 APPLE，项类型为 1(行ID 为 19、26 和 35).

So in example data, between id=5 (Apple,1) and 'id=41' (Apple,0), there are 3 rows which has items APPLE with item type 1 (row with ids 19, 26 and 35).

同样在 id=41 (Apple,0) 和 id=57 (Apple,1) 之间，再次有 2 行包含项目 APPLE，项目类型为 1(ID 为 45 和 53 的行).

Similarly between id=41 (Apple,0) and id=57 (Apple,1), there are again 2 rows which has items APPLE with item type 1 (row with ids 45 and 53).

因此所需的输出 ID 为 19,26,35,45,53

So the required output ids are 19,26,35,45,53

更新

说明

第一个边界是id为1(Apple,0)的行，寻找下一个边界该行应为 APPLE,1，在本例中为 id 5 的行.现在在 1 之间5 没有苹果，1 所以我们忽略这一点.

The first boundary is row with id 1 (Apple,0), to find next boundary the row should be APPLE,1, in this case row with id 5. Now between 1 and 5 there is no Apple,1 so we ignore this.

现在我们取行 id 5 (Apple,1) 作为第一个边界，下一个边界应该是带有 APPLE,0 的行，即第 41 行.

Now we take row id 5 (Apple,1) as a first boundary, the next boundary should be row with APPLE,0, which is row 41.

现在我们有 3 行，在这两行之间有 APPLE,1.19,26 和 35

Now we have 3 rows with APPLE,1 between these two. 19,26 and 35

现在第一个边界是 41 Apple,0，现在下一个边界应该是 APPLE,1这是行 id 53.在这两者之间没有 APPLE,1 ，所以我们忽略它

now first boundary is 41 Apple,0, now next boundary should be APPLE,1 which is row id 53. between these two there is no APPLE,1 , so we ignore it

所以最终结果应该是 19,26,35

So final result should be 19,26,35

有几个数据，我们要匹配所有的APPLE，APPLE,0和APPLE,1之间的1

There are several data, we have to match all APPLE, 1 between APPLE,0 and APPLE,1

更新我已经修改了架构和问题

UPDATED I have modified the schema and the problem

推荐答案

试试这个查询

SELECT *
FROM products
WHERE items = 'apple'
AND itemtype = '1'
AND id > 
    (
        SELECT MIN(id) AS id
        FROM products
        WHERE items = 'apple'
        AND itemtype = '1'
    )
AND id < 
    (
        SELECT MIN(id)
        FROM products
        WHERE items = 'apple'
        AND itemtype = '0'
        AND id > 
            (
                SELECT MIN(id) AS id
                FROM products
                WHERE items = 'apple'
                AND itemtype = '1'
            )
    );

这篇关于获取给定值位于具有给定条件的两行之间的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！