MySQL计数内部联接查询 - MySQL - count inner join query

MySQL - count inner join query

本文关键字：查询内部 MySQL | 更新日期: 2023-10-07

我有下面的查询，它的工作方式与我希望的一样，只是我想返回一个pseudo（？）列，该列包含每个属性从内部联接匹配的单元数。其中p.team=u.team和u.deleted='0000-00-00 00:00:00'和u.rates！='"0"。我可以在同一个查询中执行此操作吗？

一处房产有几个单元。他们都附属于一个"团队"（想想用户名）。

SELECT DISTINCT p.title, p.state, p.city, p.regionID, p.team, p.type, p.lat, p.lng, p.url_title 
        FROM Properties AS p
        INNER JOIN Units AS u ON p.team = u.team
        INNER JOIN Rates AS r ON p.team = r.team
        INNER JOIN Photos AS ph ON p.team = ph.team
        AND p.public   = '1'
        AND u.rates   != '0'
        AND p.deleted  =  '0000-00-00 00:00:00'
        AND u.deleted  =  '000-00-00 00:00:00'
        AND r.deleted  =  '000-00-00 00:00:00'
        AND ph.deleted =  '000-00-00 00:00:00'
        GROUP BY p.id
        ORDER BY p.created ASC

由于您只有一个聚合，并且似乎是按Properties行进行分组，因此这应该相对容易。你的意思是这样的（未经测试的）？

SELECT p.title, p.state, p.city, p.regionID, p.team, p.type, p.lat, p.lng, 
       p.url_title , COUNT(u.*) AS number_of_units
FROM Properties AS p
INNER JOIN Units AS u ON p.team = u.team
INNER JOIN Rates AS r ON p.team = r.team
INNER JOIN Photos AS ph ON p.team = ph.team
AND p.public   = '1'
AND u.rates   != '0'
AND p.deleted  =  '0000-00-00 00:00:00'
AND u.deleted  =  '000-00-00 00:00:00'
AND r.deleted  =  '000-00-00 00:00:00'
AND ph.deleted =  '000-00-00 00:00:00'
GROUP BY p.id, p.title, p.state, p.city, p.regionID, p.team, p.type, p.lat, p.lng, p.url_title
ORDER BY p.created ASC

根据您的意思，您可能不得不执行COUNT(DISTINCT(u.id))。很难从你的问题中辨别出来。

作为参考，"困难的方法"是创建一个子查询来连接以进行计数。你可能需要这样做来计算多个不相关的项目，或者按一件事分组，按另一件事计数：

SELECT DISTINCT p.title, p.state, p.city, p.regionID, p.team, p.type, p.lat, p.lng,
                p.url_title, uuCount.number_of_units
FROM Properties AS p
INNER JOIN Units AS u ON p.team = u.team
INNER JOIN Rates AS r ON p.team = r.team
INNER JOIN Photos AS ph ON p.team = ph.team
AND p.public   = '1'
AND u.rates   != '0'
AND p.deleted  =  '0000-00-00 00:00:00'
AND u.deleted  =  '000-00-00 00:00:00'
AND r.deleted  =  '000-00-00 00:00:00'
AND ph.deleted =  '000-00-00 00:00:00'
INNER JOIN 
(
    SELECT uu.team, COUNT(*) AS number_of_units
    FROM Units uu
    WHERE uu.deleted = '0000-00-00 00:00:00'
    AND u.rates != '0'
    GROUP BY uu.team
) AS uuCount ON p.team = uuCount.team
GROUP BY p.id
ORDER BY p.created ASC