php - MySQL中的几个表和操作的SQL请求

Question

我有以下 3 个表:单位、阶段、统计数据。

      unit                      stage
+----+--------+   +----+-------+---------------------+
| id | status |   | id |unit_id|        date         |
+----+--------+   +----+-------+---------------------+
|  1 |      2 |   |  1 |     2 | 2013-11-22 00:00:00 |
|  2 |      3 |   |  2 |     2 | 2013-11-26 12:00:00 |
|  3 |      3 |   |  3 |     3 | 2013-10-11 00:00:00 |
|  4 |      0 |   |  4 |     1 | 2013-12-29 00:00:00 |
+----+--------+   +----+-------+---------------------+

                     stats
+----+----------+---------------------+-------+
| id | stage_id |        date         | clicks|
+----+----------+---------------------+-------+
|  1 |        1 | 2013-11-22 00:00:00 |    10 |
|  2 |        1 | 2013-11-23 00:00:00 |    20 |
|  3 |        1 | 2013-11-24 00:00:00 |    25 |
|  4 |        2 | 2013-11-26 00:00:00 |    15 |
|  5 |        2 | 2013-11-27 12:00:00 |    21 |
|  6 |        3 | 2013-12-29 00:00:00 |     8 |
+----+----------+---------------------+-------+

我需要一个请求，它将产生以下响应:

+---------+---------------------+-----------------------+
| unit.id |   stage.min.date    | sum(stats.max.clicks) |
+---------+---------------------+-----------------------+
|       2 | 2013-11-22 00:00:00 |                    46 |
|       3 | 2013-12-29 00:00:00 |                     8 |
+---------+---------------------+-----------------------+

遵循以下规则:

1) unit.id - 仅显示带有 unit.status=3 的单位

2) stage.min.date - 最小stage.date对应unit_id

3) sum(stats.max.clicks) - stats.clicks 的总和每个 stage_id 的最大 d 值与相应的unit_id相关联。在我的示例中 46 = 25( stage_id=1 ) + 21( stage_id=2 )

问题出在 min.date 和点击次数中 - 我不知道如何在一个查询中获取它。使用 php 代码和多个请求绝对不是问题。

Schema in SQL Fiddle

提前致谢。

Answer 1

我只是问自己，为什么要这样做？您的示例响应有错误，并且与您的 fiddle 不匹配...但是:

SELECT
  cc.unit_id, MIN(cc.date) as stage_min_date , SUM(dd.clicks) as stats_max_clicks
FROM
  stage cc
LEFT JOIN (
  SELECT
    bb.stage_id, bb.clicks
  FROM
    stats bb LEFT JOIN (
      SELECT id, stage_id, MAX(date) AS max_date
      FROM stats
      GROUP BY stage_id
    ) aa
  ON
    aa.max_date = bb.date
  WHERE
    aa.max_date IS NOT NULL
) dd
ON cc.id = dd.stage_id
LEFT JOIN unit ee 
ON ee.id = cc.unit_id
WHERE ee.status = 3
GROUP BY cc.unit_id

...