我想改进现有的(php/mysql)代码库,提供合唱团出席人数的概述(X轴:日期,Y轴:学生)。数据分布在不同的表中(您将看到一个非常完善的命名逻辑)。该查询相当繁重,需要对每个"唱诗班"重复查询。所以我试图构建一个视图来优化后端代码。
涉及的表:
- chorale_dates =本季排练日期
- chorale_inscriptions =合唱团的注册
- 唱诗班_presence =记录的缺勤(唱诗班出席)
- chorale_id =编舞者的个人信息(邮箱,姓名,…)
┌───────────────────────┐ ┌──────────────────────────────────┐ ┌───────────────────────┐
│ chorale_dates │ │chorale_presences │ │ chorale_inscription │
├───────────────────────┤ ├──────────────────────────────────┤ ├───────────────────────┤
│ │ │ │ │ │
│ chorale_date_id │ │chorale_presences_id ┌─┼──┤►chorale_inscription_id│
│ ▲ │ │ │ │ │ │
│ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │
│ └────────┼──────┤chorale_presences_date_id │ │ │ chorale_id_id │
│ │ │ │ │ │ │ │
│ │ │chorale_presences_inscription_id│ │ │ │ │
│ │ │ │ │ │ │
│ │ │ │ │ │ │
└───────────────────────┘ └──────────────────────────────────┘ └─┼─────────────────────┘
│
│ ┌───────────────────────┐
│ │ chorale_id │
│ ├───────────────────────┤
│ │ │
└────────────► chorale_id_id │
│ │
│ │
│ │
│ │
│ │
│ │
│ │
└───────────────────────┘
下面是生成视图的语句:
CREATE VIEW `attendances` AS SELECT
`d`.`chorale_date_id` AS `date_id`,
`d`.`chorale_date` AS `date`,
`d`.`chorale_date_saison` AS `season_id`,
`d`.`chorale_date_type` AS `date_type`,
`d`.`chorale_date_chorale_id` AS `chorale_id`,
`d`.`chorale_date_etat` AS `date_etat`,
`p`.`chorale_presences_id` AS `absence_id`,
`p`.`chorale_presences_date_id` AS `chorale_presences_date_id`,
`p`.`chorale_presences_inscription_id` AS `chorale_presences_inscription_id`,
`i`.`chorale_inscription_id` AS `chorale_inscription_id`,
`p`.`chorale_presences_etat` AS `attendance`,
`i`.`chorale_inscription_pupitre` AS `chorale_inscription_pupitre`,
`p`.`chorale_presences_validation` AS `chorale_presences_validation`,
CONCAT( `c`.`chorale_id_nom`, ', ', `c`.`chorale_id_prenom`) AS `fullname`
FROM
(
(
(
`chorale_dates` `d`
LEFT JOIN `chorales_presences` `p` ON
(
(
`p`.`chorale_presences_date_id` = `d`.`chorale_date_id`
)
)
)
LEFT JOIN `chorale_inscription` `i` ON
(
(
`p`.`chorale_presences_inscription_id` = `i`.`chorale_inscription_id`
)
)
)
LEFT JOIN `chorale_id` `c` ON
(
(
`c`.`chorale_id_id` = `i`.`chorale_id_id`
)
)
)
ORDER BY d.season_id ASC, `d`.`chorale_date` ASC ;
因为它是一个LEFT JOIN,所以我希望所有注册合唱团的所有季节日期,以及空的"出席率";如果在chorale_presence表中没有记录缺席,则返回列值;如果在该表中有匹配的记录,则返回列值。无论chorale_presence中是否有条目,所有合唱团都应该有37个条目。
然而,这个视图返回的内容只包含记录了日期和缺席的行。因此,根据唱诗班的不同,我得到了不同数量的条目。
我做错了什么?
我希望所有注册合唱团的所有季节日期
这是chorale_dates
(存储日期)和chorale_inscriptio
(存储编者注册)之间的CROSS
连接的定义:
CREATE VIEW attendances AS
SELECT
d.chorale_date_id AS date_id,
d.chorale_date AS date,
d.chorale_date_saison AS season_id,
d.chorale_date_type AS date_type,
d.chorale_date_chorale_id AS chorale_id,
d.chorale_date_etat AS date_etat,
p.chorale_presences_id AS absence_id,
p.chorale_presences_date_id AS chorale_presences_date_id,
p.chorale_presences_inscription_id AS chorale_presences_inscription_id,
i.chorale_inscription_id AS chorale_inscription_id,
p.chorale_presences_etat AS attendance,
i.chorale_inscription_pupitre AS chorale_inscription_pupitre,
p.chorale_presences_validation AS chorale_presences_validation,
CONCAT(c.chorale_id_nom, ', ', c.chorale_id_prenom) AS fullname
FROM chorale_dates d CROSS JOIN chorale_inscription i
LEFT JOIN chorales_presences p ON p.chorale_presences_date_id = d.chorale_date_id AND p.chorale_presences_inscription_id = i.chorale_inscription_id
LEFT JOIN chorale_id c ON c.chorale_id_id = i.chorale_id_id
ORDER BY d.season_id ASC, d.chorale_date ASC;