在多行的两列之间选择值

admin

在多行的两列之间选择值

sql

我已经阅读了许多主题和文章以及官方的MYSQL文档，但是却找不到任何可以帮助我理解如何解决此问题的东西。

甚至这个SO主题都无法满足我的需求（在其中，他们没有尝试搜索大于2的值，您将明白为什么这是一个问题）。

例如，我在range_and_prices表中定义了范围：

---

| id  | ranges_from | ranges_to | prices |
| --- | ----------- | --------- | ------ |
| 1   | 1           | 20        | 10     |
| 2   | 21          | 40        | 20     |
| 3   | 41          | 60        | 40

当我尝试插入具有total_quantity 5的产品时；

| product_id | product | total_quantity |
| ---------- | ------- | -------------- |
| 1          | Coffee  | 5              |

我得到了我的采石场的结果，因为它应该是（因为total_quantity为5，它在1-20的范围内，而range_prices的范围是10）：

| product_id | product | total_quantity | product_id | range_prices |
| ---------- | ------- | -------------- | ---------- | ------------ |
| 1          | Coffee  | 5              | 1          | 10           |

但是，一旦我尝试进入其他范围，range_prices就会为空。

如果我选择了25，结果应该是：

| product_id | product | total_quantity | product_id | range_prices |
| ---------- | ------- | -------------- | ---------- | ------------ |
| 1          | Coffee  | 25             | 1          | 20           |

但是对于20以上的total_quantity，我将得到null。

在数据库小提琴上查看

   insert into `range`(`product_id`, `range_prices`) VALUES 
    ((SELECT LAST_INSERT_ID() AS id FROM product ), 
(SELECT prices FROM range_and_prices WHERE 
(SELECT total_quantity FROM product WHERE product_id=id) 
BETWEEN ranges_from AND ranges_to ORDER BY prices ASC
    LIMIT 1 ) );

我相信我知道那是为什么。例如，如果我选择25，则该数字在1到40之间，并且结果是两个范围，因此会感到困惑。我厌倦了各种事情，例如限制结果，尝试获得范围的最大值和最小值，我尝试了大约20种不同的事物，并且每次都获得相同的结果。以太是我的逻辑坏，或者对我而言，这对我来说很重要。请帮忙。

阅读 166

2021-07-01

共1个答案

admin

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

如你看到的

您可以使用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 ROW
insert 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 ASC
LIMIT 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           |

2021-07-01