复杂的MySQL查询汇总了父和子ID相等的连接记录(Complex MySQL query summing joined records where parent and child ids are

复杂的MySQL查询汇总了父和子ID相等的连接记录(Complex MySQL query summing joined records where parent and child ids are equal)

也许这对于你们中的一些MySQL大师来说很容易，他们会把这些东西看作3级儿童书。

我有多个表格，我正在加入这些表格来生成报告的统计数据，而我现在正试图解决这个问题。显然，这些数字是正确的，因为它会影响未来的一系列决策。

这是土地的位置（不是完整的图片，但你会明白这一点）：

会员表

+----+-----------+------------+---------------------+ | 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 | +-----+-----------+--------------+-----------+----------+---------------------+

会员统计

+--------+--------------+--------------------+----------+---------------------+ | 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可以实现这一点吗？

Maybe this will be an easy one for some of you MySQL masters who see this stuff like a level 3 children's book.

I have multiple tables that I'm joining to produce statistical data for a report and I'm getting tripped up at the moment trying to figure it out. It's obviously imperative the figures are correct because it impacts a number of decisions going forward.

Here's the lay of the land (not the full picture, but you'll get the point):

Affiliate Table

+----+-----------+------------+---------------------+ | 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 | +----+-----------+------------+---------------------+

Now our query should only find results for Bob Parker (id 3) so I'll only show example results for Bob.

Affiliate Link Table

+-----+-----------+--------------+-----------+----------+---------------------+ | 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 | +-----+-----------+--------------+-----------+----------+---------------------+

Affiliate Stats

+--------+--------------+--------------------+----------+---------------------+ | 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 | +--------+--------------+---------+----------+----------+---------------------+

Orders

+------+--------------+---------+---------------------+ | 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 | +------+--------------+---------+---------------------+

Now the results that I need should look like this (only show main/parent link 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) +---------+---------+

I'm not quite sure how to write the query so that I can sum where affiliate_link.id and affiliate_link.parent_id are combined. Is this even possible with a couple of JOINs and GROUPing?

最满意答案

我不太确定为什么你有非规范化的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上看到它。

I'm not too sure why you have denormalised affiliate_id (by placing it in each table) and, therefore, whether one can rely on all Stats and Orders that stem from a particular Link to have the same affiliate_id as that Link.

If it's possible, I'd suggest changing the AffiliateLink.parent_id column such that parent records point to themselves (rather than NULL):

UPDATE AffiliateLink SET parent_id = id WHERE parent_id IS NULL

Then it's a simple case of joining and grouping:

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

See it on sqlfiddle.

If it's not possible to make the change, you can effectively create the resulting AffiliateLink table using UNION (but beware the performance implications, as MySQL will not be able to use indexes on the result):

( 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

See it on sqlfiddle.

更多推荐