mysql - 合并两个查询而不遗漏行

Question

我有两个表，我需要这两个表中的记录。

查询 1

SELECT SUM(rec_issued) AS issed,
       regen_id,
       YEAR(issue_date) AS iYear,
       MONTH(issue_date) AS iMonth
FROM `view_rec_issued`
WHERE `regen_id` = 2
GROUP BY YEAR(issue_date) DESC, MONTH(issue_date) DESC
ORDER BY issue_date ASC

给出这个结果:

| issed | regen_id | iYear | iMonth |
-------------------------------------
|   424 |        2 |  2011 |      3 |
|  4340 |        2 |  2011 |      4 |
|  4235 |        2 |  2011 |      5 |
| 10570 |        2 |  2012 |      2 |
|  4761 |        2 |  2012 |      3 |
|  5000 |        2 |  2012 |      4 |
|  3700 |        2 |  2012 |      5 |
|  3414 |        2 |  2012 |      6 |
|  3700 |        2 |  2012 |      7 |
|  2992 |        2 |  2012 |      8 |
|   995 |        2 |  2012 |     10 |

查询 2

SELECT SUM(total_redem) AS redemed,
       regen_id,
       YEAR(redemption_date) AS rYear,
       MONTH(redemption_date) AS rMonth
FROM `recredem_month_wise`
WHERE `regen_id` = 2
GROUP BY YEAR(redemption_date) DESC, MONTH(redemption_date) DESC
order by redemption_date ASC

给出这个结果:

| redemed | regen_id | rYear | rMonth |
---------------------------------------
|     424 |        2 |  2011 |      3 |
|     260 |        2 |  2011 |      4 |
|    6523 |        2 |  2011 |      5 |
|    1070 |        2 |  2011 |      6 |
|     200 |        2 |  2011 |     10 |
|     500 |        2 |  2011 |     11 |
|    9750 |        2 |  2012 |      2 |
|    5000 |        2 |  2012 |      3 |
|    5500 |        2 |  2012 |      4 |
|    3803 |        2 |  2012 |      5 |
|    3700 |        2 |  2012 |      7 |
|    3000 |        2 |  2012 |      8 |

期望的结果

但我想要这样:

|  issed | regen_id |  iYear | iMonth | redemed | regen_id |  rYear | rMonth |
-------------------------------------------------------------------------------
|    424 |        2 |   2011 |      3 |     424 |        2 |   2011 |      3 |
|   4340 |        2 |   2011 |      4 |     260 |        2 |   2011 |      4 |
|   4235 |        2 |   2011 |      5 |    6523 |        2 |   2011 |      5 |
|   NULL |     NULL |   NULL |   NULL |    1070 |        2 |   2011 |      6 |
|   NULL |     NULL |   NULL |   NULL |     200 |        2 |   2011 |     10 |
|   NULL |     NULL |   NULL |   NULL |     500 |        2 |   2011 |     11 |
|  10570 |        2 |   2012 |      2 |    9750 |        2 |   2012 |      2 |
|   4761 |        2 |   2012 |      3 |    5000 |        2 |   2012 |      3 |
|   5000 |        2 |   2012 |      4 |    5500 |        2 |   2012 |      4 |
|   3700 |        2 |   2012 |      5 |    3803 |        2 |   2012 |      5 |
|   3414 |        2 |   2012 |      6 |    NULL |     NULL |   NULL |   NULL |
|   3700 |        2 |   2012 |      7 |    3700 |        2 |   2012 |      7 |
|   2992 |        2 |   2012 |      8 |    3000 |        2 |   2012 |      8 |
|    995 |        2 |   2012 |     10 |    NULL |     NULL |   NULL |   NULL |

在这些表中，regen_id 是唯一的，我需要数据作为 YEAR 和 MONTH。如果一个表在特定月份和年份没有任何记录，那么它应该检索零或 NULL。

但是在每个记录中年月应该是这样的-

 iYear = rYear and iMonth = rMonth

所以我们可以合并这两个字段——不需要两次显示年和月

iYear and rYear = year
iMonth and rMonth = month

---

更新

我试过这个查询，几乎解决了问题，但记录不是按“YEAR DESC，MONTH DESC”排序的形式。

SELECT DISTINCT A.issed, A.regen_id, A.iYear AS yrs, A.iMonth AS mnt, B.redemed, B.regen_id, B.rYear AS yrs, B.rMonth AS mnt
FROM(SELECT SUM(rec_issued) AS issed, regen_id, YEAR(issue_date) AS iYear, MONTH(issue_date) AS iMonth  
FROM `view_rec_issued` 
WHERE `regen_id` = 2 
GROUP BY YEAR(issue_date) DESC, MONTH(issue_date) DESC 
ORDER BY issue_date ASC) AS A 
LEFT JOIN 
(SELECT SUM(total_redem) AS redemed, regen_id, YEAR(redemption_date) AS rYear, MONTH(redemption_date) AS rMonth
FROM `recredem_month_wise` 
WHERE `regen_id` = 2 
GROUP BY YEAR(redemption_date) DESC, MONTH(redemption_date) DESC 
ORDER BY redemption_date ASC) AS B ON A.iYear = B.rYear AND A.iMonth = B.rMonth

UNION

