思路：t1 , t2 产生笛卡尔积得到每一个人对应每一个国家，然后把t3表左连接再配合IFNULL即可 SELECT t1.name_id , t2.order_county , IFNULL(num,0) AS order_counts FROM ( SELECT DISTINCT name_id FROM sell_information ) t1 JOIN ( SELECT DISTINCT order_county FROM sell_information ) t2 LEFT JOIN ( SELECT name_id , order_county , COUNT(order_id) AS 'num' FROM sell_information GROUP BY name_id , order_county ) t3 ON t3.name_id = t1.name_id AND t3.order_county = t2.order_county ORDER BY name_id,order_county;