mysql - 如何使用单个查询从两个表中检索数据？

Question

我需要一份事件列表，显示每个性别的 child 注册人数。

结构:

CREATE TABLE Child(
child_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
pc_id INTEGER NOT NULL,
child_fname VARCHAR(20) NOT NULL,
child_lname VARCHAR(20) NOT NULL,
child_dob DATE NOT NULL,
child_gender ENUM ('F','M') DEFAULT 'F' NOT NULL,
CONSTRAINT FOREIGN KEY(pc_id) REFERENCES Parent_Carer(pc_id))
ENGINE=InnoDB; 

CREATE TABLE Child_Activity(
child_id INTEGER NOT NULL,
activity_name_id ENUM('Art','Football','IT') DEFAULT 'IT'  NOT NULL,
activity_day ENUM('Tuesday','Wednesday','Thursday') NOT NULL,
CONSTRAINT PRIMARY KEY(child_id,activity_name_id),
CONSTRAINT FOREIGN KEY(child_id)references Child(child_id),
CONSTRAINT FOREIGN KEY(activity_name_id)references Activity(activity_name_id) )
ENGINE=InnoDB;

我有这个问题

SELECT activity_name_id,
       COUNT(*)     AS 'Number of Children',
       child_gender AS 'Childs Gender'
FROM   child c,
       child_activity ca
WHERE  c.child_id = ca.child_id
GROUP  BY child_gender,
          activity_name_id;  

谢谢。

Answer 1

Select Child_Activity.activity_name_id, count(child_m.id) as boys,   count(child_f.id) as girls,   
FROM Child_Activity
INNER JOIN Child as child_m
ON (child_m.child_id = Child_Activity.child_id AND child_m.gender = 'M' )
INNER JOIN  Child child_f
ON (child_f.child_id = Child_Activity.child_id AND child_f.gender = 'F' )

我希望这能奏效