在多行的两列之间选择值

我必须更改触发器，因为它不接受发送或第三行

如你看到的

您可以使用NEW.product_id来访问所有新内容，完全不需要选择

接下来需要更改的是我现在没有ID了，所以我再次使用了NEW.product：id。

模式（MySQL v5.7）

CREATE TABLE `product` (  `product_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  `product` varchar(100) NOT NULL,  `total_quantity` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE `range_and_prices` (  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  `ranges_from` int(11) NOT NULL,  `ranges_to` int(11) NOT NULL,  `prices` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE `range` (  `product_id` INT NOT NULL PRIMARY KEY,  `range_prices` int(11)  NULL,  FOREIGN KEY (`product_id`) REFERENCES `product`(`product_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO `range_and_prices` (`ranges_from`,`ranges_to`, `prices`) VALUES('1','20', 10),('21','40', 20),('41','60', 40);CREATE TRIGGER `range_on_product`AFTER insert ON `product`FOR EACH ROWinsert into `range`(`product_id`, `range_prices`) VALUES (NEW.product_id , (SELECt DISTINCT prices FROM range_and_prices WHERe (SELECt total_quantity FROM product WHERe product_id=NEW.product_id) BETWEEN ranges_from AND ranges_to ORDER BY prices ASCLIMIT 1 ) );INSERT INTO `product` ( `product`, `total_quantity`) VALUES ("Coffee", "5"),("sugar", "25");

查询＃1

SELECt * FROM `range_and_prices`;| id  | ranges_from | ranges_to | prices || --- | ----------- | --------- | ------ || 1   | 1| 20        | 10     || 2   | 21          | 40        | 20     || 3   | 41          | 60        | 40     |

查询＃2

SELECt * FROM `product`;| product_id | product | total_quantity || ---------- | ------- | -------------- || 1          | Coffee  | 5   || 2          | sugar   | 25  |

查询3

SELECt * FROM `product` INNER JOIN `range` ON product.product_id=range.product_id;| product_id | product | total_quantity | product_id | range_prices || ---------- | ------- | -------------- | ---------- | ------------ || 1          | Coffee  | 5   | 1          | 10|| 2          | sugar   | 25  | 2          | 20|