php - 如何根据不同的时间范围从多个表中选择数据？

Question

我在 MySQL 中有两个表:

• 访问
• 积分

visits 表如下所示:

+-------+-------------------+-----------+
|   id  |   date            |  user_id  |
+-------+-------------------+-----------+
|   1   |2014-08-01 05:23:00|       43  |
|   2   |2014-08-01 14:41:00|       21  |
|   3   |2014-08-02 23:54:00|       43  |
|   4   |2014-08-03 03:21:00|       43  |
|   5   |2014-08-03 04:19:00|       34  |
|   6   |2014-08-03 11:33:00|       43  |
|   7   |2014-08-04 12:21:00|       43  |
|   8   |2014-08-05 01:55:00|       43  |
|   9   |2014-08-06 06:13:00|       43  |
|  10   |2014-08-07 19:47:00|       43  |
+-------+-------------------+-----------+

points 表如下所示:

+-------+-------------------+-----------+-------+----------+
|   id  |   date            |  user_id  | points|   status |
+-------+-------------------+-----------+-------+----------+
|   1   |2014-08-01 04:33:00|       43  |   10  |        0 |
|   2   |2014-08-02 05:21:00|       21  |   23  |        0 |
|   3   |2014-08-02 09:01:00|       43  |   32  |        1 |
|   4   |2014-08-02 01:21:00|       43  |   21  |        0 |
|   5   |2014-08-03 23:23:00|       34  |   54  |        0 |
|   6   |2014-08-04 20:34:00|       43  |   11  |        0 |
|   7   |2014-08-04 17:54:00|       43  |    9  |        0 |
|   8   |2014-08-04 03:45:00|       43  |   34  |        0 |
|   9   |2014-08-06 08:23:00|       43  |   76  |        0 |
|  10   |2014-08-07 11:43:00|       43  |   52  |        0 |
+-------+-------------------+-----------+-------+----------+

我只想执行 1 个查询并实现以下目标。

我想计算 visits 表中user_id = 43 和date 的行> 介于 2014-08-01 和 2014-08-03 之间。

我还想计数行，求和points表中的点，其中 user_id = 43 并且 date 在 2014-08-01 和 2014-08-03 之间并且状态为 0。

之后，在同一个查询中，我想选择与上面相同的内容，但在不同的时间范围内，例如:2014-08-04 和 2014-08- 07。

是否有任何查询可以为我解决这个问题？

(我实际上这样做是因为我想为我的一个名为 Morris.js 的 jQuery 图表获取数据。如果访问者，我想获取 12 个数据集选择一个时间范围并根据访问者选择的日期进行划分。例如:如果他选择:2014-08-01 到 2014-08-01，我想向他显示当天的 12 个数据集。但是如果他选择例如:2014-08-01 到 2014-08-06，那么我想向他显示 6 天的数据除以 12。)

如果您不明白这里的内容，请告诉我，我会更好地解释。关键是我想收集数据集并根据访问者的时间范围绘制图表。上面的 MySQL 逻辑是解决我的问题的好方法吗？

编辑:

根据要求，我在这里显示了所需的结果:

+---------------------+-----------+-------+-------+-------------+
|   date              |  user_id  | points| visits| points_count|
+---------------------+-----------+-------+-------+-------------+
|2014-08-01-2014-08-03|     43    | 31    | 4     | 2           |
|2014-08-04-2014-08-07|     43    | 182   | 4     | 5           |
+---------------------+-----------+-------+-------+-------------+

我希望我已经正确计算了所有内容。

Answer 1

好的，所以这需要一些工作，因为您必须彼此独立地连接两个表。原因是因为一个不能具有 1 的状态而另一个可以。所以考虑到这个查询将完全返回您想要的。

查询:

SELECT 
    t.join_date as 'Time Frame',
    t1.user_id,
    t.num_visits,
    t1.num_points,
    t1.total_points
FROM
(   SELECT 
        CASE 
            WHEN DATE(date) >= '2014-06-01' AND DATE(date) <= '2014-07-10' THEN 1 
            WHEN DATE(date) >= '2014-08-05' AND DATE(date) <= '2014-08-07' THEN 2 
            ELSE 3
        END AS grouping_col,
        CONCAT(MIN(DATE(date)), ' - ', MAX(DATE(date))) as join_date,
        COUNT(id) as num_visits 
    FROM visits 
    WHERE user_id = 43
    GROUP BY grouping_col 
)t
LEFT JOIN 
(   SELECT
        CASE 
            WHEN DATE(date) >= '2014-06-01' AND DATE(date) <= '2014-07-10' THEN 1 
            WHEN DATE(date) >= '2014-08-05' AND DATE(date) <= '2014-08-07' THEN 2 
            ELSE 3
        END AS grouping_col,
        CONCAT(MIN(DATE(date)), ' - ', MAX(DATE(date))) as join_date,
        user_id,
        COUNT(id) as num_points,
        SUM(points) as total_points
    FROM points
    WHERE user_id = 43
      AND status = 0
    GROUP BY grouping_col
)t1 ON t1.grouping_col = t.grouping_col
WHERE t.grouping_col IN(1, 2) OR t1.grouping_col IN(1, 2)

注意:

您可以通过向 CASE 语句添加更多行来添加尽可能多的时间范围...

查看演示:

FIDDLE