mysql - 按平均日期(时间戳)排序

Question

非常感谢您在这方面的帮助。

我有一个应用程序，用户可以在其中创建一个由 4 名 friend 组成的团队。该应用程序将 friend 插入数据库，该表有一个“问题”列和“答案”列，当 friend 回答问题时，最后一个列会更新。此“ friend ”表还有一个“date_answer”(时间戳)列，默认值设置为 ON UPDATE CURRENT_TIMESTAMP。

一切正常，我现在要做的就是按照团队的平均 date_answer 对团队进行排序。我的意思是，第一个团队将是用户首先回答问题的团队。

我希望我能解释清楚。表格如下:

Teams
------
id
name
created
*******

Friends
-------
id
name
team_id
question
answer
date_answer

Team 1 可能会这样回答: friend 1:凌晨 1 点 friend 2:凌晨 2 点 friend 3:凌晨 3 点 friend 4:下午3点

第 2 队: friend 1:凌晨 1 点 friend 2:凌晨 4 点 friend 3:凌晨 1 点 friend 4:下午6点

获胜者将是第 1 队，因为 friend 们回答得更快。

Answer 1

据我了解，类似的事情应该有所帮助。我已经加入了第三个团队，其中一名成员仍在努力回答这个问题......

create table Teams
(
id int unsigned not null auto_increment primary key,
name varchar(50) not null,
created datetime not null
);

create table Friends
(
id int unsigned not null auto_increment primary key,
name varchar(50) not null,
team_id int unsigned not null,
question varchar(100) default null,
answer varchar(100) default null,
date_answer timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

insert into Teams (name,created) values ("Team 1",now());
insert into Teams (name,created) values ("Team 2",now());
insert into Teams (name,created) values ("Team 3",now());


insert into Friends (name,team_id,question,answer,date_answer) values ("Bob",1,"What Is Your Name?","Bob",'2012-08-15 01:00:00');
insert into Friends (name,team_id,question,answer,date_answer) values ("Joe",1,"What Is Your Name?","Joe",'2012-08-15 02:00:00');
insert into Friends (name,team_id,question,answer,date_answer) values ("Tim",1,"What Is Your Name?","Tim",'2012-08-15 03:00:00');
insert into Friends (name,team_id,question,answer,date_answer) values ("Tom",1,"What Is Your Name?","Tom",'2012-08-15 15:00:00');

insert into Friends (name,team_id,question,answer,date_answer) values ("Jon",2,"What Is Your Name?","Jon",'2012-08-15 01:00:00');
insert into Friends (name,team_id,question,answer,date_answer) values ("Jim",2,"What Is Your Name?","Jim",'2012-08-15 04:00:00');
insert into Friends (name,team_id,question,answer,date_answer) values ("Josh",2,"What Is Your Name?","Josh",'2012-08-15 01:00:00');
insert into Friends (name,team_id,question,answer,date_answer) values ("Jack",2,"What Is Your Name?","Jack",'2012-08-15 18:00:00');

insert into Friends (name,team_id,question,answer,date_answer) values ("Dave",3,"What Is Your Name?","Jon",'2012-08-15 01:00:00');
insert into Friends (name,team_id,question,answer,date_answer) values ("Dug",3,"What Is Your Name?","Jim",'2012-08-15 04:00:00');
insert into Friends (name,team_id,question,answer,date_answer) values ("Dappy",3,"What Is Your Name?",null,null);

-- Winning team must have an answer from all teams members and have earliest average 
-- date_answer
select t.name as "Winning Team",
sum(case when f.answer is not null then 1 else 0 end) as answerCount,
count(*) as teamCount,
timestamp(avg(f.date_answer)) as averageAnswerDate
from Teams t
left join Friends f on f.team_id = t.id
group by t.name
having answerCount = teamCount
order by averageAnswerDate asc
limit 1;