结果可以在不同列中的最后 x 个结果的 MySQL sum avg

Question

我有一个学校辩论数据库，我想在其中查看每所学校在过去 5 场辩论中的平均分。如果受教育者主持辩论，则得分记录在名为 hostscore 的列中，如果他们访问，则得分记录在 visitscore 中。

这是我正在处理的数据的摘录

CREATE TABLE schools (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

CREATE TABLE debates (
    debateid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    debatedate DATE NOT NULL,
    hostid INT,
    visitid INT,
    hostscore INT,
    visitscore INT
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

如果我这样做

SELECT debates.debateid, DATE_FORMAT(debates.debatedate,'%m-%d') AS DATE, school1.name AS HOST, school2.name AS VISITOR, debates.hostscore, debates.visitscore
FROM debates 
INNER JOIN schools as school1 ON debates.hostid=school1.id 
INNER JOIN schools as school2 ON debates.visitid=school2.id 
WHERE ((school1.id = 1 OR school2.id =1) ) AND debatedate < CURDATE()
ORDER BY debatedate DESC LIMIT 0 , 5

我可以看到他们最近的 5 场辩论。

如果我执行以下操作。

SELECT visitid,
(
SELECT 
(
((SELECT sum(visitscore) FROM debates WHERE (visitid=1) AND debatedate < CURDATE()) + (SELECT sum(hostscore) FROM debates WHERE (hostid=1) AND debatedate < CURDATE()))
/
(SELECT COUNT(*) FROM debates WHERE ((visitid=1)or(hostid=1)) AND debatedate < CURDATE())
) 
FROM debates 
INNER JOIN schools as school1 ON debates.hostid=school1.id 
INNER JOIN schools as school2 ON debates.visitid=school2.id 
LIMIT 0,1
)
AS AVGSCORE 
FROM debates 
WHERE visitid=1 
LIMIT 0,1

我可以看到他们当年每场辩论的平均得分。

但我不知道如何显示最后 5 场(总体)辩论(以下数据中 hostid=1 和 visitid =1 的平均值)

    ----------------------------------------------------------------------
    |DEBATEID   |DATE   |HOST       |VISITOR    |HOSTSCORE  |VISITSCORE |
    |20         |09-22  |St Luke    |St Thomas  |82         |84         |
    |16         |08-22  |St Thomas  |St Simon   |91         |88         |
    |15         |08-12  |St Luke    |St Thomas  |75         |64         |
    |11         |07-12  |St Thomas  |St Simon   |72         |64         |
    |10         |06-28  |St Luke    |St Thomas  |82         |84         |
    ----------------------------------------------------------------------

根据手动计算，此示例中的结果应该是 79。我在这里尝试了很多方法。我认为这看起来不错，但它产生的结果与我想要的相去甚远。我究竟做错了什么？看这个SQL Fiddle用于测试数据

SELECT sum(visitid),
(SELECT 
(
(
(SELECT sum(visitscore) FROM (SELECT visitscore FROM debates WHERE (hostid=1) AND debatedate < CURDATE() ORDER BY debatedate DESC LIMIT 0,5) as awayavgpg1)
+ 
(SELECT sum(visitscore) FROM (SELECT visitscore FROM debates WHERE (visitid=1) AND debatedate < CURDATE() ORDER BY debatedate DESC LIMIT 0,5) as awayavgpg2)
)
/
(SELECT COUNT(*) FROM (SELECT * FROM debates WHERE (hostid=1) or (visitid=1) AND debatedate < CURDATE()LIMIT 0,5) as awayavgpg3)
)
FROM debates 
LIMIT 0,1) AS AVSCORE
FROM debates 
WHERE visitid=1 
LIMIT 0,1

Answer 1

使用 UNION 将列合并为一个:

SELECT AVG(score) avscore
FROM (SELECT debatedate, score
      FROM (SELECT debatedate, hostscore score
            FROM debates
            WHERE hostid = 1
            UNION
            SELECT debatedate, visitscore score
            FROM debates
            WHERE visitid = 1) x
      ORDER BY debatedate DESC
      LIMIT 5) y