我正在尝试构建一种 peddy 纸。为此，我有下表:

团队

CREATE TABLE `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creator_id` int(11) NOT NULL,
  `friend_id` int(11) DEFAULT NULL,
  `team_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
);

团队日志

CREATE TABLE IF NOT EXISTS `progress_tracker` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `user_id` int(8) NOT NULL,
  `team_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `clues_found` int(11) NOT NULL,
  `clues_to_find` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

1. 每个团队由两名用户组成；
2. 每个用户从发现的线索数量开始；
3. clues_found 可以增加也可以减少。不保证最高的就是最新的；

我需要根据用户加入后发现的线索数量的平均值(对于团队中的两个用户)获得团队排名(百分比) - clues_found 在最大日期的行减去 clues_found日期最短的记录)。

例如，如果我有每个表的以下数据:

团队表数据

+--------+------------+------------+---------------+
|     id | creator_id | friend_id  |   team_name   |
+--------+------------+------------+---------------+
|      1 |         25 |         28 |         Test1 |
|      2 |         31 |          5 |         Test2 |
+--------+------------+------------+---------------+

team_log 表数据

+--------+---------+---------+------------+-------------+---------------+
|     id | user_id | team_id |    date    | clues_found | clues_to_find |
+--------+---------+---------+------------+-------------+---------------+
|      1 |      25 |       1 | 2013-01-6  |           3 |            24 |
|      2 |      25 |       1 | 2013-01-8  |           7 |            24 |
|      3 |      25 |       1 | 2013-01-10 |          10 |            24 |
|      4 |      28 |       1 | 2013-01-8  |           5 |            30 |
|      5 |      28 |       1 | 2013-01-14 |          20 |            30 |
|      6 |      31 |       2 | 2013-01-11 |           6 |            14 |
|      7 |       5 |       2 | 2013-01-9  |           2 |            20 |
|      8 |       5 |       2 | 2013-01-10 |          10 |            20 |
|      9 |       5 |       2 | 2013-01-12 |          14 |            20 |
+--------+---------+---------+------------+-------------+---------------+

期望的结果

+-------------+---------------------+
|     team_id |   team_percentage   |
+-------------+---------------------+
|           1 |         39,58333333 |
|           2 |         30          |
+-------------+---------------------+

作为引用，这是一个可能有助于理解的中间表示:

+-------------+---------+---------------------+
|     user_id | team_id | precentage_per_user |
+-------------+---------+---------------------+
|          25 |       1 | 29,16666667         |
|          28 |       1 | 50                  |
|          31 |       2 | 0                   |
|           5 |       2 | 60                  |
+-------------+---------+---------------------+

到目前为止，我有以下 sql:

SELECT STRAIGHT_JOIN
      tl2.team_id, (tl2.weight - tl1.weight)*100/tl2.clues_to_find
   from 
       ( select
               team_id,user_id,clues_found
            FROM 
               `team_log` 
            where 1

            group by
               team_id, user_id
            order by
               `date` ) base
       join (select team_id, user_id, clues_found, clues_to_find from `team_log` where user_id = base.user_id and team_id = base.team_id group by team_id, user_id order by `date` desc) tl2

但这会返回一个错误，因为我不允许在第二个查询中使用 base.user_id。我也不太确定我正朝着正确的方向前进。

有人可以帮忙吗？

最佳答案

这是另一个会产生正确结果的查询:

SELECT calc.team_id, AVG((calc.end_clues - calc.start_clues)/calc.total_clues*100) as team_percentage
FROM
    (SELECT log1.user_id, log1.team_id, log1.clues_found as start_clues, log2.clues_found as end_clues, log2.clues_to_find as total_clues FROM team_log log1
    JOIN
    (SELECT MIN(id) as start_id, MAX(id) as end_id FROM team_log GROUP BY user_id) ids
    ON ids.start_id = log1.id
    JOIN team_log log2 ON ids.end_id = log2.id) calc
GROUP BY team_id
ORDER BY team_id;

And the SQL Fiddle-link...

关于Mysql计算不同行的团队排名，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14325181/