как получить запрос mysql с количеством отзывов и суммой

ProgramBox

как получить запрос mysql с количеством отзывов и суммой

Post author:admin
Запись опубликована:9 февраля, 2023
Post category:Вопросы по программированию

#php #mysql

Вопрос:

Я использую таблицы ниже…

 CREATE TABLE IF NOT EXISTS `cuisine` (
  `cuisine_id` int(5) NOT NULL AUTO_INCREMENT,
  `cuisine_name` varchar(100) CHARACTER SET utf8 NOT NULL,
  `addeddate` datetime NOT NULL,
  PRIMARY KEY (`cuisine_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC AUTO_INCREMENT=30 ;

—

— Сброс данных для таблицы `cuisine`

 INSERT INTO `cuisine` (`cuisine_id`, `cuisine_name`, `addeddate`) VALUES
(1, 'Lebanese', '2012-06-18 18:57:48'),
(2, 'Græsk', '2012-06-18 18:58:03'),
(3, 'Spansk', '2012-06-18 18:58:11'),
(4, 'Japansk', '2012-06-18 18:58:18'),
(5, 'Mexicansk', '2012-06-18 18:58:27'),
(6, 'Thai', '2012-06-18 18:58:37'),
(7, 'Indisk', '2012-06-18 18:58:43'),
(8, 'Kinesisk', '2012-06-18 18:58:50'),
(9, 'Italiensk', '2012-06-18 18:58:56'),
(10, 'Fransk', '2012-06-18 18:59:02'),
(22, 'Tyrkisk', '2013-05-28 11:40:40'),
(23, 'Amerikansk', '2013-07-04 22:03:04'),
(24, 'Dansk', '2013-07-04 22:23:05'),
(25, 'Tandhoori', '2013-07-15 07:02:34'),
(29, 'tanish', '2013-11-12 04:57:02');

—

— Структура таблицы для таблицы `restaurants`

 CREATE TABLE IF NOT EXISTS `restaurants` (
  `restaurant_id` int(11) NOT NULL AUTO_INCREMENT,
  `restaurant_name` varchar(100) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`restaurant_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC AUTO_INCREMENT=125 ;

—

— Сброс данных для таблицы `restaurants`

 INSERT INTO `restaurants` (`restaurant_id`, `restaurant_name`) VALUES
(9, 'Demo Restaurant'),
(21, 'Adria Pizza'),
(22, 'Triumf Pizza Cafe'),
(23, 'Faxe Pizza'),
(24, 'Amager By Night'),
(28, 'Cafe Station'),
(29, 'La Rosa Pizzeria og Grill');

—

— Структура таблицы для таблицы `reviews`

 CREATE TABLE IF NOT EXISTS `reviews` (
  `rating_id` int(11) NOT NULL AUTO_INCREMENT,
  `restaurant_id` int(11) NOT NULL,
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`rating_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC AUTO_INCREMENT=137 ;

—

— Сброс данных для таблицы `reviews`

 INSERT INTO `reviews` (`rating_id`, `restaurant_id`, `rating`) VALUES
(108, 29, 3),
(109, 21, 5),
(127, 9, 5),
(128, 9, 5);

—

— Структура таблицы для таблицы `serving_cuisines`

 CREATE TABLE IF NOT EXISTS `serving_cuisines` (
  `sercuis_id` int(11) NOT NULL AUTO_INCREMENT,
  `restaurant_id` int(11) NOT NULL,
  `cuisine_id` int(11) NOT NULL,
  `status` enum('0','1') CHARACTER SET latin1 NOT NULL DEFAULT '1',
  `addeddate` datetime NOT NULL,
  PRIMARY KEY (`sercuis_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED AUTO_INCREMENT=6245 ;

—

— Сброс данных для таблицы `serving_cuisines`

 INSERT INTO `serving_cuisines` (`sercuis_id`, `restaurant_id`, `cuisine_id`, `status`, `addeddate`) VALUES
(1901, 11, 7, '1', '2013-08-08 15:25:40'),
(6194, 21, 22, '1', '2016-04-14 11:27:39'),
(6193, 21, 5, '1', '2016-04-14 11:27:39'),
(6192, 21, 9, '1', '2016-04-14 11:27:39'),
(5823, 22, 22, '1', '2014-09-22 13:50:47'),
(5822, 22, 5, '1', '2014-09-22 13:50:47'),
(5783, 23, 5, '1', '2014-09-19 18:30:30'),
(5782, 23, 9, '1', '2014-09-19 18:30:30'),
(2625, 25, 5, '1', '2013-11-29 13:58:15'),
(2624, 25, 9, '1', '2013-11-29 13:58:15'),
(753, 26, 9, '1', '2013-06-16 18:21:18'),
(754, 26, 5, '1', '2013-06-16 18:21:18'),
(2009, 27, 5, '1', '2013-08-22 17:11:25'),
(2008, 27, 9, '1', '2013-08-22 17:11:25'),
(5703, 28, 22, '1', '2014-09-16 01:00:26'),
(5702, 28, 5, '1', '2014-09-16 01:00:26'),
(5713, 29, 22, '1', '2014-09-16 01:07:11'),
(5712, 29, 5, '1', '2014-09-16 01:07:11'),
(6244, 9, 22, '1', '2016-09-19 14:09:22'),
(6243, 9, 5, '1', '2016-09-19 14:09:22'),
(6242, 9, 9, '1', '2016-09-19 14:09:22'),
(6241, 9, 24, '1', '2016-09-19 14:09:22'),
(6240, 9, 23, '1', '2016-09-19 14:09:22'),
(5711, 29, 9, '1', '2014-09-16 01:07:11'),
(5710, 29, 24, '1', '2014-09-16 01:07:11'),
(5709, 29, 23, '1', '2014-09-16 01:07:11'),
(6191, 21, 24, '1', '2016-04-14 11:27:39'),
(6190, 21, 23, '1', '2016-04-14 11:27:39'),
(5582, 24, 22, '1', '2014-09-05 13:54:22'),
(5581, 24, 5, '1', '2014-09-05 13:54:22'),
(5580, 24, 9, '1', '2014-09-05 13:54:22'),
(5579, 24, 24, '1', '2014-09-05 13:54:22'),
(5578, 24, 23, '1', '2014-09-05 13:54:22'),
(5701, 28, 9, '1', '2014-09-16 01:00:26'),
(5700, 28, 24, '1', '2014-09-16 01:00:26'),
(5699, 28, 23, '1', '2014-09-16 01:00:26'),
(5781, 23, 24, '1', '2014-09-19 18:30:30'),
(5780, 23, 23, '1', '2014-09-19 18:30:30'),
(5821, 22, 9, '1', '2014-09-22 13:50:47'),
(5820, 22, 24, '1', '2014-09-22 13:50:47'),
(5819, 22, 23, '1', '2014-09-22 13:50:47');

Я использую запрос ниже

 SELECT rest.restaurant_id, rest.restaurant_name, cui.cuisine_id,  ROUND( ( ( SUM(rvw.rating) / (COUNT(rvw.rating_id)*5) )*100 ) ,1) AS avg_rating, ROUND( ( ( SUM(rvw.rating) / (COUNT(rvw.rating_id)) )) ,1) AS ratingValue, COUNT(rvw.rating_id) AS ratingCount, SUM(rvw.rating) AS ratingSum FROM `restaurants`AS rest LEFT JOIN reviews AS rvw ON rest.restaurant_id = rvw.restaurant_id LEFT JOIN serving_cuisines AS cui ON rest.restaurant_id = cui.restaurant_id LEFT JOIN cuisine AS cuilist ON cuilist.cuisine_id = cui.cuisine_id WHERE 1 GROUP BY rest.restaurant_id

Я получаю результат, как показано ниже..

Но мой вывод должен выглядеть так, как показано ниже..

1. Давай, сбрось всю свою базу данных сюда.

2. Пожалуйста, проверьте выше, я добавил все свои таблицы

3. Сколько таблиц и данных осталось?? Лучше свести к минимуму ваш пример.

4. пожалуйста, проверьте мои отредактированные изменения

Ответ №1:

Это ваш запрос с упрощенными псевдонимами таблиц:

 SELECT r.restaurant_id, r.restaurant_name,
       ROUND( ( ( SUM(rvw.rating) / (COUNT(rvw.rating_id)*5) )*100 ) ,1) AS avg_rating,
       ROUND( ( ( SUM(rvw.rating) / (COUNT(rvw.rating_id)) )), 1) AS ratingValue,
       COUNT(rvw.rating_id) AS ratingCount,
       SUM(rvw.rating) AS ratingSum
FROM restaurants r LEFT JOIN
     reviews rvw
     ON r.restaurant_id = rvw.restaurant_id LEFT JOIN  
     serving_cuisines sc
     ON r.restaurant_id = sc.restaurant_id LEFT JOIN
     cuisine c
     ON c.cuisine_id = sc.cuisine_id
WHERE 1
GROUP BY r.restaurant_id;

Ваша проблема в том, что вы объединяетесь в двух «направлениях». Ресторан может иметь несколько рейтингов и (предположительно) в ресторане также может быть несколько кухонь. Итак, когда вы присоединяетесь, вы получаете декартово произведение для каждого ресторана.

Нормальное решение этой проблемы — выполнить агрегацию перед выполнением join .

У вас есть более простое решение. Просто удалите соединения с кухнями:

 SELECT r.restaurant_id, r.restaurant_name,
       ROUND( ( ( SUM(rvw.rating) / (COUNT(rvw.rating_id)*5) )*100 ) ,1) AS avg_rating,
       ROUND( ( ( SUM(rvw.rating) / (COUNT(rvw.rating_id)) )), 1) AS ratingValue,
       COUNT(rvw.rating_id) AS ratingCount,
       SUM(rvw.rating) AS ratingSum
FROM restaurants r LEFT JOIN
     reviews rvw
     ON r.restaurant_id = rvw.restaurant_id 
WHERE 1
GROUP BY r.restaurant_id;

Вы вообще не используете эти таблицы.

Далее, вы можете упростить запрос, используя AVG() :

 SELECT r.restaurant_id, r.restaurant_name,
       ROUND( AVG(rvw.rating) / 5)  * 100, 1) AS avg_rating,
       ROUND( AVG(rvw.rating), 1) AS ratingValue,
       COUNT(rvw.rating_id) AS ratingCount,
       SUM(rvw.rating) AS ratingSum
FROM restaurants r LEFT JOIN
     reviews rvw
     ON r.restaurant_id = rvw.restaurant_id 
WHERE 1
GROUP BY r.restaurant_id;

1. спасибо за ваш ответ, просто мне нужно добавить объединения в кухни,

2. Вам не нужны кухни в соответствии с опубликованным вами вопросом. Если у вас есть вопрос, который касается кухонь , задайте другой вопрос . Грубо изменять вопрос с ответами таким образом, чтобы ответы были недействительными. Это привлекает отрицательные голоса.

3. Вы не используете кухни в примере. Если они вам нужны для чего-то другого, вам, вероятно, следует использовать подзапрос, чтобы получить эту таблицу, которую вы выбираете сейчас, а затем присоединить ее к таблице cuisines. Смотрите это для получения дополнительной информации о подзапросах dev.mysql.com/doc/refman/5.7/en/subqueries.html