php - 使用 group by 正确连接 4 个表

Question

我是使用高级 SQL 查询的新手，但在一个查询上遇到了困难。

我用 php 创建了预订系统，它使用 4 个表:

• site_days
• site_timeslots
• site_bookings
• site_teams

• 每个 site_team 都与 site_booking 相关
• 每个 site_booking 都与 site_timeslot 相关
• 每个 site_timeslot 都与 site_days 相关

可以有多个与一个 site_day 相关的 site_timeslots可以有多个与一个 site_timeslot 相关的 site_bookings可以有多个 site_team 与一个 site_bookings 相关

您可以使用此sql创建测试表:

-- Adminer 3.6.3 MySQL dump

SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = 'SYSTEM';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `site_bookings`;
CREATE TABLE `site_bookings` (
  `id` int(11) NOT NULL auto_increment,
  `timeslot_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `site_bookings` (`id`, `timeslot_id`) VALUES
(1, 6443);

DROP TABLE IF EXISTS `site_days`;
CREATE TABLE `site_days` (
  `id` int(11) NOT NULL auto_increment,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `site_days` (`id`, `date`) VALUES
(85,    '2013-04-01'),
(92,    '2013-04-02');

DROP TABLE IF EXISTS `site_teams`;
CREATE TABLE `site_teams` (
  `id` int(11) NOT NULL auto_increment,
  `booking_id` int(11) NOT NULL,
  `name` varchar(100) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `site_teams` (`id`, `booking_id`, `name`) VALUES
(1, 1,  'Avengers'),
(2, 1,  'Big Five');

DROP TABLE IF EXISTS `site_timeslots`;
CREATE TABLE `site_timeslots` (
  `id` int(11) NOT NULL auto_increment,
  `day_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `starts` time NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7152 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `site_timeslots` (`id`, `day_id`, `date`, `starts`) VALUES
(6443,  85, '2013-04-01',   '08:00:00'),
(6444,  85, '2013-04-01',   '08:10:00'),
(7098,  92, '2013-04-02',   '08:00:00'),
(7099,  92, '2013-04-02',   '08:10:00');

因此，我想获取表 site_timeslots 的所有时间段，并添加一些附加信息:- 对于每个 site_timeslot，我想知道该时间段的所有相关预订中 site_team 的总数(例如，如果该 site_timeslot 有 2 个 site_booking，每个 site_team 有 2 个 site_team，则总计数应为 4)以及相关预订的计数.

我尝试过这个sql:

SELECT `site_teams`.`id` AS site_teams_id, `site_teams`.`name` AS site_teams_name, `site_teams`.`booking_id` AS site_teams_booking_id, `site_days`.`id` AS site_days_id, `site_days`.`date` AS site_days_date, `site_timeslots`.`id` AS site_timeslots_id, `site_timeslots`.`starts` AS site_timeslots_starts, `site_bookings`.`id` AS site_bookings_id, `site_bookings`.`timeslot_id` AS site_bookings_timeslot_id
FROM (`site_days`)
LEFT JOIN `site_timeslots` ON `site_timeslots`.`day_id` = `site_days`.`id`
LEFT JOIN `site_bookings` ON `site_bookings`.`timeslot_id` = `site_timeslots`.`id`
LEFT JOIN `site_teams` ON `site_teams`.`booking_id` = `site_bookings`.`id`
GROUP BY `site_teams`.`booking_id`

-> 但我不会得到没有任何 site_bookings 的时间段，请问我应该如何更改此 sql 查询以获得结果:

1. 每行site_timeslot
2. 新列“count_of_site_bookings”中与该 site_timeslot 相关的 site_booking 计数
3. 与新列“count_of_site_teams”中的该 site_timeslot 相关的所有 site_booking 相关的 site_team 计数

Answer 1

您可以通过从 site_timeslots 开始 LEFT JOINing 来实现此目的，然后在 2 个相关字段上使用 COUNT 来获取您想要的总数

SELECT

  sti.*,
  COUNT(DISTINCT sb.id) AS count_of_site_bookings,
  COUNT(DISTINCT ste.id) AS count_of_site_teams

FROM site_timeslots sti

INNER JOIN site_days sd
  ON sd.id = sti.day_id


LEFT JOIN site_bookings sb
ON sb.timeslot_id = sti.id

LEFT JOIN site_teams ste
ON ste.booking_id = sb.id

GROUP BY sti.id

您可以在 SQL Fiddle http://sqlfiddle.com/#!2/1a253/2 上找到此内容

我还做了一个以前的版本，由于我的假设不正确，该版本使用了子查询，如果您想看一下以供引用，也可以在 http://sqlfiddle.com/#!2/9ccf2/10 上找到它。