SQL: выбрать случайную строку из таблицы, где идентификатор строки отсутствует в другой таблице?

Я искал быстрые способы выбора случайной строки из таблицы и нашел следующий сайт: http://74.125.77.132/search?q=cache:http://jan.kneschke.de/projects/mysql/order-by-rand/&hl=en&strip=1

Что я хочу сделать, так это выбрать случайный URL-адрес из моей таблицы «URL-адреса», которого у меня НЕТ в моей другой таблице «urlinfo». Запрос, который я сейчас использую, выбирает случайный URL-адрес из «URL-адресов», но мне нужно его изменить чтобы возвращать только случайный URL-адрес, которого НЕТ в таблице 'urlinfo'.

Вот запрос:

SELECT url 
FROM urls JOIN (SELECT CEIL(RAND() * (SELECT MAX(urlid)
                                     FROM urls
                                     )
                           ) AS urlid 
               ) AS r2 USING(urlid);

И две таблицы:

CREATE TABLE urls (
 urlid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 url VARCHAR(255) NOT NULL
) ENGINE=INNODB;


CREATE TABLE urlinfo (
 urlid  INT NOT NULL PRIMARY KEY,
 urlinfo VARCHAR(10000),
 FOREIGN KEY (urlid) REFERENCES urls (urlid)
   ) ENGINE=INNODB;

sql random row
person johnrl    schedule 26.03.2010    source источник

Ответы (3)


arrow_upward
3
arrow_downward

Как насчет работы с этим случайным решением:

SELECT TOP 1 * FROM urls
WHERE (SELECT COUNT(*) FROM urlinfo WHERE urlid = urls.urlid) = 0
 ORDER BY NEWID()
person LorenVS    schedule 26.03.2010
comment
-1 SQL не имеет оператора ==; В MySQL нет функции NEWID(); и TOP 1 тоже не будет работать в MySQL :) - person Andomar; 27.03.2010
comment
Мой плохой оператор ==, исходный вопрос никогда явно не упоминал mysql, возможно, я пропустил ссылки на InnoDB при первом взгляде на вопрос - person LorenVS; 29.03.2010

arrow_upward
1
arrow_downward

Вы можете использовать where not exists для исключения строк из другой таблицы. Для случайной строки одним из вариантов является order by rand() с limit 1:

SELECT url
FROM urls
WHERE NOT EXISTS (
    SELECT *
    FROM urlinfo ui
    WHERE ui.urlid = urls.urlid
)
ORDER BY RAND()
LIMIT 1
person Andomar    schedule 26.03.2010
comment
order by rand() limit 1 — лучший способ выбрать случайную строку, если у вас небольшая таблица (‹100 строк) или вас не волнует производительность. Если ваша таблица большая, этот запрос может генерировать 100 000 случайных чисел и сканировать их все в поисках наименьшего. Это дорого. - person David; 27.03.2010
comment
Что ж, на моей машине генерация 100 000 случайных чисел и выбор наименьшего выполняется быстрее, чем запуск SELECT MAX(urlid) FROM newUrls. - person Andomar; 27.03.2010

arrow_upward
1
arrow_downward

Сначала вам нужно выполнить левое внешнее соединение, чтобы получить набор записей в «urls», которых нет в «urlinfo», а затем выбрать случайную запись из этого набора.

SELECT * FROM urls
LEFT OUTER JOIN urlinfo
ON urls.urlid = urlinfo.urlid
WHERE urlinfo.urlid IS null

Теперь выберите случайную строку из этого набора — вы можете сделать что-то вроде

SELECT newUrls.url
FROM (    
      SELECT urls.urlid, urls.url FROM urls
      LEFT OUTER JOIN urlinfo
      ON urls.urlid = urlinfo.urlid
      WHERE urlinfo.urlid IS null
     ) as newUrls
WHERE urls.urlid >= RAND() * (SELECT MAX(urlid) FROM urls) LIMIT 1

Однако это будет работать только в том случае, если URL-адреса в urlinfo примерно случайным образом распределены по диапазону возможных значений.

person David    schedule 26.03.2010
comment
+1 Вашему подзапросу нужен псевдоним, а * выдает ошибку с повторяющимся именем столбца. Но в остальном хороший ответ :) - person Andomar; 26.03.2010
comment
Спасибо. Отредактировано, чтобы исправить оба (надеюсь, на данный момент у меня нет под рукой тестовой базы данных mysql) - person David; 27.03.2010
comment
Получите VMWare :) MySQL говорит, что ERROR 1146 (42S02) at line 20: Table 'newUrls' doesn't exist, а select urls.url должно быть newUrls.url :) - person Andomar; 27.03.2010