使用 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 在那個長頁深處包含了這句話：