mysql - 分页统计报告

Question

我正在使用 MySQL 开发一个简单的消息系统。所以我有一个像这样的表结构(简化):

CREATE TABLE `messages` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `account_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `body` blob,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

现在，我正在研究一些上帝面板统计数据，这些统计数据应该呈现一个分页的、可按列排序的表格报告，其中包含以下列:

用户 ID、全名和电子邮件(单独的表格)昨天发送的消息过去 30 天内发送的消息消息发送生命周期

因此，我创建了一个像这样的 messages_stats_daily 表，每晚进程都会在每晚填充“昨天”的数据:

CREATE TABLE `messages_stats_daily` (
  `date` date NOT NULL DEFAULT '0000-00-00',
  `account_id` int(10) unsigned NOT NULL DEFAULT '0',
  `count` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`date`,`user_id`)
)

这就是我用于报告的查询:

SELECT SQL_CALC_FOUND_ROWS u.*, msd1.count, SUM(msd_30days.count), SUM(msd3.count) 
FROM accounts a 
INNER JOIN users AS u ON a.default_user_id = u.id
LEFT JOIN messages_stats_daily msd_yesterday 
  ON u.account_id = msd_yesterday.account_id 
    AND msd_yesterday.date = date(DATE_SUB(NOW(), INTERVAL 1 DAY))
LEFT JOIN messages_stats_daily msd_30days 
  ON u.account_id = msd_30days.account_id 
    AND (msd_30days.date BETWEEN date(DATE_SUB(NOW(), INTERVAL 30 DAY)) AND CURDATE())
LEFT JOIN messages_stats_daily msd_lifetime 
  ON u.account_id = msd_lifetime.account_id
GROUP BY `a`.`id`
ORDER BY SUM(msd_30days.count)
LIMIT 0, 50;

但由于某种原因，这些数字是错误的。它们奇怪地成倍增加，但我无法确定一个固定因子(我知道它们成倍增加，因为 1 天、30 天和 2 年之间的近似关系仍然适用)。对我做错了什么有什么想法吗？我也愿意更改统计数据的夜间进程行为。

注意:我知道我可以通过从第一个简单查询中获取 ID，然后为这些 ID 触发三个简单查询来实现此目的，但这不起作用，因为报告需要分页并按列排序。

Answer 1

要对 SQL 结果进行分页，请使用 LIMIT x, y，其中 x 是起始结果(0 索引)，y 是结果数。我在您的查询中的任何地方都看不到 LIMIT，因此您的分页可能过于复杂。

了解更多信息here

<小时/>

我还注意到您在为 LEFT JOIN 中的表分配别名时没有使用 AS 关键字

了解有关别名的更多信息 here

<小时/>

正如您提到的执行 3 个单独的查询，您可以考虑执行嵌套/子查询。为了节省实际提交多个查询。 Here