我写了一个查询,我在这个问题的底部解释。它正确地得到了人们提出的一些问题的总分。因此,结果集看起来像这样:
| David | Bill | Mary | James
Question 1 | 10 | 10 | 0 | 0
Question 2 | 10 | 20 | 0 | 0
Question 3 | 10 | 30 | 0 | 0
Question 4 | 0 | 20 | 0 | 1
我需要做的,但不能弄清楚的是,如何从结果集中删除所有问题都为零分的人。因此,'Mary'将从上面的结果中删除,留下:
| David | Bill | James
Question 1 | 10 | 10 | 0
Question 2 | 10 | 20 | 0
Question 3 | 10 | 30 | 0
Question 4 | 0 | 20 | 1
下面是需要进一步开发的查询:
SELECT
`questions`,
SUM(`0`) AS `David`,
SUM(`1`) AS `Bill`
FROM(
(SELECT
ROUND(((SUM(`sm`.`ScorecardMark`) * `sc`.`ScoreCriteriaWeight`)/(COUNT(`sm`.`ScorecardMark`) * `sc`.`ScoreCriteriaWeight`))*100) AS `0`,
0 AS `1`
FROM
`tables`
WHERE
`clauses`
GROUP BY
`questions`)
UNION
(SELECT
0 AS `0`,
ROUND(((SUM(`sm`.`ScorecardMark`) * `sc`.`ScoreCriteriaWeight`)/(COUNT(`sm`.`ScorecardMark`) * `sc`.`ScoreCriteriaWeight`))*100) AS `1`
FROM
`tables`
WHERE
`clauses`
GROUP BY
`questions`)
) AS `tbltotals`
GROUP BY
`questions`
我认为更好的方法是选择问题作为列,因为获得所需问题的列表应该比获得得分高于0的人的列表更容易。
那么您可以对人员应用正常的行级过滤。如果需要的话,还可以对结果进行转置,以在应用程序代码中切换行和列。