MYSQL:从两个 SELECT 左连接到日期中的 "fill gaps"

Question

假设我有一个表“日历”

+------------+
|  day_date  |
+------------+
| 2015-01-01 |
| 2015-01-02 |
| 2015-01-03 |
| .......... |
| 2015-07-14 |
| 2015-07-15 |
+------------+

通过此查询，我可以选择(我需要的)周

SELECT WEEK(day_date,1) AS NUM_WEEK,
YEAR(day_date) AS YEAR,
STR_TO_DATE(CONCAT(YEAR(day_date),WEEK(day_date,1),' Monday'), '%X%V %W') AS date_start
FROM calendar
GROUP BY NUM_WEEK

这就是结果:

+----------+------+------------+
| NUM_WEEK | YEAR | date_start |
+----------+------+------------+
|       29 | 2015 | 2015-07-20 |
|       30 | 2015 | 2015-07-27 |
|       31 | 2015 | 2015-08-03 |
|       32 | 2015 | 2015-08-10 |
|       33 | 2015 | 2015-08-17 |
|       34 | 2015 | 2015-08-24 |
|       35 | 2015 | 2015-08-31 |
|       36 | 2015 | 2015-09-07 |
|       37 | 2015 | 2015-09-14 |
|       38 | 2015 | 2015-09-21 |
|       39 | 2015 | 2015-09-28 |
|       40 | 2015 | 2015-10-05 |
|       41 | 2015 | 2015-10-12 |
|       42 | 2015 | 2015-10-19 |
|       43 | 2015 | 2015-10-26 |
+----------+------+------------+

现在我有另一张 table :

+----+------------+--------+---------------------+
| id | id_account | amount |  date_transaction   |
+----+------------+--------+---------------------+
|  1 |        283 | 150    | 2015-06-21 15:50:47 |
|  2 |        283 | 47.74  | 2015-07-23 15:55:44 |
|  3 |        281 | 21.55  | 2015-08-24 12:27:11 |
|  4 |        283 | 11.22  | 2015-08-25 10:00:54 |
+----+------------+--------+---------------------+

它们是日期上的差距。

使用类似的查询:

    SELECT WEEK(date_transaction,1) AS NUM_WEEK,
YEAR(date_transaction) AS YEAR,
STR_TO_DATE(CONCAT(YEAR(date_transaction),WEEK(date_transaction,1),' Monday'), '%X%V %W')
AS date_start,
transaction.id_account,
SUM(amount) as total FROM transaction
INNER JOIN account ON account.id_account = transaction.id_account
WHERE amount > 0 AND transaction.id_account 
IN ( SELECT id_account FROM account WHERE id_customer = 12 )
GROUP BY id_account, WEEK(date_transaction,1)

我得到这个结果(可能数据不准确，引用之前的表格，只是为了解释一下)。

+----------+------+------------+-----------+----------+
| NUM_WEEK | YEAR | date_start | idAccount |  total   |
+----------+------+------------+-----------+----------+
|       29 | 2015 | 2015-07-20 |       281 | 22377.00 |
|       30 | 2015 | 2015-07-27 |       281 | 11550.00 |
|       32 | 2015 | 2015-08-04 |       281 | 4500.00  |
|       30 | 2015 | 2015-07-27 |       283 | 1500     |
+----------+------+------------+-----------+----------+

我会怎样，右(或左)连接两个表？

1. 最短(和最长)周，这样我就可以...(参见 2)
2. 用 NULL 值填充缺少的周数。

例如，在更复杂的结果集中:

+----------+------+------------+-----------+----------+
| NUM_WEEK | YEAR | date_start | idAccount |  total   |
+----------+------+------------+-----------+----------+
|       29 | 2015 | 2015-07-20 |       281 | 22377.00 |
|       30 | 2015 | 2015-07-27 |       281 | 11550.00 |
|       31 | 2015 | 2015-07-02 |       281 | NULL     |
|       32 | 2015 | 2015-08-09 |       281 | 4500.00  |
|       29 | 2015 | 2015-08-09 |       283 | NULL     |
|       30 | 2015 | 2015-07-16 |       283 | 1500     |
|       31 | 2015 | 2015-07-16 |       283 | NULL     |
|       32 | 2015 | 2015-07-16 |       283 | NULL     |
+----------+------+------------+-----------+----------+

请注意，例如，id=283 现在在第 29、31 和 32 周具有 NULL，例如 id=281 在第 31 周具有 NULL。

