mysql - 选择查询以对表1中的平均字段求和，并对表中的计数字段求和

表：分数

Judge   No      Name    Casual  Barong  Talent  Swimsuit    Formal
Judge1  Ginoo 1 John    85      85      85      85          85
Judge2  Ginoo 1 John    84      86      88      82          83
Judge3  Ginoo 1 John    90      86      84      87          87
Judge1  Ginoo 2 David   85      85      85      85          85
Judge2  Ginoo 2 David   89      81      83      84          85
Judge3  Ginoo 2 David   87      84      83      87          88

表：textvote

No      Sender
Ginoo 1 9307895654
Ginoo 1 9566551234
Ginoo 1 9232235643
Ginoo 2 9225557878

查询结果

Rank     No      Name  Casual Barong Talent Swimsuit Formal Textvote Total
Champion Ginoo 1 John  86.33  85.67  85.67  84.67    85.00  93.75   86.73
1stRunup Ginoo 2 David 87.00  83.33  83.67  85.33    86.00  81.25   84.32
2ndRunup

这是我的最新代码：

"SELECT s.no, s.name, AVG(s.casual) AS Casual, AVG(s.barong) AS Barong, AVG(s.swimsuit) AS Swimsuit, AVG(s.formal) AS Formal, " & _
                "(select count(*) / (select count(*) from (textvote) where no like '%Ginoo%') * (100 / count(*)) + (100 - (100 / count(*))) AS 'Text Vote', " & _
                "(AVG(s.casual) * 0.15) + (AVG(s.barong) * 0.25) + (AVG(s.swimsuit) * 0.15) + (AVG(s.formal) * 0.15) + " & _
                "(select count(*) / (select count(*) from (textvote) where no like '%Ginoo%') * (100 / count(*)) + (100 - (100 / count(*))) * 0.15 AS Total  " & _
                "FROM scores s " & _
                "INNER JOIN textvote t ON s.no = t.no " & _
                "WHERE t.no LIKE '%Ginoo%' " & _
                "GROUP BY t.no"

我收到此错误：


  您的SQL语法有误。检查与您的MariaDB服务器版本相对应的手册，以找到在第1行附近的“”附近使用的正确语法

最佳答案

直到我知道如何计算textvote，我都将textvotecalc留空（填写您自己）：

SELECT s.candidate, AVG(s.gown) AS "gown 30%",
AVG(s.talent) AS "talent 30%",
?textvotecalc? AS "textvote 40% ",
((AVG(s.gown)*100)/30) + ((AVG(s.talent)*100)/30)  + ((?textvotecalc?*100)/40) AS TOTAL
FROM scores s
INNER JOIN textvote t ON s.candidate = t.candidate
GROUP BY s.candidate

编辑：

在)计算的末尾有一个额外的TOTAL。

SELECT s.no, s.name, AVG(s.casual) AS Casual, AVG(s.barong) AS Barong, AVG(s.swimsuit) AS Swimsuit, AVG(s.formal) AS Formal,
(COUNT(t.no) / " & TextVoteCountGinoo & ") * (" & TextVoteCountGinoo & " - COUNT(t.no)) + (" & TextVoteCountGinoo & " - (" & TextVoteCountGinoo & " - COUNT(t.no))) AS'Text Vote',
(AVG(s.casual) * 0.15) + (AVG(s.barong) * 0.25) + (AVG(s.swimsuit) * 0.15) + (AVG(s.formal) * 0.15) + (COUNT(t.no) / " & TextVoteCountGinoo & ") * (100 / " & TextVoteCountGinoo & ") + (100 - (100 / " & TextVoteCountGinoo & ") ) * 0.15 AS Total
FROM scores s
INNER JOIN textvote t ON s.no = t.no
WHERE s.no LIKE '%Ginoo%'
GROUP BY s.no

以您的VB格式：

SELECT s.no, s.name, AVG(s.casual) AS Casual, AVG(s.barong) AS Barong, AVG(s.swimsuit) AS Swimsuit, AVG(s.formal) AS Formal, & _
"(COUNT(t.no) / " & TextVoteCountGinoo & ") * (" & TextVoteCountGinoo & " - COUNT(t.no)) + (" & TextVoteCountGinoo & " - (" & TextVoteCountGinoo & " - COUNT(t.no))) AS'Text Vote'," & _
"(AVG(s.casual) * 0.15) + (AVG(s.barong) * 0.25) + (AVG(s.swimsuit) * 0.15) + (AVG(s.formal) * 0.15) +" & _ "(COUNT(t.no) / " & TextVoteCountGinoo & ") * (100 / " & TextVoteCountGinoo & ") + (100 - (100 / " & TextVoteCountGinoo & ") ) * 0.15 AS Total" & _
"FROM scores s
INNER JOIN textvote t ON s.no = t.no
WHERE s.no LIKE '%Ginoo%'
GROUP BY s.no"