За последние 24 часа нас посетили 9654 программиста и 479 роботов. Сейчас ищут 286 программистов ...

Выборка из трёх таблиц + группировка

Тема в разделе "PHP и базы данных", создана пользователем AlexProg, 22 сен 2020.

Метки:
  1. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    290
    Симпатии:
    6
    Всем добра!

    Кто хочет прокачать скилл? У меня и у гугла не хватает знаний.

    Помогите, пожалуйста :)

    Есть три таблицы.
    1 - users
    2 - blue
    3- red

    Нужно выбрать количество упоминаний пользователей всего из двух таблиц.
    т.е. count (какой-то), имя из users и дату из users.

    Я пробовал так:

    Код (Text):
    1. SELECT *,count(*) as numgames, users.user FROM blue
    2.         LEFT JOIN users ON blue.user_id = users.id
    3.         LEFT JOIN red ON red.id = users.id
    4.         GROUP BY _blue.user_id, red.user_id
    5.         ORDER BY numgames DESC
    6.  
    7. ------
    8.  
    9. SELECT A.user_id
    10.             ,T.user_id
    11.             ,count(*) AS TotalAmount
    12.         FROM red AS A
    13.         LEFT JOIN blue AS T ON T.user_id = A.user_id
    14.         GROUP BY A.user_id,T.user_id
    15.  
    16. ------
    17.  
    18. select c.user_id,r.user_id,
    19.         (select user from users_001 WHERE r.user_id = users.id ) user,
    20.         (select date_reg from users_001 WHERE r.user_id = users.id ) date_reg,
    21.         (select id from users_001 WHERE r.user_id = users.id ) user_id,
    22.         count(*) as numgames
    23.         bets_blue c join red r on c.user_id=r.user_id
    24.         group by c.user_id,r.user_id order by numgames DESC

    Результат всегда ужасный. У ID = 30 максимум до 100 должно быть.

    Спасибо всем кто откликнется!
     

    Вложения:

  2. Drunkenmunky

    Drunkenmunky Новичок

    С нами с:
    12 авг 2020
    Сообщения:
    218
    Симпатии:
    36
    Выгрузите таблицы(или их часть) со структурой, и приведите пример ожидаемого результата.
     
  3. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    290
    Симпатии:
    6
    Код (Text):
    1.  
    2. CREATE TABLE `users` (
    3.   `id` int(11) NOT NULL,
    4.   `user` varchar(50) NOT NULL,
    5.   `email` varchar(50) NOT NULL,
    6.   `pass` varchar(80) CHARACTER SET utf8 NOT NULL,
    7.   `date_reg` int(11) NOT NULL DEFAULT 0,
    8.   `date_login` int(11) NOT NULL DEFAULT 0
    9. ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
    10.  
    11. ---
    12.  
    13. CREATE TABLE `blue` (
    14.   `id` int(11) NOT NULL,
    15.   `game_id` int(11) NOT NULL,
    16.   `user_id` int(11) NOT NULL,
    17.   `login` varchar(40) NOT NULL,
    18.   `color` varchar(10) NOT NULL
    19. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
    20.  
    21. ----
    22.  
    23. CREATE TABLE `red` (
    24.   `id` int(11) NOT NULL,
    25.   `game_id` int(11) NOT NULL,
    26.   `user_id` int(11) NOT NULL,
    27.   `login` varchar(40) NOT NULL,
    28.   `color` varchar(10) NOT NULL
    29. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

    Вася - ID 97 - упомянут в обеих таблицах 123 раза
    Петя - ID 45 - упомянут в обеих таблицах 67 раз
    Коля - ID 67 - упомянут в обеих таблицах 45 раз
    Даша - ID 89 - упомянут в обеих таблицах 12 раз

    НО! Вася может быть только в blue или red. Не обязательно что пользователь есть и там и там.
     
  4. Drunkenmunky

    Drunkenmunky Новичок

    С нами с:
    12 авг 2020
    Сообщения:
    218
    Симпатии:
    36
    Это не совсем то, что нужно.
    ID? Где это в структуре выше?
    Так не пойдет.
     
  5. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    290
    Симпатии:
    6
    ID Васи здесь - users.id

    Что не так-то?
     
  6. Sail

    Sail Активный пользователь

    С нами с:
    1 ноя 2016
    Сообщения:
    1.261
    Симпатии:
    289
    @AlexProg, на DB Fiddle создайте таблички с набором тестовых данных.
    А ещё есть UNION
     
  7. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    290
    Симпатии:
    6
    https://www.db-fiddle.com/f/4zar45u3igwAdtuj88xtJM/4
    --- Добавлено ---
    Но при этом необходимо помнить, что это все же не одно и то же, что и оператор JOIN
     
  8. Drunkenmunky

    Drunkenmunky Новичок

    С нами с:
    12 авг 2020
    Сообщения:
    218
    Симпатии:
    36
    Код (Text):
    1. SELECT CONCAT(`users`.`user`, ' - ID ' , `t1`.`user_id`, ' упомянут в обеих таблицах ', count(`t1`.`user_id`), ' раз')
    2. FROM
    3. (SELECT * FROM `blue`
    4. UNION
    5. SELECT * FROM `red`) AS `t1`
    6. JOIN `users`
    7. ON `users`.`id` = `t1`.`user_id`
    8. GROUP BY `t1`.`user_id`
     
  9. Sail

    Sail Активный пользователь

    С нами с:
    1 ноя 2016
    Сообщения:
    1.261
    Симпатии:
    289
    Так-то всё просто.
    Код (SQL):
    1. SELECT user_id, SUM(cnt) FROM (
    2.   SELECT COUNT(user_id) AS cnt, user_id FROM blue GROUP BY user_id
    3.   UNION
    4.   SELECT COUNT(user_id) AS cnt, user_id FROM red GROUP BY user_id
    5. ) AS subq GROUP BY user_id;
     
    #9 Sail, 22 сен 2020
    Последнее редактирование: 22 сен 2020
    AlexProg нравится это.
  10. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    290
    Симпатии:
    6
    Спасибо большое!

    Не могу сортировку сделать. Тех кто больше - выше всех. (DESC)
     
  11. Drunkenmunky

    Drunkenmunky Новичок

    С нами с:
    12 авг 2020
    Сообщения:
    218
    Симпатии:
    36
    Не то чтобы я критиковал, но три группировки, не считая пропущенного объединения - "просто"?
     
  12. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    290
    Симпатии:
    6
    Ваш пример ошибки выдал. По ссылке можно протестировать.
     
  13. Drunkenmunky

    Drunkenmunky Новичок

    С нами с:
    12 авг 2020
    Сообщения:
    218
    Симпатии:
    36
    user_id.jpg
     
    AlexProg нравится это.
  14. Sail

    Sail Активный пользователь

    С нами с:
    1 ноя 2016
    Сообщения:
    1.261
    Симпатии:
    289
    Нет предела совершенству :)
    Надо просто добавить в перечень group by поле, которое участвует в выборке. (...GROUP BY `t1`.`user_id`, `users`.`user`)
    Группировка - она такая. Часто содержимое полей, не участвующих в группировке неоднозначно.
    Реакция на подобную ситуацию зависит от настроек сервера СУБД.
     
    AlexProg нравится это.
  15. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    290
    Симпатии:
    6
    Я пробовал на DB Fiddle

    Вопрос с сортировкой открыт, ни как не могу победить :(
     
  16. Drunkenmunky

    Drunkenmunky Новичок

    С нами с:
    12 авг 2020
    Сообщения:
    218
    Симпатии:
    36
    Как вам выше написали группировка имеет свои особенности.
    Откройте базу в phpMyadmin и отлаживайте SQL в нем.
    Нет никаких проблем с сортировкой
    Код (Text):
    1. SELECT `users`.`user`, `t1`.`user_id`, count(`t1`.`user_id`) as `count`
    2. FROM
    3. (SELECT * FROM `blue`
    4. UNION
    5. SELECT * FROM `red`) AS `t1`
    6. JOIN `users`
    7. ON `users`.`id` = `t1`.`user_id`
    8. GROUP BY `t1`.`user_id`
    9. ORDER BY `users`.`user` ASC
     
    AlexProg нравится это.
  17. AlexProg

    AlexProg Активный пользователь

    С нами с:
    13 май 2014
    Сообщения:
    290
    Симпатии:
    6
    Да, действительно, сработало: ORDER BY `count` DESC

    Спасибо всем большое! От души!