MySQL 查找最接近或完全匹配的值与更改查找列

Question

我有以下几组数据...

状态监测位置数据(CML表)

+--------+--------------------+--------------------+--------------+------------+--------------------+-------+-----+
| CML_ID |     POF_COLUMN     |      CML_TYPE      | SAMPLE_VALUE | COMPLIANCE | CORROSION_SEVERITY | LR_LD | POF |
+--------+--------------------+--------------------+--------------+------------+--------------------+-------+-----+
|      1 | SAMPLE_VALUE       | MIC_SAMPLING_POINT |            5 | NO         | MINOR              |     1 |     |
|      2 | SAMPLE_VALUE       | MIC_SAMPLING_POINT |          0.5 | NO         | MINOR              |     2 |     |
|      3 | SAMPLE_VALUE       | MIC_SAMPLING_POINT |           20 | NO         | MINOR              |     3 |     |
|      4 | COMPLIANCE         | VALVE_ROTATED      |            0 | YES        | MINOR              |     4 |     |
|      5 | LR_LD              | PIPING_THICKNESS   |            0 | YES        | MINOR              |   0.1 |     |
|      6 | CORROSION_SEVERITY | VESSEL_SHELL       |            0 | NO         | SEVERE             |     0 |     |
|      7 | CORROSION_SEVERITY | NOZZLE             |            0 | NO         | LOW                |     0 |     |
+--------+--------------------+--------------------+--------------+------------+--------------------+-------+-----+

查找失败概率数据(POF表)

+--------------------+--------------------+-------------+-----+
|     POF_COLUMN     |      CML_TYPE      | VALUE_RANGE | POF |
+--------------------+--------------------+-------------+-----+
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 1           |   5 |
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 5           |   4 |
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 10          |   3 |
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 15          |   2 |
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 100         |   1 |
| COMPLIANCE         | VALVE_ROTATED      | YES         |   5 |
| COMPLIANCE         | VALVE_ROTATED      | NO          |   1 |
| LR_LD              | PIPING_THICKNESS   | 2           |   5 |
| LR_LD              | PIPING_THICKNESS   | 1.5         |   4 |
| LR_LD              | PIPING_THICKNESS   | 1           |   3 |
| LR_LD              | PIPING_THICKNESS   | 0.8         |   2 |
| LR_LD              | PIPING_THICKNESS   | 0.5         |   1 |
| CORROSION_SEVERITY | VESSEL_SHELL       | NEGLIGIBLE  |   5 |
| CORROSION_SEVERITY | VESSEL_SHELL       | LOW         |   4 |
| CORROSION_SEVERITY | VESSEL_SHELL       | MEDIUM      |   3 |
| CORROSION_SEVERITY | VESSEL_SHELL       | HIGH        |   2 |
| CORROSION_SEVERITY | VESSEL_SHELL       | SEVERE      |   1 |
| CORROSION_SEVERITY | NOZZLE             | NEGLIGIBLE  |   5 |
| CORROSION_SEVERITY | NOZZLE             | LOW         |   5 |
| CORROSION_SEVERITY | NOZZLE             | MEDIUM      |   5 |
| CORROSION_SEVERITY | NOZZLE             | HIGH        |   3 |
| CORROSION_SEVERITY | NOZZLE             | SEVERE      |   2 |
+--------------------+--------------------+-------------+-----+

并且我需要使用 [POF_Column]、[CML_Type] 以及每个 CML 记录的 [SAMPLE_VALUE]、[COMPLIANCE]、[CORROSION_SEVERITY] 或 [LR_LD] 字段返回 POF 表中最接近或完全匹配的记录在 CML 表中。然后更新记录POF到CML表。

例如，如果我们查看 CML_ID = 2。

按 [POF_COLUMN] = 'SAMPLE_VALUE'、[CML_TYPE] = 'MIC_SAMPLING_POINT' 和 [VALUE_RANGE] 过滤 POF 表 - [SAMPLE_VALUE] 列中的值(在本例中为 0.5)是最小值。

在这种情况下，它将匹配 POF 表中的第一条记录并返回值 POF = 5。

如果我们再看一个案例。 CML_ID = 7。

通过 [POF_COLUMN] = 'CORROSION_SEVERITY'、[CML_TYPE] = 'NOZZLE' 和 [VALUE_RANGE] = [CORROSION_SEVERITY] 列中的值过滤 POF 表，在本例中为“LOW”。

在这种情况下，它将匹配 POF 表中从底部开始的第四行并返回 POF = 5 的值。

总而言之，我需要更新 CML 表以显示以下结果...

+--------+---+-----+
| CML_ID | … | POF |
+--------+---+-----+
|      1 | … |   4 |
|      2 | … |   5 |
|      3 | … |   2 |
|      4 | … |   5 |
|      5 | … |   1 |
|      6 | … |   1 |
|      7 | … |   5 |
+--------+---+-----+

有谁知道我怎样才能做到这一点？我在下面放置了一些我尝试的示例代码。这适用于查找完全匹配的值，但不适用于最接近的匹配值。

DROP DATABASE IF EXISTS VESSELS;
CREATE DATABASE VESSELS;
USE VESSELS;

