联接表以获取与另一列的出现次数 - Joining tables to get the number of occurences with another column

Joining tables to get the number of occurences with another column

本文关键字：一列获取 | 更新日期: 2023-09-27

我有两张表。一个被称为subscriptions，另一个被称作service_sushi_service。我正在试着弄到所有有两个以上不同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

我需要将其输出为电话=12345，发生次数=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`

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

我在这个代码中看到了两件事：

你为什么要做LEFT JOIN？我认为你需要一个直的INNER JOIN。
您可以使用count(distinct field_name)来获得相应的值。

因此SQL可以是这样的：

SELECT 
    `sub`.`phone` AS `phone`,
     count(distinct `sushi_service`.`sushi_service_id`) as `occurrences`
FROM 
    `subscriptions` AS `sub`
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 `occurrences` > 2

根本不需要连接

select s1.phone, count(s1.*) from 
    (select unique phone, sushi_service_id from subscriptions) s1
group by s1.phone having count(s1.*) > 2