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

ikfrs5lh  于 2023-04-19  发布在  Mysql
关注(0)|答案(1)|浏览(133)

没有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记录,经过反复试验,插入“组由”解决它。但这个“组由”是不是总结,但显示更多的结果比没有它。意想不到的结果和这样的背叛的意义。

t2a7ltrp

t2a7ltrp1#

使用GROUP BY时,查询结果中GROUP BY表达式的每个不同值对应一行。在您的示例中,PM.id的每个值对应一行。MAX()等聚合函数的结果将应用于与给定值关联的每个组中的行子集。
如果不使用GROUP BY,结果实际上是一个组,因此查询只返回一行。任何聚合函数都将应用于整个行集。
https://dev.mysql.com/doc/refman/8.0/en/select.html在那一长页的深处包含了这样一句话:
如果不存在GROUP BY,则所有行隐式地形成单个聚合组。

相关问题