mysql同行数据计算,Mysql计算不同行的团队等级

我正在尝试制作一种小册子纸.为此,我有以下表格：

球队

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`)

);

team_log

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`)

);

>每个团队由两个用户组成;

>每个用户都可以找到可变数量的线索;

> 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.我也不太确定我正朝着正确的方向前进.

有人可以帮忙吗？

解决方法:

请看一下并发表评论：

团队pct：

select z.team_id, avg(z.pct) as teampct

from (

select x.user_id, y.team_id, x.mndate,

y.mxdate, x.mnclues_found,

y.mxclues_found,

(((y.mxclues_found - x.mnclues_found)*100)

/y.mxclues_tofind) pct

from

(select user_id, team_id, min(date) mndate,

min(clues_found) as mnclues_found

from team_log

group by user_id, team_id) x

left join

(select user_id, team_id, max(date) mxdate,

max(clues_found) as mxclues_found,

max(clues_to_find) as mxclues_tofind

from team_log

group by user_id, team_id) y

on x.user_id = y.user_id and

x.team_id = y.team_id) z

group by z.team_id

;

结果1：

| USER_ID | TEAM_ID | MNDATE | MXDATE | MNCLUES_FOUND | MXCLUES_FOUND | PCT |

-------------------------------------------------------------------------------------

| 5 | 2 | 13-01-09 | 13-01-12 | 2 | 14 | 60 |

| 25 | 1 | 13-01-06 | 13-01-10 | 3 | 10 | 29.1667 |

| 28 | 1 | 13-01-08 | 13-01-14 | 5 | 20 | 50 |

| 31 | 2 | 13-01-11 | 13-01-11 | 6 | 6 | 0 |

结果最终：

| TEAM_ID | TEAMPCT |

----------------------

| 1 | 39.58335 |

| 2 | 30 |

标签：mysql,sql

来源： https://codeday.me/bug/20190620/1245957.html