sql根据多字段分组并查询每组内top1-CSDN博客

博客主要围绕数据库查询展开，要求查询2017年Q1季度从a_country发往其他城市货量最高的公司，并输出货量总和，同时还需在相同条件下查询公司b。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.如下表格，查询2017年Q1季度的从a_country发往其他城市的货量最高的公司，并输出货量总和

id	`from_country`	`to_country`	`company_name`	`company_count`	`year`	`quarter`	`month`

SELECT  * FROM(

	SELECT  sum(company_count) as top_count,company_name
	FROM testData
	WHERE `year`='2017' AND `quarter`='Q1' AND from_country='a_country'
	GROUP BY from_country,to_country,company_name,`year`,`quarter`
	ORDER BY company_count DESC) AS A

GROUP BY from_country,to_country

2.条件同1，同时查询公司b

SELECT * FROM

	(SELECT  * FROM(

		SELECT  sum(company_count) as top_count,company_name,company_count
		FROM testData
		WHERE `year`='2017' AND `quarter`='Q1' 
		GROUP BY from_country,to_country,company_name,`year`,`quarter`
		ORDER BY company_count DESC) AS A

	GROUP BY from_country,to_country) AS TopCompany

	INNER JOIN

	(SELECT  B_count,to_country FROM(

		SELECT  sum(company_count) as B_count,company_name as B,to_country
		FROM testData
		WHERE `year`='2017' AND `quarter`='Q1'  AND company_name='B'
		GROUP BY from_country,to_country,company_name,`year`,`quarter`
		ORDER BY company_count DESC) AS Bdata

	GROUP BY origin_country_name,dest_country_name) AS BCompany

ON TopCompany.to_country =BCompany.to_country

转载于:https://www.cnblogs.com/mufire/p/9534226.html