代码之家 › 专栏 › 技术社区 › calumbrodie

复杂联接-涉及日期范围和和

between sum join mysql

1

calumbrodie · 技术社区 · 15 年前

我有两张桌子需要加入…我想在“id”上联接表1和表2,但是在表2中,id不是唯一的。我只希望为表2返回一个值,该值表示名为“售出总额”的列在指定日期范围(例如一个月)内的总和,但是我希望同时有多个日期范围…

SELECT ta.id, sum(tb.total_sold) as total_sold_this_week, sum(tc.total_sold) as total_sold_this_month
FROM table_a as ta
LEFT JOIN table_b as tb ON ta.id=tb.id AND tb.date_sold BETWEEN ADDDATE(NOW(),INTERVAL -1 WEEK) AND NOW()
LEFT JOIN table_b as tc ON ta.id=tc.id AND tc.date_sold BETWEEN ADDDATE(NOW(),INTERVAL -1 MONTH) AND NOW()
GROUP BY ta.id

这是有效的,但不求和行-每个ID只返回一行…如何从表B中而不是只从一行中得到和???? 请批评问题的格式是否可以使用更多的工作-如果需要,我可以重写并提供示例数据-这是一个更大问题的琐碎版本。

-谢谢

1 回复 | 直到 15 年前

1

6

gnarf 15 年前

使用子查询

解决这个问题的一种方法是 subqueries . LEFT JOIN 为右表中的每个匹配项创建一个新的“result”,因此使用两个左联接创建的行比您想要的多。您可以选择所需的值,但这可能很慢:

SELECT ta.id, 
   (SELECT SUM(total_sold) as total_sold 
    FROM table_b 
    WHERE date_sold BETWEEN ADDDATE(NOW(), INTERVAL -1 WEEK) AND NOW()
    AND id=ta.id) as total_sold_this_week, 
   (SELECT SUM(total_sold) as total_sold 
    FROM table_b 
    WHERE date_sold BETWEEN ADDDATE(NOW(), INTERVAL -1 MONTH) AND NOW() 
    AND id = ta.id) as total_sold_this_month 
FROM table_a ta;

结果:

+----+----------------------+-----------------------+
| id | total_sold_this_week | total_sold_this_month |
+----+----------------------+-----------------------+
|  1 |                    3 |                     7 |
|  2 |                    4 |                     4 |
|  3 |                 NULL |                  NULL |
+----+----------------------+-----------------------+
3 rows in set (0.04 sec)

使用SUM(大小写…)

此方法不使用子查询(在较大的数据集上可能更快)。我们想将表A和表B连接在一起一次,使用我们的“最大”日期范围,然后使用 SUM() 基于A CASE 计算“较小范围”。

SELECT ta.*, 
  SUM(total_sold) as total_sold_last_month, 
  SUM(CASE 
    WHEN date_sold BETWEEN NOW() - INTERVAL 1 WEEK AND NOW() 
    THEN total_sold
    ELSE 0 
    END) as total_sold_last_week 
FROM table_a AS ta 
LEFT JOIN table_b AS tb 
   ON ta.id=tb.id AND tb.date_sold BETWEEN ADDDATE(NOW(),INTERVAL -1 MONTH) AND NOW() 
GROUP BY ta.id;

这将返回与子查询示例几乎相同的结果集:

+----+-----------------------+----------------------+
| id | total_sold_last_month | total_sold_last_week |
+----+-----------------------+----------------------+
|  1 |                     7 |                    3 |
|  2 |                     4 |                    4 |
|  3 |                  NULL |                    0 |
+----+-----------------------+----------------------+
3 rows in set (0.00 sec)

唯一的区别是 0 而不是 NULL . 使用此方法可以总结尽可能多的日期范围,但最好还是将返回的行限制在 ON 条款。

只是为了展示它的工作原理:移除 GROUP BY 和 () 调用,并添加 date_sold 选择返回:

+----+------------+-----------------------+----------------------+
| id | date_sold  | total_sold_last_month | total_sold_last_week |
+----+------------+-----------------------+----------------------+
|  1 | 2010-04-30 |                     2 |                    2 |
|  1 | 2010-04-24 |                     2 |                    0 |
|  1 | 2010-04-24 |                     2 |                    0 |
|  1 | 2010-05-03 |                     1 |                    1 |
|  2 | 2010-05-03 |                     4 |                    4 |
|  3 | NULL       |                  NULL |                    0 |
+----+------------+-----------------------+----------------------+
6 rows in set (0.00 sec)

现在当你 GROUP BY id 和 () 这两个总销售栏你有你的结果!

陈旧的建议

在将两个不同的日期范围加入到组合中之前,您可以使用 GROUP BY 使用表1上的表ID分组,以及 SUM() 聚合函数来添加返回的行。

SELECT ta.id, SUM(tb.total_sold) as total_sold_this_week
FROM table_a as ta
LEFT JOIN table_b as tb 
ON ta.id=tb.id AND tb.date_sold BETWEEN ADDDATE(NOW(),INTERVAL -3 WEEK) AND NOW()
GROUP BY ta.id

+----+----------------------+
| id | total_sold_this_week |
+----+----------------------+
|  1 |                    7 |
|  2 |                    4 |
|  3 |                 NULL |
+----+----------------------+
3 rows in set (0.00 sec)

测试数据

NOW() 2010-05-03

mysql> select * from table_a; select * from table_b;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

+----+------------+------------+
| id | date_sold  | total_sold |
+----+------------+------------+
|  1 | 2010-04-24 |          2 |
|  1 | 2010-04-24 |          2 |
|  1 | 2010-04-30 |          2 |
|  1 | 2010-05-03 |          1 |
|  2 | 2010-05-03 |          4 |
+----+------------+------------+
5 rows in set (0.00 sec)