使用 group by 子句的 MySQL 查询返回的行数比不使用 group by 子句时返回的行数多

Question

没有group by子句，只返回1条记录；其中，返回所需的记录数

SELECT 
PM.id AS id, PM.email AS email, PM.name AS name, PM.mobile AS mobile, 
PM.country AS country, PM.status AS status, PM.cdate AS joined_date, 
PM.details_status AS details_status, PM.freeze AS freeze, PM.blacklist AS blacklist, 
PM.blacklist_remark AS blacklist_remark, PM.blacklist_adate AS blacklist_adate, 
MA.m_app_id AS app_id, MA.full_name AS replace_name, MAX(MA.ldate) AS ldate, 
MA.service_type AS svc_type 

FROM whatever_db.tw_person_merchant PM 
    LEFT JOIN (
        whatever_db.tw_person_merchant PMM
        INNER JOIN
        whatever_db.tw_merchant_application_details MA 
        ON PMM.app_id = MA.m_app_id
        INNER JOIN 
        whatever_db.tw_merchant_application MAP 
        ON MA.m_app_id = MAP.id
        AND MAP.status NOT IN ('10' , '60')
        AND MA.id = (SELECT max(id) FROM whatever_db.tw_merchant_application_details WHERE m_app_id = PMM.app_id )
    )  ON PM.app_id = MA.m_app_id 

WHERE PM.details_status IN ('90', '0')
AND PM.blacklist = 0 
AND PM.name != ''

-- **here, without the group by clause, the result is just 1 record
GROUP BY PM.id 
ORDER BY PM.id DESC

我正在测试sql脚本是否有相关商家返回，但中间只显示1条记录，经过反复试验，插入的“group by”解决了这个问题。但这个“group by”不是总结，而是显示比没有它更多的结果。意想不到的结果和如此背叛理智的行为。

Answer 1

当您使用GROUP BY时，查询结果对于GROUP BY表达式的每个不同值都有一行。在您的情况下，PM.id 的每个值占一行。诸如 MAX() 之类的聚合函数的结果将应用于与给定值关联的每个组中的行子集。

如果不使用GROUP BY，则结果实际上是一组，因此查询仅返回一行。任何聚合函数都会应用于整个行集。

https://dev.mysql.com/doc/refman /8.0/en/select.html 在那个长页面深处包含了这句话：