php - 在 MYSQL 中按出现次数对两列进行分组并按时间排序

Question

我正在处理一个表格，其中包含将由激光切割机处理的上传文件列表。

为了提高效率，我需要按照出现多少相同组合的顺序列出具有相同 Material 和颜色的文件。然后在发送时订购。

这是一个非常简化的表格示例:

从上面的示例表我想实现以下结果:

6    File Number 6    Plastic    Red     9am
4    File Number 4    Plastic    Red     10am
5    File Number 5    Plastic    Red     10:30am
1    File Number 1    Card       Blue    9am
2    File Number 2    Card       Blue    9:30am
7    File Number 7    Plastic    Purple  8am
3    File Number 3    Card       Green   9am

因此，在 Material 和颜色出现次数最多的地方，相应的行位于顶部，其中最早的文件位于顶部。

如您所见，红色塑料出现的次数最多，因此出现在顶部。单个文件仅按时间排序，如文件 7 和 3 所示。

理想情况下，我想在一个 MYSQL 查询中执行此操作，但考虑到它的复杂性，我不确定这是否可行。也许存储结果并循环遍历数组？

我几乎已经通过这个查询实现了这一点:

SELECT * 
FROM propellor.pro_files 
GROUP BY material, colour 
ORDER BY count(*) DESC, 
   material DESC, 
   colour DESC, 
   sent DESC;

虽然这似乎排序几乎正确，但它只为每种 Material /颜色组合返回单行。

值得注意的是颜色列可以是null。

关于我如何对它们进行分组的想法，下面是它在实践中的样子:

更新: Material /颜色组现在已正确排序，但当我需要时，单个文件会按从新到旧的顺序排列，我试过使用发送的 ASC/DESC，但它只影响组中的文件。

更新 2:这是我当前的完整 MYSQL 查询:

SELECT * 
FROM propellor.pro_files

INNER JOIN propellor.pro_users ON propellor.pro_files.userid=propellor.pro_users.userid

INNER JOIN (
SELECT material, colour, count(*) AS occ 
FROM propellor.pro_files
GROUP BY material, colour
) 

AS mcCounts 
USING (material, colour)
WHERE status =1 AND laser=1
ORDER BY mcCounts.occ DESC,
material,
colour,
propellor.pro_files.timesent ASC
;

Answer 1

您需要单独计算计数，但可以在单个查询中全部完成；应该这样做:

SELECT pf.* 
FROM propellor.pro_files AS pf
INNER JOIN (
   SELECT material, colour, count(*) AS occ 
   FROM propellor.pro_files
   GROUP BY material, colour 
) AS mcCounts USING (material, colour)
ORDER BY mcCounts.occ DESC
   , material, colour
   , pf.sent DESC
;

编辑:为 ORDER BY...添加 Material 、颜色，用于当两个组合具有相同频率时。