我在这里还准备了 SQLFiddle:http://sqlfiddle.com/#!9/a8fdc/3

非常感谢。

Answer 1

我看了你的问题，然后想出了这个解决方案。您的查询如下所示:

SELECT t1.NUM_WEEK, t1.`YEAR`, t1.date_start, t1.id_account, t2.total
FROM (SELECT c.NUM_WEEK, c.`YEAR`, c.date_start, a.id_account
      FROM (SELECT WEEK(day_date,1) AS NUM_WEEK,
                   YEAR(day_date) AS `YEAR`,
                   STR_TO_DATE(CONCAT(YEAR(day_date),WEEK(day_date,1),' Monday'), '%X%V %W') AS date_start,
                   (SELECT GROUP_CONCAT(id_account) FROM account WHERE id_customer=12) AS accounts_id 
             FROM calendar
             GROUP BY NUM_WEEK) c
       INNER JOIN account a
       ON FIND_IN_SET(a.id_account, c.accounts_id)
       ORDER BY a.id_account, c.NUM_WEEK) t1
LEFT JOIN
     (SELECT WEEK(t.date_transaction,1) AS NUM_WEEK,
             YEAR(t.date_transaction) AS `YEAR`,
             STR_TO_DATE(CONCAT(YEAR(t.date_transaction),WEEK(t.date_transaction,1),' Monday'), '%X%V %W') AS date_start,
             t.id_account, SUM(t.amount) AS total 
      FROM `transaction` t
      INNER JOIN account a 
      ON a.id_account = t.id_account
      WHERE t.amount > 0 AND 
            t.id_account IN (SELECT id_account FROM account WHERE id_customer = 12)
      GROUP BY id_account, WEEK(date_transaction,1)) t2
 ON t1.NUM_WEEK = t2.NUM_WEEK AND t1.YEAR = t2.YEAR AND t1.id_account = t2.id_account;

这里是SQL Fiddle为此，您可以检查结果。希望这就是您所寻找的。

小解释:

首先我认为我所做的是我几乎没有修改您的第一个查询，您从表日历中提取数据并添加一个名为accounts_id的新列。该查询现在看起来像这样:

SELECT WEEK(day_date,1) AS NUM_WEEK,
       YEAR(day_date) AS `YEAR`,
       STR_TO_DATE(CONCAT(YEAR(day_date),WEEK(day_date,1),' Monday'), '%X%V %W') AS date_start,
       (SELECT GROUP_CONCAT(id_account) FROM account WHERE id_customer=12) AS accounts_id 
FROM calendar
GROUP BY NUM_WEEK

请注意SELECT语句中的这一行

(SELECT GROUP_CONCAT(id_account) FROM account WHERE id_customer=12) AS accounts_id

注意，当您选择特定客户时，您也需要更改此行中的客户 ID!!!

这里是Fiddle这样您就可以检查此查询产生的结果。

这是必要的，因为我们需要每周与每个帐户连接以获得所需的结果。

下一步是扩展先前的查询，以便我们可以分离accounts_id列(查看先前查询的结果)，以便我们可以获取该列中每个值的行。扩展查询如下所示:

SELECT c.NUM_WEEK, c.`YEAR`, c.date_start, a.id_account
FROM (SELECT WEEK(day_date,1) AS NUM_WEEK,
             YEAR(day_date) AS `YEAR`,
             STR_TO_DATE(CONCAT(YEAR(day_date),WEEK(day_date,1),' Monday'), '%X%V %W') AS date_start,
             (SELECT GROUP_CONCAT(id_account) FROM account WHERE id_customer=12) AS accounts_id 
       FROM calendar
       GROUP BY NUM_WEEK) c
INNER JOIN account a
ON FIND_IN_SET(a.id_account, c.accounts_id)
ORDER BY a.id_account, c.NUM_WEEK

您可以在 Fiddle 中看到输出

之后，我们需要做的就是在此查询和您已在问题中编写的查询(最后一个查询)之间进行左连接。

可能有更好的解决方案，甚至这个解决方案可能可以稍微改进，但我现在没有太多时间来处理这个问题，这是我想到的第一个想法......

GL!

P。 S.当你在MySQL中使用保留字，如YEAR、TRANSACTION等作为列名(如column_name)时，请注意..如果必须在列名或表名中使用它们，请使用反引号( ) to mark them (as 年` )...