mysql - 在一个查询中从两个表中选择多个 COUNT 的子查询

Question

我正在努力解决我写过的最复杂的 SQL 查询!

数据库结构:

frog_shared.staff
`ID`, `Firstname`, `Surname`

frog_observations.observations
`ID`, `Teacher_ID`, `Type`, `Main_Positive_Aspect`, `Main_Development_Aspect`, `Grade_for_Behaviour`, `Grade_for_Attainment`, `Grade_for_Teaching`

frog_observations.aspects
`ID`, `Observation_ID`, `Label_ID`, `Type`

frog_observations.aspect_labels
`ID`, `Title`

示例数据:

Member of staff:
`12345`, `Duncan`, `Wraight`

Observation:
`9888`, `12345`, `Formal`, `5`, `7`, `1`, `1`, `1`

Aspects:
`101`, `9888`, `2`, `P`

Aspect labels:
`2`, `Questioning`

我想要实现的目标:

我想列出一份在特定观察方面最好的老师的名单。例如，我想查看“提问”方面排名前 5 的老师。

最重要的是，我想对数据进行一些过滤:

• 应计算正式 (Type=Formal) 和共享最佳实践 (Type=SBP) 观察结果的方面
• 仅当该观察的总体评分最多为 2 时(不能更高，即不能有两个 2 分和一个 3 分)，才应计算正式观察的方面，但共享最佳实践观察结果的分数均为 0成绩
• 观察表中记录的主要积极方面应在计数中加权
• 对于正式观察，主要积极方面的权重应为正常方面的 3 倍
• 为了分享最佳实践观察结果，主要积极方面的权重应为正常方面的 2 倍

我的尝试:

此声明按等级过滤观察结果，但不包含任何主要的积极方面(它们未包含在原始系统中)

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT( * ) AS Total,
  GREATEST(o.`Achievement_Grade`, o.`Behaviour_Grade`, o.`Teaching_Grade`) AS `Worst Grade`
FROM frog_observations.observations o
  INNER JOIN frog_shared.staff s ON o.Teacher_ID = s.ID
  INNER JOIN frog_observations.aspects a ON o.ID = a.Observation_ID
WHERE a.Aspect_ID =  4
  AND ( GREATEST(o.`Achievement_Grade`, o.`Behaviour_Grade`, o.`Teaching_Grade`) BETWEEN 1 AND 2 ) AND o.Datetime > '2011-09-01'
  AND a.Type =  'P'
GROUP BY s.ID
ORDER BY `Total` DESC 
LIMIT 5

现在我想要的方式是这样的:

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT(mp.*) AS `Number of Appraisal Main Positives`,
  COUNT(sp.*) AS `Number of SBP Main Positives`,
  COUNT(a.*) AS `Number of Other Positives`,
  SUM(`Number of Appraisal Main Positives` + `Number of SBP Main Positives` `Number of Other Positives`) AS `Total`
FROM
  `frog_observations`.`observations` o,
  ( `frog_observations`.`observations` WHERE `Type` = 'Formal') AS mp,
  ( `frog_observations`.`observations` WHERE `Type` = 'SBP') AS sp
INNER JOIN
  `frog_observations`.`aspects` a ON a.Observation_ID = o.ID
GROUP BY s.ID

不幸的是，除了知道我想要的标题(即评估主要积极因素的数量、分享最佳实践积极因素的数量、标准积极因素的数量 code> 和加权总计)，我不知道如何编写我认为的子查询来从单个语句中获取所有这些信息。

感谢任何指导。

<小时/>

编辑:示例输入/输出

输入用户希望查看提问方面排名前 5 位的员工

流程 - 方面提问 在aspect_labels 表中具有ID 4。 - 然后，系统应该为每位员工计算他们在将 Main_Positive_Aspect 设置为 4 时的观察。其中一些观察将是类型正式的，一些将是类型 SBP。 - 然后，系统还应计算 aspects 表中 Aspect_ID 为 4 的每个员工的行数。

输出

关键:

• FMP = observations 表中 Type 为 Formal 的行数； GREATEST(Achievement_Grade、Behaviour_Grade、Teaching_Grade)介于 1 和 2 之间；并且 Main_Positive_Aspect 为 4(即 aspects_label 表中的 Questioning)
• SMP = observations 表中的行数，其中 Type 为 SBP，Main_Positive_Aspect 为 4
• 其他 = aspects 表中每位员工的 Aspect_ID 为 4 的行数 (通过 o.Observation_ID -> o.Staff_ID 链接)
• 分数 = 每一项的加权总和 - 每个 FMP 值 3 分，每个 SMP 值 2 分，每个其他值 1 分。然后应对查询进行排序，DESC，通过本专栏。

示例输出:

-------------------------------------------------------------
|       Staff Name      FMP     SMP     Other       Points  |
|------------------------------------------------------------
|       D Wraight       2       1       4           12      |
|       A Nother        3       0       0           9       |
|       J Bloggs        0       4       1           9       |
|       J Arthur        1       1       1           6       |
|       M Turner        0       1       0           2       |
-------------------------------------------------------------

<小时/>

以下是单独编写的查询，适用于我的数据库。基本上我需要将这些合并到一个查询中。如果可以的话!

#=======#
#  FMP  #
#=======#

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT( * ) AS `FMP`
FROM frog_observations.observations o
  INNER JOIN frog_shared.staff s ON o.Teacher_ID = s.ID
WHERE o.Main_Positive  =  4
  AND o.Type = 'F'
  AND ( GREATEST(o.`Achievement_Grade`, o.`Behaviour_Grade`, o.`Teaching_Grade`) BETWEEN 1 AND 2 )
GROUP BY s.ID

#=======#
#  SMP  #
#=======#

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT( * ) AS `SMP`
FROM frog_observations.observations o
  INNER JOIN frog_shared.staff s ON o.Teacher_ID = s.ID
WHERE o.Main_Positive  =  4
  AND o.Type = 'S'
GROUP BY s.ID

#=========#
#  Other  #
#=========#

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT( * ) AS `Other`
FROM observations o
  INNER JOIN frog_shared.staff s ON o.Teacher_ID = s.ID
  INNER JOIN aspects a ON o.ID = a.Observation_ID
WHERE a.Aspect_ID = 4
  AND a.Type = 'P'
GROUP BY s.ID

Answer 1

这样的东西适合初始剪辑吗...

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS Teacher,
  sum(Case when `Type` = 'Formal' then 1 else 0 end) AS `Number of Appraisal Main Positives`,
  sum(Case when `Type` = 'SBP' then 1 else 0 end)  AS `Number of SBP Main Positives`,
  coalesce(OtherPositives,0) AS `Number of Other Positives`,
  sum(Case when `Type` in ( 'Formal' ,'SBP') then 1 else 0 end) + coalesce(OtherPositives,0)  AS `Total`
FROM observations o
INNER JOIN staff s ON o.Teacher_ID = s.ID
LEFT JOIN (select Observation_ID, count(*) as OtherPositives) from aspects where Label_ID=4 group by Observation_ID) a ON a.Observation_ID = o.ID
WHERE Main_Positive =4
GROUP BY s.ID