具有多个计数和分组依据的 MySql 过程

Question

我一直在为一家慈善机构创建一款应用程序，允许家庭在节日期间注册接收礼物。数据库组织有多个表。下面列出了这些表(及其架构/创建语句):

CREATE TABLE IF NOT EXISTS ValidDates(
  chosenYear YEAR PRIMARY KEY, 
  startDate DATE NOT NULL,
  endDate DATE NOT NULL,
  maxReservationsPerDay INTEGER default 50, 
  CONSTRAINT chkDates CHECK(startDate < endDate)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Families(
  fID INTEGER PRIMARY KEY AUTO_INCREMENT,
  signature BLOB DEFAULT NULL,
  reservationDate DATE NOT NULL,
  signatureTS TIMESTAMP,
  createdTS TIMESTAMP NOT NULL,
  createdBy INTEGER,
  FOREIGN KEY (createdBy) REFERENCES Employees(eID) ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS FamilyMembers(
  fmID INTEGER PRIMARY KEY AUTO_INCREMENT,
  fName VARCHAR(255) NOT NULL,
  lName VARCHAR(255) NOT NULL,
  age INTEGER NOT NULL,
  sex BIT(1) NOT NULL, 
  ssNum VARCHAR(9) UNIQUE NOT NULL,
  recievedBig BIT(1) DEFAULT 0,
  recievedNormal BIT(1) DEFAULT 0,
  recievedStocking BIT(1) DEFAULT 0,
  famID INTEGER,
  INDEX fIndex (famID), 
  FOREIGN KEY (famID) REFERENCES Families(fID)  ON DELETE CASCADE 
) ENGINE=INNODB;

我想编写一个查询(存储过程)，该查询将遍历每个日期，选择日期、该日期服务的家庭数量以及家庭成员总数于该日期送达。例如，应返回2018年12月12日，共有5个家庭进行了预订，其中家庭成员总数为13人(id为1的家庭有5人，id为2的家庭有1人，家庭 ID 3 有 1 名成员，家庭 ID 4 有 4 名成员，家庭 5 有 2 名成员)。它应该对所有日期执行此操作。谢谢您的帮助。

注意:这些值将被放入 C# 中的 Dictionary< DateTime, tuple< int, int >> 中(其中键是日期，第一个 int 是家庭数量，第二个 int 是家庭数量)这些家庭的成员)并用于报告功能。

Answer 1

这将返回预期结果(不使用 ValidDates 表):

select reservationDate
  ,count(distinct fmID) as #_of_families
  ,count(*) as #_of_familiy_members
from Families as f
join FamilyMembers as fm
  on f.fID = fm.famID
group by reservationDate