mysql - 查找每个日期完成的最大值
问题描述
请原谅标题,但我完全不知道如何描述这个。
我有一张这样的桌子:
trackID playerID score date
1 2 4510 1494075555
1 2 4507 1494076300
1 2 4513 1494076561
2 3 39455 1494083772
3 3 5665 1494089018
2 2 38444 1494074519
4 3 34443 1494089138
5 3 56443 1494260918
我只想要每首曲目的第一个完成,一个曲目可以有多个玩家在同一日期完成。我想在一天内完成最多的首次完成。
在上表中,玩家 3 在 2017 年 5 月 6 日首次完成了 3 张地图。在 8.5.2017 上,他只完成了一首曲目。我只想包括他完成最多曲目的日期。
我想要的结果:
playerID trackID count(trackID) date
3 2,3,4 3 6.5.2017
2 1,2 2 6.5.2017
解决方案
这很丑陋,但我们能做些什么。增加样本量以测试有些复杂的算法并显示限制
所以为了更好地验证我的结果。
桌子
CREATE TABLE table1
(`trackID` int, `playerID` int, `score` int, `date` int)
;
INSERT INTO table1
(`trackID`, `playerID`, `score`, `date`)
VALUES
(1, 2, 4510, 1494075555),
(1, 2, 4507, 1494076300),
(1, 2, 4513, 1494076561),
(2, 3, 39455, 1494083772),
(3, 3, 5665, 1494089018),
(2, 2, 38444, 1494074519),
(4, 3, 34443, 1494089138),
(5, 3, 56443, 1494260918),
(6, 3, 56443, 1494260928),
(7, 3, 56443, 1494260938),
(1, 4, 4510,1494324530),
(2, 4, 4510,1494324530),
(3, 4, 4510,1494324530),
(4, 4, 4510,1494324530),
(5, 4, 4510,1494324530),
(6, 4, 4510,1494324530)
;
有了这个选择语句
SELECT
a4.trackID, a4.playerID,
MAX(
a4.countgroup
) countgroup,
MAX(
CONCAT(day_a,".",month_a,".",year_a)
) `date`
FROM
(
SELECT
Group_COnCAT(DISTINCT trackID) trackID,
playerID
, Count(*) countgroup
,YEAR(FROM_UNIXTIME(`date`)) year_a
,MONTH(FROM_UNIXTIME(`date`)) month_a
,DAY(FROM_UNIXTIME(`date`)) day_a
FROM
(Select
DISTINCT trackID, playerID,MAX(`date`) `date`
FROM table1
GROUP by playerID,trackID
) c1
GROUP BY playerID,YEAR(FROM_UNIXTIME(`date`))
,MONTH(FROM_UNIXTIME(`date`)) ,DAY(FROM_UNIXTIME(`date`))
) a4
inner join
( SELECT
a2.playerID, a2.countgroup,a2.`date`
FROM
(
SELECT playerID, Count(*) countgroup, MAX(`date`) `date`
FROM
(
Select
DISTINCT trackID, playerID,MAX(`date`) `date`
FROM table1
GROUP by playerID,trackID
) c1
GROUP BY playerID,YEAR(FROM_UNIXTIME(`date`))
,MONTH(FROM_UNIXTIME(`date`)) ,DAY(FROM_UNIXTIME(`date`))
) a2
INNER JOIN
(
SELECT playerID,max(countgroup) maxcountgroup, MAX( `date`) `date`
FROM
(
SELECT playerID, Count(*) countgroup, MAX( `date`) `date`
FROM
(
Select
DISTINCT trackID, playerID,MAX(`date`) `date`
FROM table1
GROUP by playerID,trackID
) c1
GROUP BY playerID,YEAR(FROM_UNIXTIME(`date`))
,MONTH(FROM_UNIXTIME(`date`)) ,DAY(FROM_UNIXTIME(`date`))
) g1
GROUP BY playerID,YEAR(FROM_UNIXTIME(`date`))
,MONTH(FROM_UNIXTIME(`date`)) ,DAY(FROM_UNIXTIME(`date`))
) a3
ON a2.playerID = a3.playerID AND a3.maxcountgroup = a2.countgroup
and YEAR(FROM_UNIXTIME(a2.`date`)) = YEAR(FROM_UNIXTIME(a3.`date`))
#and MONTH(FROM_UNIXTIME(a2.`date`)) = MONTH(FROM_UNIXTIME(a3.`date`))
#and DAY(FROM_UNIXTIME(a2.`date`)) = DAY(FROM_UNIXTIME(a3.`date`))
) b4
ON a4.playerID = b4.playerID AND a4.countgroup = b4.countgroup
GROUP BY playerID,trackID;
你会得到以下结果
trackID playerID countgroup date
1,2 2 2 6.5.2017
2,3,4 3 3 6.5.2017
5,6,7 3 3 8.5.2017
1,2,3,4,5,6 4 6 9.5.2017
这很复杂:所以在表 a4 中我得到了所需的数据,但是每个玩家都有多天,我必须选择赢得最多曲目的日子。
进入表 b4,它为每个玩家选择到达的日期和最多的曲目。
您在示例中看到的问题是,当有两天或更多天征服了相同数量的轨道时。
这是 dbfiddle 示例,以便您可以对其进行测试 https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=88bb4f9c208ccf6a9865b79b1681faa5
编辑2:必须将日期列添加到 SEELCT 和 b4 的子句中
推荐阅读
- mysql - 两张表之间有哪些关系?
- python - 从播放列表中抓取视频标题
- node.js - MongoDB 基于字符串类型匹配
- c# - 在启动 C# .Net 5 MacOS 时启动程序
- python - Polar 等高线 2 的问题
- python - 如何从响应中获取 xml 内容并将其作为 Python 中的帖子发送回
- python - python3.7 install on mac not recognized in terminal
- oracle - 将数据插入表单 oracle apex
- java - 错误:无法访问 jarfile auth-api.jar - SpringBoot
- python - 按空格键暂停和恢复python中的循环