sql relation显示ther表的每个记录的sum（values）

I have 2 tables 1 for web-pages detail and the other is for visits as below

我有2个表1用于网页详细信息,另一个用于访问如下

PAGES

pid pname      and-more-columns
1   index      -----------
2   contact    -----------
3   about      -----------

VISITS

vid   pid    ip          datetime
1      1    222.1.1.1     2013-01-01 11:11:11
2      1    12.12.2.1     2013-01-01 11:21:11 
3      3    222.1.1.1     2013-01-02 10:11:11
4      2    12.12.2.1     2013-02-01 11:21:11 
5      3    222.1.1.1     2013-03-01 11:14:11
6      1    12.12.2.1     2013-04-01 11:55:11

I want to display as below

我想显示如下

pid pname      and-more-columns   total_visits (shud count from VISITS table)
1   index      -----------           3 
2   contact    -----------           1
3   about      -----------           2

3 个解决方案

#1

Assuming you want "0" where there is no match, then you want a left outer join:

假设你想要“0”没有匹配,那么你想要一个左外连接:

SELECT p.*, COUNT(v.pid)
FROM PAGES p left outer join
     VISITS v
     ON p.pid = v.pid
GROUP BY p.pid

Note that this is grouping by only one field in the group by clause. This uses a MySQL (mis)feature called "hidden columns". This version also assumes that the pages.pid field is unique.

请注意,这只是group by子句中的一个字段的分组。这使用称为“隐藏列”的MySQL(mis)功能。此版本还假定pages.pid字段是唯一的。

#2

SELECT p.*, COUNT(v.pid)
FROM PAGES p
INNER JOIN VISITS v
ON p.pid = v.pid
GROUP BY p.pid, p.pname, <list all other columns here>

#3

SELECT
    v.*,
    count(p.pid) AS `TotalVisits`
FROM PAGES AS p
INNER JOIN VISITS AS v ON v.pid = p.pid
GROUP BY p.pid

#1