SELECT DISTINCT A.issed, A.regen_id, A.iYear AS yrs, A.iMonth AS mnt, B.redemed, B.regen_id, B.rYear AS yrs, B.rMonth AS mnt
FROM(SELECT SUM(rec_issued) AS issed, regen_id, YEAR(issue_date) AS iYear, MONTH(issue_date) AS iMonth  
FROM `view_rec_issued` 
WHERE `regen_id` = 2 
GROUP BY YEAR(issue_date) DESC, MONTH(issue_date) DESC 
ORDER BY issue_date ASC) AS A 
RIGHT JOIN 
(SELECT SUM(total_redem) AS redemed, regen_id, YEAR(redemption_date) AS rYear, MONTH(redemption_date) AS rMonth 
FROM `recredem_month_wise` 
WHERE `regen_id` = 2 
GROUP BY YEAR(redemption_date) DESC, MONTH(redemption_date) DESC 
ORDER BY redemption_date ASC) AS B ON A.iYear = B.rYear AND A.iMonth = B.rMonth

Answer 1

您所描述的称为 FULL OUTER JOIN:您需要两个表中的所有行，如果未找到匹配项，则另一个表填充 NULL。不幸的是，MySQL 不提供完整的外部连接，因此您必须模拟它们。

另见 Full Outer Join in MySQL .

以规范的方式模拟完整的外部连接

一种编写方法如下，它为丢失的匹配项生成 NULL。

(SELECT I.iYear AS `year`, I.iMonth AS `month`, I.issed, R.redeemed
 FROM (
  SELECT SUM(rec_issued) AS issed,
         regen_id,
         YEAR(issue_date) AS iYear,
         MONTH(issue_date) AS iMonth
  FROM `view_rec_issued`
  WHERE `regen_id` = 2
  GROUP BY YEAR(issue_date), MONTH(issue_date)
 ) I
 LEFT JOIN (
  SELECT SUM(total_redem) AS redemed,
         regen_id,
         YEAR(redemption_date) AS rYear,
         MONTH(redemption_date) AS rMonth
  FROM `recredem_month_wise`
  WHERE `regen_id` = 2
  GROUP BY YEAR(redemption_date), MONTH(redemption_date)
 ) R
 ON (I.iYear = R.rYear AND I.iMonth = R.rMonth)
)
UNION DISTINCT
(SELECT R.iYear AS `year`, R.iMonth AS `month`, I.issed, R.redeemed
 FROM (
  SELECT SUM(total_redem) AS redemed,
         regen_id,
         YEAR(redemption_date) AS rYear,
         MONTH(redemption_date) AS rMonth
  FROM `recredem_month_wise`
  WHERE `regen_id` = 2
  GROUP BY YEAR(redemption_date), MONTH(redemption_date)
 ) R
 LEFT JOIN (
  SELECT SUM(rec_issued) AS issed,
         regen_id,
         YEAR(issue_date) AS iYear,
         MONTH(issue_date) AS iMonth
  FROM `view_rec_issued`
  WHERE `regen_id` = 2
  GROUP BY YEAR(issue_date), MONTH(issue_date)
 ) I
 ON (I.iYear = R.rYear AND I.iMonth = R.rMonth)
)
ORDER BY `year` ASC, `month` ASC

您可以通过为其中包含的子查询编写 View 来缩短它，但是如果您希望能够更改 regen_id，那么这仍然是相当庞大的代码。

与零并集

您可以通过完全避免外部联接并使用不同的联合来使事情变得容易得多，像这样将零填充到行中:

SELECT YEAR(`date`) AS `year`, MONTH(`date`) AS `month`,
       SUM(rec_issued) AS issued, SUM(total_redem) AS redeemed
FROM (
    SELECT issue_date AS `date`, rec_issued, 0 AS total_redem
    FROM view_rec_issued WHERE regen_id = 2
  UNION ALL
    SELECT redemption_date AS `date`, 0 AS rec_issued, total_redem
    FROM redemption_month_wise WHERE regen_id = 2
  ) AS U
GROUP BY EXTRACT(YEAR_MONTH FROM `date`) ASC

Left join everything to subquery finding months

下表进行外部联接模拟只是为了查找任一表中出现的日期。然后它进行左连接以计算总和。因此，对于缺失值，它将返回零而不是 NULL。

SELECT LEFT(DI.ym, 4) + 0 AS `year`, RIGHT(DI.ym, 2) + 0 AS `month`,
       DI.issed, SUM(R.total_redem) AS redeemed
FROM
 (SELECT D.ym, SUM(I.rec_issued) AS issed
  FROM
   (SELECT DISTINCT EXTRACT(YEAR_MONTH FROM issue_date) as ym
    FROM view_rec_issued
    WHERE regen_id = 2
   UNION DISTINCT
    SELECT DISTINCT EXTRACT(YEAR_MONTH FROM redemtion_date) AS ym
    FROM recredem_month_wise
    WHERE regen_id = 2
   ) AS D
  LEFT JOIN view_rec_issued AS I
         ON I.regen_id = 2
        AND EXTRACT(YEAR_MONTH FROM I.issue_date) = D.ym
 ) AS DI
LEFT JOIN recredem_month_wise AS R
       ON R.regen_id = 2
      AND EXTRACT(YEAR_MONTH FROM R.redemtion_date) = DI.ym
GROUP BY DI.ym ASC

与上面的查询相比，这将不得不对每个表多看两次，所以它可能会更慢。如果您担心性能，只需尝试两者并对其进行基准测试。就代码长度而言，这里的查询要紧凑得多，我相信也更具可读性。

注意上面的查询在 past revision of this post 中是不同的.该版本省略了子查询 DI。结果更易读，但不幸的是错误:如果一个月内有两期和三次赎回，那么该连接会将其合并为该月的六行，从而将每一期计算三次并且每次赎回两次。

到目前为止，所有查询都未经测试。如果您的问题带有关于 http://sqlfiddle.com/ 的一些样本数据，那么我会先在那里尝试我的查询，然后再将它们发布到这里。