mysql - MySQL-使用多个表格按过去7天的一天选择最后一个值

这是我当前的数据库：

TABLE profileData
+---------------------------+
| profileID | name          |
+----+----------------------+
| 1         | Stackoverflow |
| 2         | Stackexchange |
| 3         | Askubuntu     |
+-----------+---------------+

TABLE stats
+----+-----------+------------------------------+
| id | profileID | sCount | ts                  |
+----+-----------+--------+---------------------+
| 1  | 1         | 1      | 2013-10-04 00:00:01 |
| 2  | 2         | 5      | 2013-10-04 00:00:01 |
| 3  | 3         | 8      | 2013-10-04 00:00:01 |
| 4  | 1         | 10     | 2013-10-05 00:00:01 |
| 5  | 2         | 50     | 2013-10-05 00:00:01 |
| 6  | 1         | 100    | 2013-10-06 00:00:01 |
| 7  | 2         | 500    | 2013-10-06 00:00:01 |
| 8  | 1         | 101    | 2013-10-06 13:00:01 |
| 9  | 2         | 501    | 2013-10-06 19:00:01 |
| 10 | 3         | 17     | 2013-10-06 05:00:01 |
| 11 | 1         | 100    | 2013-10-09 00:00:01 |
| 12 | 2         | 500    | 2013-10-09 00:00:01 |
+----+-----------+------- +---------------------+

TABLE users
+--------+-----------+
| userID | profileID |
+--------------------+
| 1337   | 1         |
| 1337   | 2         |
+--------+-----------+

我需要的是以下内容：

从“用户”表中选择所有配置文件，然后从“统计”表中获取这些名称的名称以及最近7天每天的最后一个条目。因此，预期结果是

+---------------+--------+---------------------+
| name          | sCount | ts                  |
+---------------+--------+---------------------+
| Stackoverflow | 1      | 2013-10-04 00:00:01 |
| Stackexchange | 5      | 2013-10-04 00:00:01 |
| Stackoverflow | 10     | 2013-10-05 00:00:01 |
| Stackexchange | 50     | 2013-10-05 00:00:01 |
| Stackoverflow | 101    | 2013-10-06 13:00:01 |
| Stackexchange | 501    | 2013-10-06 19:00:01 |
| Stackoverflow | 100    | 2013-10-09 00:00:01 |
| Stackexchange | 500    | 2013-10-09 00:00:01 |
+---------------+--------+---------------------+

我最终得到以下声明：

SELECT
    profileData.name,
    stats.scount,
    stats.ts
FROM
    users
INNER JOIN profiles ON
    users.profileID = profiles.profileID
INNER JOIN
        (
SELECT t1.profileID, t1.sCount, t1.ts
FROM stats t1
INNER JOIN (
SELECT MAX(ts) maxi
FROM stats
GROUP BY DATE(ts)
        ) a2 ON t1.ts = a2.maxi) stats ON
            users.profileID = stats.profileID
WHERE
    users.userID = 1337 AND DATE(stats.ts) >= DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))
ORDER BY users.userID, stats.ts

这部分起作用。但是，此声明似乎是一种过大的手段，并且不再起作用。

我也尝试选择MAX(ts)。那行得通，但是结果不包含对应的sCount值。

因此，我正在寻找解决问题的方法，希望任何人都可以帮助我。

哦，如果可能的话，它必须是一个纯SQL解决方案。

最佳答案

您可以执行类似的操作，与您所做的相差不远。

select p.name,
       s.scount,
       s.ts
from profileData p
inner join users u on u.profileID = p.profileID
inner join stats s on s.profileID = p.profileID
inner join (select max(ts) as maxTs, profileID
            from stats
            where DATE(stats.ts) >= DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))
            group by profileID, DATE(ts)) as mx
         on s.profileID = mx.profileID and mx.maxTs = s.ts
where u.userID = 1337

见sqlfiddle