如何GROUP BY连续的数据（在这种情况下是日期）

本文介绍了如何GROUP BY连续的数据（在这种情况下是日期）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个产品表和一个销售表，用于记录给定产品在销售过程中销售的产品数量每个日期。当然，并非所有的产品每天都有销售。

I have a products table and a sales table that keeps record of how many items a given product sold during each date. Of course, not all products have sales everyday.

我需要生成一份报告，告诉我产品销售的时间有多少连续 （从最新日期到过去）以及它在那些日子里卖出的物品数量。

I need to generate a report that tells me how many consecutive days a product has had sales (from the latest date to the past) and how many items it sold during those days only.

我想告诉你我已经尝试了多少东西很远，但唯一成功（和缓慢的递归）是我的应用程序内部的解决方案，而不是内部SQL，这是我想要的。

I'd like to tell you how many things I've tried so far, but the only succesful (and slow, recursive) ones are solutions inside my application and not inside SQL, which is what I want.

我也浏览了几个类似但是我还没有找到让我清楚自己真正需要什么的一个。

I also have browsed several similar questions on SO but I haven't found one that lets me have a clear idea of what I really need.

我已经设置了一个 SQLFiddle here 向你展示我在说什么。在那里你会看到我能想到的唯一的问题，这并不能给我我需要的结果。我还在那里添加了注释，显示查询的结果应该是什么。

I've setup a SQLFiddle here to show you what I'm talking about. There you will see the only query I can think of, which doesn't give me the result I need. I also added comments there showing what the result of the query should be.

我希望这里有人知道如何实现这一点。感谢您的任何意见！

I hope someone here knows how to accomplish that. Thanks in advance for any comments!

Francisco

Francisco

推荐答案

sqlfiddle/#!2/20108/1

这是一个执行这项工作的存储过程

Here is a store procedure that do the job

CREATE PROCEDURE myProc() BEGIN -- Drop and create the temp table DROP TABLE IF EXISTS reached; CREATE TABLE reached ( sku CHAR(32) PRIMARY KEY, record_date date, nb int, total int) ENGINE=HEAP; -- Initial insert, the starting point is the MAX sales record_date of each product INSERT INTO reached SELECT products.sku, max(sales.record_date), 0, 0 FROM products join sales on sales.sku = products.sku group by products.sku; -- loop until there is no more updated rows iterloop: LOOP -- Update the temptable with the values of the date - 1 row if found update reached join sales on sales.sku=reached.sku and sales.record_date=reached.record_date set reached.record_date = reached.record_date - INTERVAL 1 day, reached.nb=reached.nb+1, reached.total=reached.total + sales.items; -- If no more rows are updated it means we hit the most longest days_sold IF ROW_COUNT() = 0 THEN LEAVE iterloop; END IF; END LOOP iterloop; -- select the results of the temp table SELECT products.sku, products.title, products.price, reached.total as sales, reached.nb as days_sold from reached join products on products.sku=reached.sku; END//

然后你只需要做

call myProc()