mysql - 复杂的 MySQL 查询求和连接的记录，其中父 ID 和子 ID 相等

Question

对于你们中的一些 MySQL 大师来说，这可能是一本简单的书，他们将这本书视为 3 级 child 读物。

我有多个表，我正在加入这些表来为报告生成统计数据，但我现在正试图弄清楚它。显然，这些数字必须正确，因为它会影响 future 的许多决策。

这是地形图(不是全貌，但您会明白要点):

关联表

+----+-----------+------------+---------------------+
| id | firstname | lastname   | created_date        |
+----+-----------+------------+---------------------+
|  1 | Mike      | Johnson    | 2010-11-22 17:44:37 |
|  2 | Trevor    | Wilson     | 2010-12-23 16:24:24 |
|  3 | Bob       | Parker     | 2011-11-04 10:33:49 |
+----+-----------+------------+---------------------+

现在我们的查询应该只查找 Bob Parker (id 3) 的结果，所以我将只显示 Bob 的示例结果。

附属链接表

+-----+-----------+--------------+-----------+----------+---------------------+
| id  | parent_id | affiliate_id | link_type | linkhash | created_date        |
+-----+-----------+--------------+-----------+----------+---------------------+
|  21 |      NULL |            3 | PRODUCT   | fa2e82a7 | 2011-06-15 16:18:37 |
|  27 |      NULL |            3 | PRODUCT   | 55de2ae7 | 2011-06-23 01:03:00 |
|  28 |      NULL |            3 | PRODUCT   | 02cae72f | 2011-06-23 01:03:00 |
|  29 |        27 |            3 | PRODUCT   | a4dfb2c8 | 2011-06-23 01:03:00 |
|  30 |        28 |            3 | PRODUCT   | 72cea1b2 | 2011-06-23 01:03:00 |
|  36 |        21 |            3 | PRODUCT   | fa2e82a7 | 2011-06-23 01:07:03 |
|  59 |        21 |            3 | PRODUCT   | ec33413f | 2011-11-04 17:49:17 |
|  60 |        27 |            3 | PRODUCT   | f701188c | 2011-11-04 17:49:17 |
|  69 |        21 |            3 | PRODUCT   | 6dfb89fd | 2011-11-04 17:49:17 |
+-----+-----------+--------------+-----------+----------+---------------------+

成员(member)统计

+--------+--------------+--------------------+----------+---------------------+
| id     | affiliate_id | link_id | order_id | type     | created_date        |
+--------+--------------+---------+----------+----------+---------------------+
|  86570 |            3 |      21 |     NULL | CLICK    | 2013-01-01 00:07:31 |
|  86574 |            3 |      21 |     NULL | PAGEVIEW | 2013-01-01 00:08:53 |
|  86579 |            3 |      21 |      411 | SALE     | 2013-01-01 00:09:52 |
|  86580 |            3 |      36 |     NULL | CLICK    | 2013-01-01 00:09:55 |
|  86582 |            3 |      36 |     NULL | PAGEVIEW | 2013-01-01 00:09:56 |
|  86583 |            3 |      28 |     NULL | CLICK    | 2013-01-01 00:11:04 |
|  86584 |            3 |      28 |     NULL | PAGEVIEW | 2013-01-01 00:11:04 |
|  86586 |            3 |      30 |     NULL | CLICK    | 2013-01-01 00:30:18 |
|  86587 |            3 |      30 |     NULL | PAGEVIEW | 2013-01-01 00:30:20 |
|  86611 |            3 |      69 |     NULL | CLICK    | 2013-01-01 00:40:19 |
|  86613 |            3 |      69 |     NULL | PAGEVIEW | 2013-01-01 00:40:19 |
|  86619 |            3 |      69 |      413 | SALE     | 2013-01-01 00:42:12 |
|  86622 |            3 |      60 |     NULL | CLICK    | 2013-01-01 00:46:00 |
|  86624 |            3 |      60 |     NULL | PAGEVIEW | 2013-01-01 00:46:01 |
|  86641 |            3 |      60 |     NULL | PAGEVIEW | 2013-01-01 00:55:58 |
|  86642 |            3 |      30 |      415 | SALE     | 2013-01-01 00:56:35 |
|  86643 |            3 |      28 |     NULL | PAGEVIEW | 2013-01-01 00:56:43 |
|  86644 |            3 |      60 |      417 | SALE     | 2013-01-01 00:56:52 |
+--------+--------------+---------+----------+----------+---------------------+

订单

+------+--------------+---------+---------------------+
| id   | affiliate_id | total   | created_date        |
+------+--------------+---------+---------------------+
|  411 |            3 |  138.62 | 2013-01-01 00:09:50 |
|  413 |            3 |  312.87 | 2013-01-01 00:09:52 |
|  415 |            3 |  242.59 | 2013-01-01 00:09:55 |
|  417 |            3 |  171.18 | 2013-01-01 00:09:55 |
+------+--------------+---------+---------------------+

现在我需要的结果应该是这样的(只显示主/父链接 ID)

+---------+---------+
| link_id | total   |
+---------+---------+
|      21 |  451.49 | <- 1 order from parent (21), 1 from child (69)
|      27 |  171.18 | <- 1 order from child (69)
|      28 |  242.59 | <- 1 order from child (30)
+---------+---------+

我不太确定如何编写查询以便我可以对 affiliate_link.id 和 affiliate_link.parent_id 的合并位置求和。这甚至可以通过几个 JOIN 和 GROUPing 实现吗？

Answer 1

我不太确定为什么要对 affiliate_id 进行非规范化(通过将其放在每个表中)，因此，是否可以依赖源自特定链接的所有统计数据和订单来拥有与该链接相同的 affiliate_id。

如果可能，我建议更改 AffiliateLink.parent_id 列，使父记录指向它们自己(而不是 NULL):

UPDATE AffiliateLink SET parent_id = id WHERE parent_id IS NULL

然后是一个简单的连接和分组的例子:

SELECT   AffiliateLink.parent_id AS link_id,
         SUM(Orders.total) AS total
FROM     AffiliateLink
    JOIN AffiliateStats ON AffiliateStats.link_id = AffiliateLink.id
    JOIN Orders         ON Orders.id = AffiliateStats.order_id
WHERE    AffiliateLink.affiliate_id = 3
GROUP BY AffiliateLink.parent_id

查看sqlfiddle .

如果无法进行更改，您可以使用 UNION 有效地创建生成的 AffiliateLink 表(但要注意性能影响，因为 MySQL 将无法在结果上使用索引):

(
  SELECT parent_id, id, affiliate_id FROM AffiliateLink WHERE parent_id IS NOT NULL
UNION ALL
  SELECT id       , id, affiliate_id FROM AffiliateLink WHERE parent_id IS NULL
) AS AffiliateLink

查看sqlfiddle .