Как вывести одинаковые значение полей через mysql?

Question

[Дмитрий Тарасов]

Всем привет!
Короче сейчас вопрос стоит так.
Есть бд users там хранятся юзеры: id, username.
есть бд users_auth там хранится последний вход пользователя на сайт. Там есть поля: id_user, user_agent, gpu, screen, langs(языки).

Мне нужно вывести пользователей у которых совпадают между собой значения полей допустим user_agent and gpu. Причем поиск может быть разным, в разных комбинациях gpu and screen и т.д.

Важно понимать что сейчас поиск не идет через where, хотя он тоже может быть если я захочу и буду знать по чем искать.

допустим у нас есть записи 1,2,3,4,5,6 у записей 1 и 2 между собой одинаковые поля user_agent and gpu и 4 и 6 тоже между собой одинаковые записи. ТО есть мы выведем 1,2 потом 4,6

Основой запрос идет к таблице users, а потом уже джойить таблицу users_auth но я хз как это сделать, чтобы искало совпадения само.

Также допустим мы хотим найти у кого одинаковые: user_agent, gpu, screen, langs
Как я говорил ранее поиск допустим по user_agent and gpu, записи 1 и 2, 3 и 4 были одинаковыми между собой, мы добавили langs и screen и у юзеров 3 и 4 отличается langs, значит они не должны выводиться, то есть при 100% совпадении всех полей в поиске только в этом случае выводим пары юзеров.

Мне вчера подсказали сделать такой запрос. Но у него ошибка.


Также сегодня подсказали сделать так


Оно работает, но что с ним дальше делать ума не приложу, типа через пыху взять и уже новый запрос делать, не знаю.

Вообщем вот ссылка на sqlfiddle, help!!!!

Answer 1

[ThunderCat]

SELECT u0.username, u1.*
FROM users_auth AS u1, users_auth AS u2 
LEFT join users AS u0
on u0.id = u2.user_id
WHERE u1.user_agent = u2.user_agent
AND u1.screen = u2.screen
AND u1.id != u2.id 
ORDER BY u2.user_agent,u2.screen;

Comments

[Дмитрий Тарасов]

ThunderCat , Я сделал вот такой запрос не будет ли он работать быстрее ?
Ведь запрос выше что ты дал, он выведет сначала все данные из таблиц
users_auth AS u1, users_auth AS u2 а потом уже будет через where отсекать не нужное ?
Мой запрос уже выведет уже связанные данные, с помощью where отсечет не нужные.

SELECT u1.*, ua1.*
FROM users u1 
join users_auth ua1 ON ua1.user_id = u1.id
join users u2 ON u2.id != u1.id
join users_auth ua2 ON ua2.user_id = u2.id
WHERE ua1.user_agent = ua2.user_agent 
AND ua1.langs = ua2.langs
GROUP BY u1.id ORDER BY ua1.user_agent, ua1.langs

[ThunderCat]

Дмитрий Тарасов, вообще для сервера эти запросы скорее всего эквивалентны, (для этого достаочно глянуть эксплэйн к запросам), просто смена записи, вы все равно сначала получаете произведение таблиц джоином и потом выборку по условию.

[Дмитрий Тарасов]

ThunderCat, можешь подсказать в чем разница между

FROM users_auth AS u1, users_auth AS u2 
LEFT join users AS u0
on u0.id = u2.user_id

и

FROM users u1 
join users_auth ua1 ON ua1.user_id = u1.id
join users u2 ON u2.id != u1.id
join users_auth ua2 ON ua2.user_id = u2.id

И как называется первый запрос(твой), мой это же просто join обычный которые выводит объединяет исходя из ON ?
Твой же вроде получает первую таблицу в полном объеме а после чего происходит слияние 2 и 3

Просто мне не понятно как и в каком случае использовать твой вариант назовем его multiple FROM, а в каком случае мой ?

[ThunderCat]

Дмитрий Тарасов, там ничего не происходит "сначала", просто возьми и сделай explain по обоим запросам - тебе покажет как строится план запроса и как работают при этом индексы.

FROM users_auth AS u1, users_auth AS u2это альтернативная запись джоин(в движке оно все равно преобразуется в джоин), просто вместо on указывается ключи по where. У тебя в изначальной задаче было свести пары из 1 таблицы, более короткой записью было так, потом еще добавилось условие с третьей таблицей, которую я записал через лефт джоин, больше для удобства чтения.

[Дмитрий Тарасов]

ThunderCat, я вижу лишь одно замечание в твоем запросе это смешивание ansi и non-ansi, то есть устаревшей конструкции и новой.
Не будет ли с этим проблем ?
это естественные объединения, которые неявно опасны, не так ли ?

[ThunderCat]

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

Answer 2

[d-stream]

из второй таблицы нужное количество раз group by по нужным комбинациям и это использовать как элемент join к первой таблице (select as table, cte, etc)

Comments

[Дмитрий Тарасов]

Не совсем понял, что нужно сделать, сделал, но оно не работает так как нужно или я не правильно понял ? sqlfiddle.com/#!9/fa29e4/48

SELECT a.*
FROM users a
JOIN (SELECT a1.user_id
FROM users_auth a1, users_auth a2
WHERE a1.user_agent = a2.user_agent
AND a1.gpu = a2.gpu
AND a1.id != a2.id
GROUP BY a1.user_agent, a1.gpu) b
GROUP BY a.id ORDER BY a.id

[d-stream]

Дмитрий Тарасов, в конечном итоге что именно хочется?

[Дмитрий Тарасов]

d-stream, Хочу чтобы это все работало как я описал выше, но ничего не выходит.
Если я хочу допустим сделать поиск по user_agent, gpu, screen, langs.
если юзера 1,2,3,4,5,6 у юзеров 1 и 2, все совпадает, все поля, а если больше нет юзеров у которых все совпадает на 100% значит их не нужно выводить.
Так же хочу чтобы эти юзеры выводились рядом друг с другом.
Если окажется что юзеров 1 и 4, 2 и 5 и 6 все совпало, то вывести их рядом друг с другом, сначала: 1 и 4, потом 2, 5, 6 etc.

[d-stream]

Дмитрий Тарасов, Вот - немножко "поиграться":

select 
user_id,
max(qqq)
from(
select 
  4 as qqq,
  user_id
from users_auth 
inner join (select user_agent,gpu,screen,langs from users_auth group by user_agent,gpu,screen,langs having count(*)>1) as by_all
on users_auth.user_agent=by_all.user_agent and users_auth.gpu=by_all.gpu 
and users_auth.screen=by_all.screen and users_auth.langs=by_all.langs 

union

select 
3 as qqq,
user_id
from users_auth inner join (select user_agent,gpu,screen from users_auth group by user_agent,gpu,screen having count(*)>1) as by_three
on users_auth.user_agent=by_three.user_agent and users_auth.gpu=by_three.gpu 
and users_auth.screen=by_three.screen 

union

select 
2 as qqq,
user_id
from users_auth inner join (select user_agent,gpu from users_auth group by user_agent,gpu having count(*)>1) as by_two
on users_auth.user_agent=by_two.user_agent and users_auth.gpu=by_two.gpu 
 ) as users 
 group by user_id
;

результат - можно использовать как inner join для отбора только повторяющихся юзеров, а qqq - "степени повторяемости"

ну и дальше поиграться с другими комбинациями "повторяемости"

p/s/ естественно - это тупо в "лоб" - не быстрый вариант )