MYSQL - GROUP BY 2 COLUMN ORDER BY CREATION DATE [英] MYSQL - GROUP BY 2 COLUMN ORDER BY CREATION DATE

问题描述

我试图用2列的GROUP BY获取我的表的所有结果，并获取最后一次插入。

Prices表：

>

我的要求：

  SELECT DISTINCT p。* 
 FROM prices p 
 JOIN（
 SELECT MAX（created_at）aslast_created
 FROM价格
 WHERE station_id = 27210003 
 GROUP BY station_id，fuel_id）as sub 
 ON sub.last_created = p.created_at 
 WHERE p.station_id = 27210003 
 GROUP BY p.station_id，p.fuel_id 
  

结果是3行，但行错了，因为没有最后在我的DB -_-'

请帮助我！ > _<'

Thx

解决方案

WHERE 子句中的特定 station_id ，您不需要将它包含在 GROUP BY 。

子查询必须返回您正在分组的列，然后您必须将它们包含在 ON 子句。

您并不需要 GROUP BY 外部查询，因为 JOIN 应该确保每个 fuel_id 只有一行（除非重复 created_at 为相同的 fuel_id 和 station_id ）。

  SELECT DISTINCT p。* 
 FROM prices p 
 JOIN（
 SELECT fuel_id，MAX（created_at）as last_created
 FROM价格
 WHERE station_id = 27210003 
 GROUP BY fuel_id）as sub 
 ON sub.last_created = p.created_at AND sub.fuel_id = p.fuel_id 
在哪里p.station_i d = 27210003 
  

I'm trying to get all results of my table with a GROUP BY on 2 columns and get the last insertions.

"Prices" table :

My Request :

SELECT DISTINCT p.*
FROM prices p 
JOIN (
SELECT MAX(created_at) as "last_created" 
FROM prices
WHERE station_id = 27210003
GROUP BY station_id, fuel_id) as sub 
ON sub.last_created = p.created_at
WHERE p.station_id = 27210003
GROUP BY p.station_id, p.fuel_id

Results are 3 lines but wrong line because not last created in my DB -_-'

Please help me !!! >_<'

Thx

解决方案

Since you're selecting a specific station_id in the WHERE clause, you don't need to include that in GROUP BY.

The subquery has to return the columns you're grouping on, and then you have to include them in the ON clause.

And you don't need GROUP BY in the outer query, since the JOIN should ensure that there's just one row for each fuel_id (unless there are duplicate created_at for the same fuel_id and station_id).

SELECT DISTINCT p.*
FROM prices p 
JOIN (
    SELECT fuel_id, MAX(created_at) as "last_created" 
    FROM prices
    WHERE station_id = 27210003
    GROUP BY fuel_id) as sub 
ON sub.last_created = p.created_at AND sub.fuel_id = p.fuel_id
WHERE p.station_id = 27210003

这篇关于MYSQL - GROUP BY 2 COLUMN ORDER BY CREATION DATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！