mysql - 使用三个表计算行数

Question

我有三个mysql数据库表作为

 1) websites
+-----+--------------------+
|  id |website             |
+-----+--------------------+
|   1 | http://abcd.com    |
|   2 | http://xyz.com     |
|   3 | http://pqrs.com    | 
+-----+--------------------+

 2) pages
+-----+---------------------------------------+
|  id |page                                   |
+-----+---------------------------------------+
|   1 | http://abcd.com/index.php             |
|   2 | http://abcd.com/contact.php           |
|   3 | http://xyz.com /index.php             | 
|   4 | http://pqrs.com /index.php            |
+-----+---------------------------------------+

 3) statistics
+-----+-------------------+
|  id |page_id | type     |
+-----+-------------------+
|   1 | 1      | AOL      |
|   2 | 1      | YAHOO    |
|   3 | 2      | AOL      | 
|   4 | 3      | YAHOO    |
|   5 | 3      | YAHOO    |
|   6 | 4      | YAHOO    |
+-----+-------------------+

我希望输出为:

+-------------------+--------------------+
|website            | count_hit_by_AOL   | 
+-------------------+--------------------+
|http://abcd.com    |      2             |
|http://xyz.com     |      0             |
|http://pqrs.com    |      0             |
+-------------------+--------------------+

为了获得此输出，我使用以下 mysql 查询--

SELECT
  COUNT(statistics.id) as count_stats,
  websites.website
FROM websites,
  pages,
  statistics
WHERE pages.page LIKE CONCAT(websites.website,'%')
    AND pages.id = statistics.page_id
    and statistics.type LIKE 'AOL%'
GROUP BY websites.website
ORDER BY count_stats DESC

我得到的输出是

+-------------------+--------------------+
|website            | count_hit_by_AOL   | 
+-------------------+--------------------+
|http://abcd.com    |      2             |
+-------------------+--------------------+

如果我做错了什么，请帮助我，因为我是 MYSQL 的新手。

Answer 1

现有查询的问题是您在表上使用了 INNER JOIN，因此您将只返回匹配的行。

既然你想返回所有 website 行，那么你会想使用 LEFT JOIN:

SELECT w.website, count(s.id) Total
FROM websites w
LEFT JOIN pages p
  on p.page like CONCAT(w.website,'%')
LEFT JOIN statistics  s
  on p.id = s.page_id
  and s.type like 'AOL%'
group by w.website

参见 SQL Fiddle with Demo .结果是:

|         WEBSITE | TOTAL |
---------------------------
| http://abcd.com |     2 |
| http://pqrs.com |     0 |
|  http://xyz.com |     0 |

编辑#1，如果你想包括每个网站的总页数，那么你可以使用:

SELECT w.website, 
  count(s.id) TotalStats,
  count(p.page) TotalPages
FROM websites w
LEFT JOIN pages p
  on p.page like  CONCAT (w.website,'%')
LEFT JOIN statistics  s
  on p.id = s.page_id
  and s.type like 'AOL%'
group by w.website

参见 SQL Fiddle with Demo .这个查询的结果是:

|         WEBSITE | TOTALSTATS | TOTALPAGES |
---------------------------------------------
| http://abcd.com |          2 |          2 |
| http://pqrs.com |          0 |          1 |
|  http://xyz.com |          0 |          1 |