使用 DATE_FORMAT 在 mysql 中分组 [英] Using DATE_FORMAT to group in mysql

问题描述

我正在 Mysql 中编写 SQL 查询，其中结果应按 ID 和日期分组.日期是动态确定的，可以是简单的日期时间字段，也可以是年和月或年中周的组合.请参阅下面的查询以了解一年中的第几周；

I am writing an SQL query in Mysql where the result should be grouped by both an ID and the date. The date is determined dynamically, and can either be a simple datetime field, or a combination of the year and month or week of the year. See the query below for the week of the year case;

SELECT t.transactionId, t.transactionDate, t.transactionProcessDate, 
t.status, t.type, t.versionId, t.note, WEEKOFYEAR(t.transactionDate) as CW, 
YEAR(t.transactionDate) as yr, (DATE_FORMAT(t.transactionDate, '%Y-%u')) as tDate
FROM transaction t 
WHERE (t.status = 'A' or t.status = 'N') 
GROUP BY t.transactionId , tDate

我遇到的问题是试图按 tDate 分组，

The problem I have is attempting to group by the tDate,

GROUP BY t.transactionId , tDate

返回具有相同日期的重复项.

which returns duplicates with same dates.

当我将查询更改为按 CW 和 yr 字段分组时，

When I change the query to group by the CW and yr fields,

GROUP BY t.transactionId , CW, yr

我得到了预期的结果，没有重复.

I get the expected result, without duplicates.

问题:按DATE_FORMAT生成的字段分组是否存在问题，可能导致返回的查询无法识别重复项?
我想从查询中完全排除 CW 和 yr 两个字段，因为它们仅用于分组.

Question: Is there a problem with grouping by fields generated by the DATE_FORMATwhich may result in the returned query not being able to recorgnize duplicates?
I would like to completely exclude the two fields CW and yr from the query since they are used only for grouping.

推荐答案

UPDATED 首先，很难说没有看到你的真实数据，但是 GROUP BY DATE_FORMAT(t.transactionDate, '%Y-%u') 工作正常

UPDATED First of all, it's hard to tell not seeing your real data, but GROUP BY DATE_FORMAT(t.transactionDate, '%Y-%u') works just fine

SELECT transactionId, 
       DATE_FORMAT(transactionDate, '%Y-%u') tDate
       ...
  FROM transaction
 WHERE status IN('A', 'N') 
 GROUP BY transactionId, tDate

这里是SQLFiddle

Here is SQLFiddle

附注:即使 MySql 允许在 SELECT 中指定一个字段(在你的例子中是 t.status、t.type 等)，它不是一个GROUP BY 的一部分，这不是一件好事.您需要将聚合函数应用于该字段(MAX、MIN...).当您执行 GROUP BY

On a side note: even though MySql allows to specify a field (in your case t.status, t.type etc.) in SELECT that is not a part of GROUP BY it's a not a good thing to do. You need to apply an aggregate function to that field (MAX, MIN...). There is no way to tell deterministically which value of such a field to grab when you do GROUP BY

这篇关于使用 DATE_FORMAT 在 mysql 中分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！