CREATE TABLE CML (
  `CML_ID`              INTEGER NOT NULL,
  `POF_COLUMN`          VARCHAR(50),
  `CML_TYPE`            VARCHAR(50),
  `SAMPLE_VALUE`        FLOAT,
  `COMPLIANCE`          ENUM('YES','NO'),
  `CORROSION_SEVERITY`  VARCHAR(50),
  `LR_LD`               FLOAT,
  `POF`                 TINYINT,
  PRIMARY KEY (CML_ID)
);

CREATE TABLE POF (
  `POF_COLUMN`   VARCHAR(50),
  `CML_TYPE`     VARCHAR(50),
  `VALUE_RANGE`  VARCHAR(100),
  `POF`          TINYINT,
  PRIMARY KEY (POF_COLUMN, CML_TYPE, VALUE_RANGE)
);

INSERT INTO CML 
VALUES (1, 'SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 5, 'NO', 'MINOR', 1, NULL),
       (2, 'SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 0.5, 'NO', 'MINOR', 2, NULL),
       (3, 'SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 20, 'NO', 'MINOR', 3, NULL), 
       (4, 'COMPLIANCE', 'VALVE_ROTATED', 0, 'YES', 'MINOR', 4, NULL), 
       (5, 'LR_LD', 'PIPING_THICKNESS', 0, 'YES', 'MINOR', 0.1, NULL),
       (6, 'CORROSION_SEVERITY', 'VESSEL_SHELL', 0, 'NO', 'SEVERE', 0, NULL),
       (7, 'CORROSION_SEVERITY', 'NOZZLE', 0, 'NO', 'LOW', 0, NULL);

INSERT INTO POF 
VALUES ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 1, 5),
       ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 5, 4),
       ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 10, 3),
       ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 15, 2),
       ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 100, 1),
       ('COMPLIANCE', 'VALVE_ROTATED', 'YES', 5), 
       ('COMPLIANCE', 'VALVE_ROTATED', 'NO', 1), 
       ('LR_LD', 'PIPING_THICKNESS', 2, 5),
       ('LR_LD', 'PIPING_THICKNESS', 1.5, 4),
       ('LR_LD', 'PIPING_THICKNESS', 1, 3),
       ('LR_LD', 'PIPING_THICKNESS', 0.8, 2),
       ('LR_LD', 'PIPING_THICKNESS', 0.5, 1),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'NEGLIGIBLE', 5),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'LOW', 4),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'MEDIUM', 3),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'HIGH', 2),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'SEVERE', 1),
       ('CORROSION_SEVERITY', 'NOZZLE', 'NEGLIGIBLE', 5),
       ('CORROSION_SEVERITY', 'NOZZLE', 'LOW', 5),
       ('CORROSION_SEVERITY', 'NOZZLE', 'MEDIUM', 5),
       ('CORROSION_SEVERITY', 'NOZZLE', 'HIGH', 3),
       ('CORROSION_SEVERITY', 'NOZZLE', 'SEVERE', 2);

-- UPDATE EXACT MATCHING VALUE
UPDATE CML c
LEFT JOIN POF p
ON c.POF_COLUMN = p.POF_COLUMN
   AND c.CML_TYPE = p.CML_TYPE
   AND ( (c.POF_COLUMN = 'COMPLIANCE' AND c.COMPLIANCE = p.VALUE_RANGE) OR
         (c.POF_COLUMN = 'SAMPLE_VALUE' AND c.SAMPLE_VALUE = p.VALUE_RANGE) OR
         (c.POF_COLUMN = 'LR_LD' AND c.LR_LD = p.VALUE_RANGE) OR
         (c.POF_COLUMN = 'CORROSION_SEVERITY' AND c.CORROSION_SEVERITY = p.VALUE_RANGE)
       )
SET c.POF = p.POF;

SELECT * FROM CML;

Answer 1

为包含示例数据和预期结果的非常详细的帖子点赞。问题出在 sample_value 和 lr_ld 上，因为它可能不是 POF 表中的准确值。但是，您会注意到这些值等于或小于值范围。

因此，如果我们得到POF值的最大值，其中sample_value或lr_ld小于或等于取值范围，那么我们只需要得到POF值的最大值。

只有当 sample_value 或 lr_ld 增加时 POF 值增加，此查询才有效。

UPDATE CML c
  JOIN 
(
    select c.CML_ID, max(p.POF) POF 
    from CML c
    LEFT JOIN POF p
    ON c.POF_COLUMN = p.POF_COLUMN
       AND c.CML_TYPE = p.CML_TYPE
       AND ( (c.POF_COLUMN = 'COMPLIANCE' AND c.COMPLIANCE = p.VALUE_RANGE) OR
             (c.POF_COLUMN = 'SAMPLE_VALUE' AND  c.SAMPLE_VALUE<=p.VALUE_RANGE) OR
             (c.POF_COLUMN = 'LR_LD' AND c.LR_LD  <= p.VALUE_RANGE) OR
             (c.POF_COLUMN = 'CORROSION_SEVERITY' AND c.CORROSION_SEVERITY = p.VALUE_RANGE)
           )
     group by c.CML_ID 
   ) t
 on c.CML_ID = t.CML_ID
set c.POF = t.POF;