Как найти ближайшее значение и объединить его с записью?

Question

Как найти ближайшее значение и объединить его с записью?

0

По сути, я хочу найти запись с ближайшим значением, извлечь из нее одно значение и добавить создать записи, которые состоят из этого значения. Я понятия не имею, возможно ли это вообще.

Так что мои записи выглядят так

Wire_tag|lenght|place|
----------------------
W1      |2     |closet
W2      |2.5   |NULL
W3      |3     |NULL
W4      |4     |conveyor
W5      |5     |NULL

Чего я хочу добиться, чтобы назначить wire_tag, где место столбца не равно NULL, с ближайшим значением длины провода, где место столбца отличается от нуля. Таким образом, результаты, которые я хочу достичь, примерно такие

Wire_tag|lenght|place   |wire_assign
------------------------------------
W1      |2.5   |closet  |W2
W4      |3     |conveyor|W3
W4      |5     |conveyor|W5

Toriam 31 янв. 2019, в 11:24

Источник

0

Я не понимаю последнюю строку вашего желаемого результата. Я бы ожидал (W4,4,conveyor,W5)
Robert Kock 31 янв. 2019, в 10:16
0

Ближайшее значение длины для W1 (2) - W2 (2.5). Ближайшее значение длины для W4 (4) - W3 (3) и W5 (5). Длина исходного провода (W1 и W4) теряется. Я редактировал оригинальный пост
Toriam 31 янв. 2019, в 10:27

Теги:

mysql

sql

2 ответа

0

Я думаю:

select t.*,
       (select t2.wire_tag
        from t t2
        where t2.place is not null
        order by abs(t2.length - t.length)
        limit 1
       ) closest_wire_tag
from t
where t.place is null;

Gordon Linoff 31 янв. 2019, в 10:53

Ещё вопросы

Я не понимаю последнюю строку вашего желаемого результата. Я бы ожидал (W4,4,conveyor,W5)
Ближайшее значение длины для W1 (2) - W2 (2.5). Ближайшее значение длины для W4 (4) - W3 (3) и W5 (5). Длина исходного провода (W1 и W4) теряется. Я редактировал оригинальный пост

Richard Hansell · Accepted Answer · 2019-01-31T08-09-00.000Z

Здесь два метода, первый (хороший) метод основан на использовании выражений общих таблиц, и я не уверен, будут ли они доступны в версии MySQL, на которой вы работаете, поэтому вторая версия не использует какие-либо "причудливые" SQL.

Сначала я создал временную таблицу, вам не понадобится этот шаг (и в любом случае это не синтаксис MySQL):

CREATE TABLE #wires (
    wire_tag VARCHAR(50),
    lenght NUMERIC(9,1),
    place VARCHAR(50));
INSERT INTO #wires SELECT 'W1', 2, 'closet';
INSERT INTO #wires SELECT 'W2', 2.5, NULL;
INSERT INTO #wires SELECT 'W3', 3, NULL;
INSERT INTO #wires SELECT 'W4', 4, 'conveyor';
INSERT INTO #wires SELECT 'W5', 5, NULL;

Поэтому замените #wires фактическим именем таблицы в запросах ниже:

--Nice way
WITH distances AS (
    SELECT
        w.wire_tag,
        w2.lenght,
        w.place,
        w2.wire_tag AS wire_assign,
        ABS(w2.lenght - w.lenght) AS distance
    FROM
        #wires w
        LEFT JOIN #wires w2 ON w2.place IS NULL
    WHERE
        w.place IS NOT NULL),
min_distances AS (
    SELECT
        wire_tag,
        MIN(distance) AS min_distance
    FROM
        distances
    GROUP BY
        wire_tag)
SELECT
    d.wire_tag,
    d.lenght,
    d.place,
    d.wire_assign
FROM
    distances d
    INNER JOIN min_distances m ON m.wire_tag = d.wire_tag AND m.min_distance = d.distance;

--Nasty way 
SELECT
    y.wire_tag,
    y.lenght,
    y.place,
    y.wire_assign
FROM
    (SELECT
        w.wire_tag,
        w2.lenght,
        w.place,
        w2.wire_tag AS wire_assign,
        ABS(w2.lenght - w.lenght) AS distance
    FROM
        #wires w
        LEFT JOIN #wires w2 ON w2.place IS NULL
    WHERE
        w.place IS NOT NULL) y
    INNER JOIN (
        SELECT
            wire_tag,
            MIN(distance) AS min_distance
        FROM
            (SELECT
                w1.wire_tag,
                ABS(w2.lenght - w1.lenght) AS distance
            FROM
                #wires w1
                LEFT JOIN #wires w2 ON w2.place IS NULL
            WHERE
                w1.place IS NOT NULL) x
        GROUP BY
            wire_tag) m ON m.wire_tag = y.wire_tag AND m.min_distance = y.distance;

Дает одинаковые результаты для обоих методов:

wire_tag    lenght  place   wire_assign
W1          2.5     closet      W2
W4          3.0     conveyor    W3
W4          5.0     conveyor    W5