Поиск повторяющихся строк, которые отличаются в одном столбце

У меня есть следующая таблица в MySQL 5.1:

+--------------+----------------+------+-----+---------+----------------+
| Field        | Type           | Null | Key | Default | Extra          |
+--------------+----------------+------+-----+---------+----------------+
| log_id       | int(11)        | NO   | PRI | NULL    | auto_increment |
| date         | datetime       | NO   | MUL | NULL    |                |
| date_millis  | int(3)         | NO   |     | NULL    |                |
| eib_address  | varchar(20)    | NO   |     | NULL    |                |
| ip_address   | varchar(15)    | NO   |     | NULL    |                |
| value        | decimal(20,10) | NO   | MUL | NULL    |                |
| application  | tinyint(4)     | NO   |     | NULL    |                |
| phys_address | varchar(20)    | NO   |     | NULL    |                |
| orig_log_id  | bigint(20)     | NO   |     | NULL    |                |
+--------------+----------------+------+-----+---------+----------------+

В этой таблице log_id и orig_log_id всегда уникальны. Однако возможно, что две строки могут иметь повторяющиеся значения для любого из других полей. Игнорируя поля *log_id, наша проблема заключается в том, что две строки могут быть идентичными во всех других столбцах, но иметь разные значения для value. Я пытаюсь выяснить правильный SQL-запрос, чтобы определить, когда две (или более) строки имеют одинаковые значения для date, date_millis и eib_address, но разные значения для value, log_id и orig_log_id. До сих пор мне удалось придумать запрос, который выполняет первое предложение в моем предыдущем предложении:

SELECT main.* 
FROM sensors_log main
INNER JOIN 
    (SELECT date, date_millis, eib_address 
    FROM sensors_log 
    GROUP BY date, date_millis, eib_address 
    HAVING count(eib_address) > 1) dupes 
ON main.date = dupes.date 
    AND main.date_millis = dupes.date_millis 
    AND main.eib_address = dupes.eib_address;

Однако я не могу понять, когда value отличается. По крайней мере, я знаю, что просто добавление AND main.value != dupes.value в предложение ON ничего не даст!


mysql sql duplicates duplicate-data
person GarlicFries    schedule 23.03.2011    source источник

Ответы (2)


arrow_upward
8
arrow_downward

Я думаю, что это немного проще, чем вы пытаетесь это сделать. Попробуй это:

SELECT *
  FROM SENSORS_LOG s1
  INNER JOIN SENSORS_LOG s2
    ON (s2.DATE = s1.DATE AND
        s2.DATE_MILLIS = s1.DATE_MILLIS AND
        s2.EIB_ADDRESS = s1.EIB_ADDRESS)
  WHERE s1.VALUE <> s2.VALUE OR
        s1.LOG_ID <> s2.LOG_ID OR
        s1.ORIG_LOG_ID <> s2.ORIG_LOG_ID;

Делитесь и наслаждайтесь.

person Bob Jarvis - Reinstate Monica    schedule 23.03.2011
comment
Это более-менее то, что мне нужно. Я бы предпочел не иметь полного содержимого обеих таблиц, но это поможет. Спасибо. - person GarlicFries; 23.03.2011
comment
Это делает это так просто! Как раз то, что я искал, а также. Я, очевидно, выбрал только те столбцы, которые меня интересовали, но универсальность в этом решении велика. - person missscripty; 07.07.2016

arrow_upward
1
arrow_downward

Может быть, я неправильно понял задачу, но нельзя ли просто выполнить COUNT вот так?

SELECT date, date_millis, eib_address, count(*) as nr_dupes
FROM sensors_log
GROUP BY date, date_millis, eib_address
HAVING count(*) > 1

or 

SELECT date, date_millis, eib_address, 
       group_concat(value), group_concat(log_id), group_concat(orig_log_id)
FROM sensors_log
GROUP BY date, date_millis, eib_address
HAVING count(*) > 1
person Don    schedule 23.03.2011