mysql 显示每行中来自其他表的行数

mysql show Count of rows from other table in each row(mysql 显示每行中来自其他表的行数)

问题描述

select `personal`.`id` AS `id`,
`personal`.`name` AS `name`,
(select count(visit.id) 
      from visit,personal 
        where visit.user_id=personal.id) as count 
from personal;

我正在尝试获取所有用户及其访问次数.

im trying to get all users and the counts of visits they did.

我得到的结果是所有用户，但计数列包含相同的值(不特定于该行 ID).

the result i get is all users but the count column contain same value (not specific to that row id).

我在这里做错了什么?如何告诉 mysql 使用此行 ID?

what am i doing wrong here ? how to tell mysql to user this row id ?

复合选择最佳方法还是有更好的方法?

is compound select optimum way to do it or is there a better way ?

推荐答案

SELECT  p.id, p.name, COUNT(v.user_id)
FROM    personal p
LEFT JOIN
        visit v
ON      v.user_id = p.id
GROUP BY
        p.id

当然，您也可以使用子选择(例如，如果您具有 ANSI GROUP BY 兼容性):

You may also use subselect of course (for instance if you have ANSI GROUP BY compatibility on):

SELECT  p.id, p.name,
        (
        SELECT  COUNT(*)
        FROM    visit v
        WHERE   v.user_id = p.id
        )
FROM    personal p

这篇关于mysql 显示每行中来自其他表的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！