MySQL - выберите несоответствующие данные в отношении один ко многим

Question

MySQL - выберите несоответствующие данные в отношении один ко многим

0

Прежде всего, этот вопрос касается PHP и MySQL

У меня есть две таблицы базы данных:

Таблица People:

person_id   |   field_1  |  field_2  |  other_fields...

И таблица Notes:

note_id   |  person_id  |  created_timestamp  |  other_fields...

Таблица People имеет отношение "один-много" к таблице Notes...
Каждый раз, когда в таблице Notes создается заметка, к ней привязывается метка времени, также назначается внешний ключ person_id.

Теперь...
Мне нужно найти всех People, у которых не было note против них за последние 30 дней.
Теперь я делаю это так:

Получите все заметки из таблицы Notes с отличными person_id и created_timestamp > 'time(31*86400)' (неточно. Я знаю, но мне подходит)
Прокрутите результаты и добавьте person_id во временный массив $temp
Получить все записи из таблицы People
Прокрутите каждую запись и выполните in_array() сравнение person_id с $temp

Это не очень эффективно и калечит приложение, когда есть много People или Notes.

Кто-нибудь получил лучшее решение. В идеале это может быть достигнуто с использованием только одного SQL-запроса.

Спасибо, что посмотрели

ekhaled 15 сен. 2010, в 19:14

Источник

Теги:

php

mysql

performance

5 ответов

1

LEFT JOIN/IS NULL

   SELECT p.*
     FROM PEOPLE p
LEFT JOIN NOTES n ON n.person_id = p.person_id
                 AND n.created_timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    WHERE n.note_id IS NULL

НЕ СУЩЕСТВУЕТ

SELECT p.*
  FROM PEOPLE p
 WHERE NOT EXISTS(SELECT NULL
                    FROM NOTES n
                   WHERE n.person_id = p.person_id
                     AND n.created_timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY))

NOT IN

SELECT p.*
  FROM PEOPLE p
 WHERE p.person_id NOT (SELECT n.person_id
                          FROM NOTES n
                         WHERE n.created_timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY))

Заключение

LEFT JOIN IS NULL является наиболее эффективным для MySQL, когда сравниваемые столбцы не являются нулевыми. Если сравниваемые столбцы были обнуляемы, NOT IN и NOT EXISTS более эффективны.

OMG Ponies 15 сен. 2010, в 15:00

1

Стандартное решение - использовать подзапрос этой формы:

Select * from people where PersonID NOT in 
  (select PersonID from Notes where Created_Timestamp>...)

Другой вариант - сделать правое внешнее соединение в Notes и фильтровать только для Notes.PersonID IS NULL, который дает только строки, которые не совпадают с Notes.

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

Elemental 15 сен. 2010, в 14:45

1

Я считаю, что это должно работать:

SELECT * FROM people WHERE person_id NOT IN (SELECT DISTINCT person_id FROM notes);

MikeTheReader 15 сен. 2010, в 14:04

2

Это не ограничивает результаты за последние 30 дней.
halfdan 15 сен. 2010, в 16:21

1

Если это важно, вы можете рассмотреть денормализацию: сохранение метки времени последней заметки в таблице пользователя и индексацию этого столбца.

В противном случае нет способа избежать обхода всей таблицы людей, поэтому добавьте индекс в пару (person_id, timestamp) таблицы заметок и используйте левое соединение или подзапрос:

SELECT * FROM people 
         LEFT JOIN notes ON people.person_id = notes.person_id
                        AND notes.created_timestamp < NOW() - INTERVAL 30 DAY
WHERE notes.person_id IS NULL

SELECT * FROM people
WHERE person_id NOT IN (SELECT person_id FROM notes
                        WHERE created_timestamp < NOW() - INTERVAL 30 DAY)

Victor Nicollet 15 сен. 2010, в 13:28

Ещё вопросы

Это не ограничивает результаты за последние 30 дней.

halfdan · Accepted Answer · 2010-09-15T14-06-00.000Z

SELECT person_id FROM People WHERE person_id NOT IN 
    (SELECT person_id FROM Note 
        WHERE created_timestamp > DATE_SUB(CURDATE(), INTERVAL 30 DAY))

Это предполагает, что create_timestamp имеет тип "DATE", "TIMESTAMP" или "DATETIME". Если вы используете временную метку unix, преобразуйте ее в временную метку MySQL с помощью FROM_UNIXTIME(created_timestamp)

Спасибо, выглядит многообещающе ... Я использую метку времени Unix, поэтому полный запрос будет таким: SELECT person_id FROM People WHERE person_id NOT IN (SELECT person_id FROM Примечание WHERE FROM_UNIXTIME (create_timestamp)> DATE_SUB (CURDATE (), INTERVAL 30 DAY)