Как сделать выборку по нескольким таблицам в одном запросе sql?
всем привет у меня есть такая вот задача (Нужно отсортировать заведения по рейтингу из их отзывов, если заведение является сетью (например Макдональдс) то нужно считать его рейтингом среднее арифметическое рейтингов его филиалов, если заведение является филиалом его не нужно выводить, так как его рейтинг входит в рейтинг сети, если у заведения нет review то его рейтингом будет 0,
Для сетей нужно считать именно среднее арифметическое рейтингов филиалов а не среднее арифметическое рейтингов review всех филиалов,
если у филиала ко-во review будет не чётным такой подсчет будет не верным) . Подскажите пожалуйста в каком направлении мне нужно двигаться потому как я новичок в sql запросах и такой запрос для меня на этом этапе проблема.Буду благодарен за любую помощь.
Дополнительно:
Опубликуйте версию базы данных, структуру таблиц и тестовые данные
, а это скрипт базы. На данный момент у меня получается только получить среднее по ревью и еще какие то результаты но только не то что нужно.
CREATE SCHEMA IF NOT EXISTS EstablishmentHomeWork;
USE EstablishmentHomeWork;
CREATE TABLE IF NOT EXISTS Establishment
(
id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
name VARCHAR(100) NOT NULL,
is_chain BOOLEAN default false,
chain_id BIGINT,
FOREIGN KEY (chain_id)
REFERENCES Establishment (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS Review
(
id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
establishment_id BIGINT NOT NULL,
FOREIGN KEY (establishment_id)
REFERENCES Establishment (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS Rate
(
id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
rate DOUBLE,
type VARCHAR(100) NOT NULL,
review_id BIGINT NOT NULL,
FOREIGN KEY (review_id)
REFERENCES Review (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
INSERT
INTO Establishment(name, is_chain, chain_id)
VALUES ('Starbucks', true, null),
('Starbucks New York1', false, 1),
('Starbucks Philadelphia', false, 1),
('Starbucks New York2', false, 1),
('Starbucks Los Angeles1', false, 1),
('Starbucks Los Angeles2', false, 1),
('Masa', false, null),
('Beard Papas', false, null),
('Joanne', false, null),
('Brooklyn Fare', false, null),
('KFC', true, null),
('KFC Los Angeles', false, 11),
('KFC New York', false, 11),
('KFC Philadelphia', false, 11),
('McDonald''s', true, null),
('McDonald''s Philadelphia', false, 15),
('McDonald''s Los Angeles', false, 15),
('McDonald''s New York', false, 15);
INSERT INTO Review(establishment_id)
VALUES (2),
(2),
(3),
(3),
(4),
(5),
(7),
(8),
(8),
(8),
(9),
(10),
(12),
(13),
(14),
(16),
(16),
(16),
(17),
(17),
(18),
(18),
(18),
(18);
INSERT INTO Rate(rate,type,review_id)
VALUES (9.2,'FOOD',1),
(9.1,'SERVICE',1),
(7.1,'PRICES',1),
(10,'INTERIOR',1),
(7.8,'AMBIENCE',1),
(3.9,'FOOD',2),
(5.1,'SERVICE',2),
(3.5,'PRICES',2),
(4.9,'INTERIOR',2),
(6.9,'AMBIENCE',2),
(7,'FOOD',3),
(8.5,'SERVICE',3),
(5.4,'PRICES',3),
(6.2,'INTERIOR',3),
(5.4,'AMBIENCE',3),
(7.6,'FOOD',4),
(6.1,'SERVICE',4),
(4.3,'PRICES',4),
(7.8,'INTERIOR',4),
(4.6,'AMBIENCE',4),
(9.6,'FOOD',5),
(9,'SERVICE',5),
(7.4,'PRICES',5),
(8.7,'INTERIOR',5),
(8.3,'AMBIENCE',5),
(0,'FOOD',6),
(0,'SERVICE',6),
(0,'PRICES',6),
(0,'INTERIOR',6),
(10,'AMBIENCE',6),
(2,'FOOD',7),
(9.1,'SERVICE',7),
(8.8,'PRICES',7),
(9,'INTERIOR',7),
(8.9,'AMBIENCE',7),
(9.1,'FOOD',8),
(9.1,'SERVICE',8),
(7.3,'PRICES',8),
(9,'INTERIOR',8),
(8.1,'AMBIENCE',8),
(8.2,'FOOD',9),
(8.1,'SERVICE',9),
(7.5,'PRICES',9),
(5.7,'INTERIOR',9),
(8.4,'AMBIENCE',9),
(4.9,'FOOD',10),
(5.2,'SERVICE',10),
(0,'PRICES',10),
(8.2,'INTERIOR',10),
(3.3,'AMBIENCE',10),
(6.6,'FOOD',11),
(5.1,'SERVICE',11),
(5.3,'PRICES',11),
(5.8,'INTERIOR',11),
(6.4,'AMBIENCE',11),
(9.4,'FOOD',12),
(1.6,'SERVICE',12),
(2.4,'PRICES',12),
(2.3,'INTERIOR',12),
(1.7,'AMBIENCE',12),
(8.7,'FOOD',13),
(8.4,'SERVICE',13),
(7.1,'PRICES',13),
(7.8,'INTERIOR',13),
(6.8,'AMBIENCE',13),
(5.3,'FOOD',14),
(5.3,'SERVICE',14),
(3.9,'PRICES',14),
(7.4,'INTERIOR',14),
(6.2,'AMBIENCE',14),
(6.1,'FOOD',15),
(5.6,'SERVICE',15),
(6.3,'PRICES',15),
(4,'INTERIOR',15),
(5.3,'AMBIENCE',15),
(10,'FOOD',16),
(6,'SERVICE',16),
(6.8,'PRICES',16),
(9.7,'INTERIOR',16),
(5,'AMBIENCE',16),
(9.1,'FOOD',17),
(5.1,'SERVICE',17),
(8.2,'PRICES',17),
(10,'INTERIOR',17),
(8.2,'AMBIENCE',17),
(8.8,'FOOD',18),
(8.3,'SERVICE',18),
(7.7,'PRICES',18),
(7.8,'INTERIOR',18),
(6.5,'AMBIENCE',18),
(10,'FOOD',19),
(10,'SERVICE',19),
(10,'PRICES',19),
(10,'INTERIOR',19),
(10,'AMBIENCE',19),
(9.4,'FOOD',20),
(10,'SERVICE',20),
(6.3,'PRICES',20),
(7.8,'INTERIOR',20),
(9.3,'AMBIENCE',20),
(6.9,'FOOD',21),
(6.2,'SERVICE',21),
(4.2,'PRICES',21),
(4.8,'INTERIOR',21),
(9,'AMBIENCE',21),
(0,'FOOD',22),
(0,'SERVICE',22),
(0,'PRICES',22),
(0,'INTERIOR',22),
(0,'AMBIENCE',22),
(9.3,'FOOD',23),
(7.9,'SERVICE',23),
(6.6,'PRICES',23),
(6.9,'INTERIOR',23),
(6.4,'AMBIENCE',23),
(4.4,'FOOD',24),
(4.9,'SERVICE',24),
(7.1,'PRICES',24),
(3.4,'INTERIOR',24),
(3.5,'AMBIENCE',24);
Ответы:
SELECT chains.name, ROUND(AVG(rate), 2) avg_rate FROM Establishment JOIN Review ON Review.establishment_id = Establishment.id JOIN Rate ON Rate.review_id = Review.id JOIN Establishment chains ON chains.id = COALESCE(Establishment.chain_id, Establishment.id) GROUP BY chains.name ORDER BY avg_rate DESC ; |
SELECT chains.name, ROUND(AVG(rate), 2) avg_rate FROM Establishment JOIN Review ON Review.establishment_id = Establishment.id JOIN Rate ON Rate.review_id = Review.id JOIN Establishment chains ON chains.id = COALESCE(Establishment.chain_id, Establishment.id) GROUP BY chains.name ORDER BY avg_rate DESC ;
SQL fiddle live
- спасибо большое. реально для меня это трудный запрос так как я еще новичок в sql, но буду учиться.
- evgeniy1982, sqlize.online Вам в помощь! И да пребудет с Вами ACID
- Slava Rozhnev, Спасибо
- Slava Rozhnev,
Здравствуйте, извините что снова к вам обращаюсь за помощью. Вы недавно помогали мне с sql задачей но там немного не корректный запрос, так как мне тогда не полностью задание объяснили.
Вот как должен быть выполнен запрос
Здесь показано как вручную выполнен запрос. Если вас не затруднит то помогите мне пожалуйста, так как sql для меня дается к сожалению с трудом.
- evgeniy1982, Пожалуйста опубликуйте новый вопрос, добавьте к вопросу ссылку на данные
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для того чтобы сделать выборку данных из нескольких таблиц в одном запросе SQL, можно воспользоваться оператором JOIN. JOIN позволяет объединять данные из разных таблиц на основе определенного условия.
Прежде всего, необходимо определить, какие таблицы вы хотите объединить и какие столбцы вы хотите выбрать из каждой таблицы. После этого нужно указать условие объединения таблиц с помощью оператора ON.
Например, предположим у вас есть две таблицы - users и orders. Вы хотите выбрать данные о пользователях и их заказах. Вот как может выглядеть запрос:
SELECT users.name, orders.product FROM users JOIN orders ON users.id = orders.user_id;
В данном примере мы выбираем имя пользователя из таблицы users и продукт из таблицы orders. Мы объединяем эти две таблицы по полю user_id из таблицы orders и id из таблицы users.
Если у вас есть больше таблиц для объединения, вы можете использовать дополнительные операторы JOIN или LEFT JOIN, в зависимости от того, нужно ли вам включать все записи из одной таблицы или нет.
Например, если у вас есть третья таблица products и вы хотите включить данные из нее в запрос, вы можете сделать следующее:
SELECT users.name, orders.product, products.price FROM users JOIN orders ON users.id = orders.user_id JOIN products ON orders.product_id = products.id;
Таким образом, используя оператор JOIN в SQL, вы можете делать выборки данных из нескольких таблиц в одном запросе и объединять их в нужном формате для вашего приложения или отчета.