MySQL - 特定日期发生最多的事件？

Question

我被这个问题困住了。

基本上，我需要为每个部门找出如何确定哪些天的销售额最高。结果显示部门编号和当天的日期，如果有几天的销售额相同，部门编号可能会在结果中出现多次。

这是我到目前为止:

SELECT departmentNo, sDate FROM Department
HAVING MAX(sDate)
ORDER BY departmentNo, sDate;

我尝试使用 max 函数来查找出现次数最多的日期。但它只返回一行值。为了更清楚地说明，销售额最多的日期应与名为部门号的相应列一起出现。此外，如果部门 A 的两个日期具有相同的大部分销售额，那么部门 A 将出现两次并且两个日期也都显示。

注意:只应显示销售额最多的日期，并且部门编号。

我已经开始使用 mySQL 几个星期了，但仍在努力掌握子查询和存储功能之类的东西。但我会从经验中学习。提前谢谢你。

更新:

我应该得到的结果:

DepartmentNo Column 1: 1 | Date Column 2: 15/08/2000 
DepartmentNo Column 1: 2 | Date Column 2: 01/10/2012 
DepartmentNo Column 1: 3 | Date Column 2: 01/06/1999 
DepartmentNo Column 1: 4 | Date Column 2: 08/03/2002 
DepartmentNo Column 1: nth | Date Column 2: nth date

这些是数据:

INSERT INTO Department VALUES ('1','tv','2012-05-20','13:20:01','19:40:23','2');
INSERT INTO Department VALUES ('2','radio','2012-07-22','09:32:23','14:18:51','4');
INSERT INTO Department VALUES ('3','tv','2012-09-14','15:15:43','23:45:38','3');
INSERT INTO Department VALUES ('2','tv','2012-06-18','06:20:29','09:57:37','1');
INSERT INTO Department VALUES ('1','radio','2012-06-18','11:34:07','15:41:09','2');
INSERT INTO Department VALUES ('2','batteries','2012-06-18','16:20:01','23:40:23','3');
INSERT INTO Department VALUES ('2','remote','2012-06-18','13:20:41','19:40:23','4');
INSERT INTO Department VALUES ('1','computer','2012-06-18','13:20:54','19:40:23','4');
INSERT INTO Department VALUES ('2','dishwasher','2011-06-18','13:20:23','19:40:23','4');
INSERT INTO Department VALUES ('3','lawnmower','2011-06-18','13:20:57','20:40:23','4');
INSERT INTO Department VALUES ('3','lawnmower','2011-06-18','11:20:57','20:40:23','4');
INSERT INTO Department VALUES ('1','mobile','2012-05-18','13:20:31','19:40:23','4');
INSERT INTO Department VALUES ('1','mouse','2012-05-18','13:20:34','19:40:23','4');
INSERT INTO Department VALUES ('1','radio','2012-05-18','13:20:12','19:40:23','4');
INSERT INTO Department VALUES ('2','lawnmowerphones','2012-05-18','13:20:54','19:40:23','4');
INSERT INTO Department VALUES ('2','tv','2012-05-12','06:20:29','09:57:37','1');
INSERT INTO Department VALUES ('2','radio','2011-05-23','11:34:07','15:41:09','2');
INSERT INTO Department VALUES ('1','batteries','2011-05-21','16:20:01','23:40:23','3');
INSERT INTO Department VALUES ('2','remote','2011-05-01','13:20:41','19:40:23','4');
INSERT INTO Department VALUES ('3','mobile','2011-05-09','13:20:31','19:40:23','4');

对于部门 1，将出现日期 2012-05-18，因为该日期出现次数最多。对于每个部门，它应该只显示销售额最多的部门，如果同一日期出现相同数量的销售额，则两者都会出现，例如部门 1 将出现两次，同时包含最大销售日期。

Answer 1

我已经根据您随示例数据提供的表和两列测试了以下查询。那么，让我为你描述一下。最里面的“PREQUERY”正在按部门和日期进行计数。其结果将首先由部门预先排序，然后是降序中的最高计数(因此最高销售计数首先列出)，计数发生的日期无关紧要。

接下来，通过使用 MySQL @variables，我预先声明了两个要在查询中使用。 @variables 就像使用 MySQL 进行内联编程。它们可以声明一次，然后更改为应用于正在处理的每个记录。所以，我默认为一个虚假的部门值(value)和零销售额。

