我在这里有一个大难题:)
我有一个包含2个表的数据库：Survey_reply和问题，
像这样：
table SURVEY_REPLY id | question_id | data_time | user_id | user reply and others col... -------------------------------------------------------- 522| 2 | 2016-02-29 10:07:10 | jacky . | 3 | 2016-02-29 10:07:22 | jacky . | 1 | 2016-02-29 10:07:59 | jacky .. | 4 | 2016-02-29 10:08:40 | jacky ...| 2 | 2016-02-29 11:21:10 | paul . | 3 | 2016-02-29 11:21:32 | paul . | 2 | 2016-02-29 11:21:35 | louise . | 1 | 2016-02-29 11:21:50 | paul .. | 4 | 2016-02-29 11:22:30 | paul .. | 3 | 2016-02-29 11:23:01 | louise
The question are shown to the users following the order in this table:
table QUESTIONS
id | q_ord | survey_id | other columns....
-------------------------------------------
1 | 3 | 786
2 | 1 | 786
3 | 2 | 786
4 | 4 | 786
i would know the average reply time, the time spent by people for make his choice and reply to question.
calculation in this example
4th-3th. (last one minus the previus one)
for reply to question.id=4 (question.q_ord=4)
jacky spent 41 sec (10:08:40-10:07:59)
paul 80 sec (11:22:30-11:21:50)
louise doesnt reply
3日至2日。 （第3个减去第2个）
用于question.id = 1（question.q_ord = 3）
jacky spent 37 sec (10:07:59-10:07:22)
paul 18 sec (11:21:50-11:21:32)
louise doesnt reply
2日至1日。
用于question.id = 3（question.q_ord = 2）
jacky spent 12 sec (10:07:22-10:07:10)
paul 22 sec (11:21:32-11:21:10)
louise 86 sec (11:23:01-11:21:35)
我不需要为开始的问题计算时间。id= 2（question.q_ord = 1）
结果应该是：
q_id | q_ord | av_reply_time
-------------------------------------------
3 | 2 | （12 + 22 + 86）/ 3
1 | 3 | （37 + 18）/ 2
4 | 4 | （41 + 80）/ 2
如何弄清楚？
PS q_ord是连续的整数，不跳过任何数字。
总是以1开头。在这种情况下，我总是知道最大数量（调查中的总问题）只有4个。
最佳答案
我假设您的结果集略有偏离，但我（尚未）看到第二张表的意义...
SELECT question_id
, AVG(diff) avg_diff
FROM
( SELECT x.user_id
, x.question_id
, TIME_TO_SEC(TIMEDIFF(MAX(y.data_time),x.data_time)) diff
FROM survey_reply x
JOIN survey_reply y
ON y.user_id = x.user_id
AND y.data_time < x.data_time
GROUP
BY x.user_id
, x.question_id
) a
GROUP
BY question_id;
关于mysql - 显示记录组之间的平均时间，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35700237/