php - 连接表以获取与另一列的出现次数

Question

我有 2 张 table 。一个称为 subscriptions，另一个称为 service_sushi_service。我正在尝试获取所有具有 2 个以上不同 service_id 的电话。例如。

phone: 12345 service id: 12 
phone: 12345 service id: 12 
phone: 12345 service id: 8
phone: 12345 service id: 8
phone: 12345 service id: 13
phone: 22222 service id: 13
phone: 22333 service id: 3

我需要将其输出为 phone=12345，occurences=3

这是我目前得到的结果:我不知道如何更进一步。

SELECT 
    `sub`.`phone` AS `phone`
FROM 
    `subscriptions` AS `sub`
LEFT JOIN `service_sushi_service` `sushi_service` ON `sushi_service`.`sushi_service_id` = `sub`.`sushi_service_id`
WHERE 
    date(`sub`.`added`) >= '2013-01-01'
GROUP BY `sub`.`phone`

Answer 1

SELECT 
    `sub`.`phone` AS `phone`,
    COUNT(`sushi_service`.`sushi_service_id`) as occurences        
FROM 
    `subscriptions` AS `sub`
LEFT JOIN `service_sushi_service` `sushi_service` ON `sushi_service`.`sushi_service_id` = `sub`.`sushi_service_id`
WHERE 
    date(`sub`.`added`) >= '2013-01-01'
GROUP BY `sub`.`phone`
HAVING occurences > 2

编辑:应该指出 HAVING 似乎是 MySQL 唯一的东西。我想我会发布这个供您引用 - http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html