现在，我正在获取 PreQuery(部门、#Sales 和日期)的结果，但是现在，添加了一个测试。如果它是给定部门的第一个条目，则使用该记录的“NumberOfSales”并将其放入@maxSales 变量并存储为最终列名“MaxSaleCnt”。下一列名称使用@lastDept 并设置为当前记录的部门编号。所以它可以与下一个记录进行比较。

如果下一条记录是同一个部门，那么它只保留前一个的@maxSales 值，从而为每个相应部门的所有条目保持相同的第一个 count(*) 结果。

现在，关闭。我添加了一个 HAVING 子句(不是 WHERE，因为它限制了哪些记录被测试，但是在记录是 PROCESSED 集的一部分之后的 HAVING 进程。所以现在，它将包含所有 5 列。我是说只保留那些记录在哪里记录的最终 NumberOfSales 与部门的 MaxSaleCnt 匹配。如果有一个、两个或多个日期，没问题，它会根据各个部门返回它们。

因此，一个部门可以有 5 个日期，每个日期有 10 个销售，另一个部门有 2 个日期，每个日期只有 3 个销售，另一个部门只有 1 个日期，有 6 个销售。

select
      Final.DepartmentNo,
      Final.NumberOfSales,
      Final.sDate
   from
      (select
          PreQuery.DepartmentNo,
          PreQuery.NumberOfSales,
          PreQuery.sDate,
          @maxSales := if( PreQuery.DepartmentNo = @lastDept, @maxSales, PreQuery.NumberOfSales ) MaxSaleCnt,
          @lastDept := PreQuery.DepartmentNo
       from
          ( select
                  D.DepartmentNo,
                  D.sDate,
                  count(*) as NumberOfSales
               from
                  Department D
               group by
                  D.DepartmentNo,
                  D.sDate
               order by
                  D.DepartmentNo,
                  NumberOfSales DESC ) PreQuery,
          ( select @lastDept := '~',
                   @maxSales := 0 ) sqlvars
       having
          NumberOfSales = MaxSaleCnt ) Final

根据您的最终评论澄清“@”和“〜”。 “@”表示可以在查询中使用的程序的局部变量(或在本例中为内嵌 sql 变量)。 '~' 只不过是一个简单的字符串，概率永远不会存在于你的任何部门，所以当它与第一个合格的记录进行比较时，做一个 IF('~' = YourFirstDepartmentNumber，然后使用这个答案，否则使用这个答案)。

现在，如何进行上述工作。假设以下是最内层查询返回的数据的结果，按顶部销售额最多的顺序分组和排序......从您的数据中略有改变，让我们假设以下内容来模拟部门的多个日期2 有相同的销售量...

Row#   DeptNo  Sales Date     # Sales
1      1       2012-05-18        3
2      1       2012-06-18        2
3      1       2012-05-20        1

4      2       2012-06-18        4
5      2       2011-05-23        4
6      2       2012-05-18        2
7      2       2012-05-12        1

8      3       2011-06-18        2
9      3       2012-09-14        1

跟踪实际行。以别名“PreQuery”完成的最里面的查询按您在此处看到的顺序返回所有行。然后，将其与 @ sqlvariables 的声明连接(隐含)(MySQL 专用，其他 sql 引擎不这样做)并以 lastDept = '~' 和 maxSales = 0 开始它们的值(通过@someVariable 赋值: = 这方面的结果)。
现在，将上述处理视为

DO WHILE WE HAVE RECORDS LEFT
   Get the department #, Number of Sales and sDate from the record.

   IF the PreQuery Record's Department # = whatever is in the @lastDept
      set MaxSales = whatever is ALREADY established as max sales for this dept
      This basically keeps the MaxSales the same value for ALL in the same Dept #
   ELSE
      set MaxSales = the # of sales since this is a new department number and is the highest count
   END IF

   NOW, set @lastDept = the department you just processed to it 
   can be compared when you get to the next record.

   Skip to the next record to be processed and go back to the start of this loop
END DO WHILE LOOP

现在，您需要将 @MaxSales 和 THEN @LastDept 作为返回列的原因是必须为每条记录计算它们以用于与 NEXT 记录进行比较。这种技术可用于许多应用目的。如果您单击我的名字，查看我的标签并单击 MySQL 标签，它会向您显示我已回复的许多 MySQL 答案。他们中的许多人确实使用@ sqlvariables。此外，还有很多其他人非常擅长工作查询，所以不要只看一个地方。对于任何问题，如果您发现一个对您有帮助的好答案，即使您没有发布问题，单击答案旁边的向上箭头也可以帮助其他人指出真正帮助他们理解并解决问题的内容 - - 再说一遍，即使这不是你的问题。祝你的 MySQL 增长好运。