gpt4 book ai didi

mysql - 一个字段与 JOINED 行数的 SUM 计数

转载 作者:行者123 更新时间:2023-11-29 13:31:43 25 4
gpt4 key购买 nike

我有以下三个表( observationsaspect_labelsaspects ):

观察表

结构

Observations table

数据

Observations data

方面标签表

结构

aspect_labels table

数据

aspect_labels data

方面表

结构

aspects table

数据

aspects data

这三个表背后的想法是每个 observation有一个Main_DevelopmentMain_Positive而且还有无数个额外的Positive方面和Development方面。

例如,如果我现在输入一个观察值(到 observations 表中),则 Main_Positive可能是Student Enthusiasm ( aspect_label ID 1) 和 Main_Development可能是Student Engagement (aspect_label ID 2)。不过我也可以添加一个标准 Positive Expectations的方面( aspect_label ID 3) 进入 aspects表。

我正在尝试做的事情,按 Teacher_ID 分组,显示每个工作人员拥有每个 aspect 的次数列在他们的观察中。

所以,我想看看 D Wraight已有 2 Student Engagement , 0 Expectations , 1 Student Enthusiasm等等

此查询对 Main_Positive 执行的操作完全相同。从observations方面表,但我不知道如何添加 aspects 中的行表到查询中。

SELECT
CONCAT(sta.Firstname, " ", sta.Surname) AS `Member of Staff`,
SUM(IF(o.`Main_Positive` = 1, 1, 0)) AS `Student enthusiasm`,
SUM(IF(o.`Main_Positive` = 2, 1, 0)) AS `Student engagement`,
SUM(IF(o.`Main_Positive` = 3, 1, 0)) AS `Expectations`,
SUM(IF(o.`Main_Positive` = 4, 1, 0)) AS `Safe and supportive environment`,
SUM(IF(o.`Main_Positive` = 5, 1, 0)) AS `Attitude and values of teacher`,
SUM(IF(o.`Main_Positive` = 6, 1, 0)) AS `Objectives to stretch all students`,
<< MORE GOES HERE >>
FROM frog_observations.observations o
LEFT JOIN frog_shared.staff sta ON o.Teacher_ID = sta.ID
GROUP BY o.Teacher_ID
ORDER BY sta.Surname ASC, sta.Firstname ASC

查询产生以下输出(当 PHPMA 导出为 CSV 时):

Output

如何修改查询以包含 aspects 中的连接行表?

编辑:SQL fiddle :http://sqlfiddle.com/#!2/75cf3/1/0

最佳答案

您实际上不能这样做,因为它会导致列数可变(除非您想在每次添加另一个方面时更改 SQL)。

你可以这样做:-

SELECT CONCAT(s.Firstname, ' ', Surname), al.Title, IFNULL(SUM(AspectCnt), 0)
FROM staff s
CROSS JOIN aspect_labels al
LEFT OUTER JOIN
(
SELECT o.Teacher_ID, o.Main_Positive AS AspectId, COUNT(*) AS AspectCnt
FROM observations o
GROUP BY o.Teacher_ID, o.Main_Positive
UNION ALL
SELECT o.Teacher_ID, o.Main_Development AS AspectId, COUNT(*) AS AspectCnt
FROM observations o
GROUP BY o.Teacher_ID, o.Main_Development
UNION ALL
SELECT o.Teacher_ID, a.Aspect_ID AS AspectId, COUNT(*) AS AspectCnt
FROM observations o
INNER JOIN aspects a
ON o.ID = a.Observation_ID
GROUP BY o.Teacher_ID, a.Aspect_ID
) Sub1
ON s.ID = Sub1.Teacher_ID
AND al.ID = Sub1.AspectId
GROUP BY CONCAT(s.Firstname, ' ', Surname), al.Title
ORDER BY CONCAT(s.Firstname, ' ', Surname), al.Title

这为您提供了每个方面每个员工一行的信息,然后在代码中循环以整理您的列。

编辑 - 使用固定列:-

SELECT CONCAT(s.Firstname, ' ', Surname), 
IFNULL(SUM(IF(Sub1.AspectId = 1, 1, 0)), 0) AS `Student enthusiasm`,
IFNULL(SUM(IF(Sub1.AspectId = 2, 1, 0)), 0) AS `Student engagement`,
IFNULL(SUM(IF(Sub1.AspectId = 3, 1, 0)), 0) AS `Expectations`,
IFNULL(SUM(IF(Sub1.AspectId = 4, 1, 0)), 0) AS `Safe and supportive environment`,
IFNULL(SUM(IF(Sub1.AspectId = 5, 1, 0)), 0) AS `Attitude and values of teacher`,
IFNULL(SUM(IF(Sub1.AspectId = 6, 1, 0)), 0) AS `Objectives to stretch all students`
FROM staff s
LEFT OUTER JOIN
(
SELECT o.Teacher_ID, o.Main_Positive AS AspectId
FROM observations o
UNION ALL
SELECT o.Teacher_ID, o.Main_Development AS AspectId
FROM observations o
UNION ALL
SELECT o.Teacher_ID, a.Aspect_ID AS AspectId
FROM observations o
INNER JOIN aspects a
ON o.ID = a.Observation_ID
) Sub1
ON s.ID = Sub1.Teacher_ID
GROUP BY CONCAT(s.Firstname, ' ', Surname)
ORDER BY CONCAT(s.Firstname, ' ', Surname)

将其拆分一点以使用教师 ID 进行分组来获取所有计数,然后将结果连接回教师表以获取名称可能会更快。将避免在连接字段上进行分组。

关于mysql - 一个字段与 JOINED 行数的 SUM 计数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19363310/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com