mysql - 如何通过左连接正确获取计数？

Question

sqlfiddle上的结构表

如果我使用查询:

SELECT 
    ei.ItemIdn as ItemIdn,
    ei.UserIdn as UserIdn

FROM items ei

WHERE ei.UserIdn = '0000' 
LIMIT 0, 25

我得到 4 行，但如果我想获取每行的观看者数:

SELECT 
    ei.ItemIdn as ItemIdn,
    ei.UserIdn as UserIdn,
    ifnull(count(cv.ItemIdn),0) as CountShow

FROM items ei

    left join CountViewes as cv on ei.ItemIdn = cv.ItemIdn

WHERE ei.UserIdn = '0000' 
LIMIT 0, 25

我只得到一行。

请告诉我如何获取所有行和每行的 count(cv.ItemIdn) ？

Answer 1

我确实相信您的查询需要以 GROUP BY 结尾，因为它包含聚合函数。

GROUP BY ei.ItemIdn, ei.UserIdn

请参见此处:http://sqlfiddle.com/#!2/24d19/32/0