MySQL多个联接到同一个表 [英] MySQL multiple joins to same table

问题描述

我正在尝试改善跟踪网站中最受欢迎路径的程序的性能.给定的URL在表sessionpage中注册，并带有unique_id:

I am attempting to improve the performance of a program that tracks the most popular paths taken through a website. a given URL is registered in table sessionpage with a unique_id:

+-----------+--------------------------+---------------------+
| unique_id | page_url                 | mod_date_stamp      |
+-----------+--------------------------+---------------------+
|      2378 | /resources/series75.html | 2008-10-03 22:49:00 |
+-----------+--------------------------+---------------------+

然后，另一个表session使用sessionpage中的unique_id计算一组最多5个网址的命中次数.

Then another table, session, counts the number of hits for a set of up to 5 urls using the unique_id from sessionpage.

会话

+-----------+---------+-----------------+-----------+---------------------+-------
| unique_id | counter | sequence_length | yearmonth | mod_date_stamp      | page1 | 
+-----------+---------+-----------------+-----------+---------------------+-------
|         1 |       2 |               2 |    201203 | 2012-03-28 15:42:38 |  5298 |  
+-----------+---------+-----------------+-----------+---------------------+-------

+-------+-------+-------+-------+
page2 | page3 | page4 | page5 |
+-------+-------+-------+-------+
6075 |     0 |     0 |     0 |
+-------+-------+-------+-------+

现在，stats程序正在从会话页面获取每个URL的unique_id，这花费了太长时间.我想多次将sessionpage加入到session中，以便page1-page5字段显示url路径(或0/null)，而不是sessionpage中的unique_id，以加快程序运行速度.

Right now, the stats program is fetching the unique_id from session page for each URL, which is taking way too long. I want to join sessionpage back to session multiple times so that the page1 - page5 fields show the url path (or 0/null) rather than the unique_id from sessionpage to speed up the program.

最终结果应类似于:

+-----------+---------+-----------------+-----------+---------------------+-------
| unique_id | counter | sequence_length | yearmonth | mod_date_stamp      | page1 | 
+-----------+---------+-----------------+-----------+---------------------+-------
|         1 |       2 |               2 |    201203 | 2012-03-28 15:42:38 |  /path/index.html |  
+-----------+---------+-----------------+-----------+---------------------+-------

+-------+-------+-------+-------+
page2            | page3 | page4 | page5 |
+-------+-------+-------+-------+
/path3/disk.html |     0 |     0 |     0 |
+-------+-------+-------+-------+

谢谢.

推荐答案

根据您的评论，page1，page2 ..是外键列，我认为这很简单.

Based on your comment that page1, page2.. are foreign keys columns, I think it's simple.

类似的东西:

SELECT 
    s.unique_id, s.counter, s.sequence_length, s.yearmonth, 
    s.mod_date_stamp,
    pg1.page_url as page1, pg2.page_url as page2, 
    pg3.page_url as page3, pg4.page_url as page4, 
    pg5.page_url as page5
FROM 
    session s
LEFT JOIN 
    sessionpage pg1 ON pg1.unique_id = s.page1 
LEFT JOIN 
    sessionpage pg2 ON pg2.unique_id = s.page2
LEFT JOIN 
    sessionpage pg3 ON pg3.unique_id = s.page3
LEFT JOIN 
    sessionpage pg4 ON pg4.unique_id = s.page4
LEFT JOIN 
    sessionpage pg5 ON pg5.unique_id = s.page5

我认为这会起作用.

但是，如果列上没有值(在表会话中)，则应使用null而不是0(零).

But when there is no value on column (on table session), you should use null instead of 0 (zero).

关于SELECT语句的另一个提示，如果您不希望使用NULL值，则可以使用:

Another tip, on the SELECT statement, if you don't want NULL values you can use:

COALESCE(pg5.page_url, ' ') as page5

这篇关于MySQL多个联接到同一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！