按连续外键值分组的 MySQL 查询 [英] MySQL query grouped by contigious foreign key values
问题描述
我有这样的数据:
+----+-------------------------+----------+----------+
| ID | DateReceived | Quantity | VendorID |
+----+-------------------------+----------+----------+
| 1 | 2010-08-09 06:53:44.783 | 2 | 1 |
| 2 | 2010-08-01 13:31:26.893 | 1 | 1 |
| 3 | 2010-07-26 07:52:29.403 | 2 | 1 |
| 4 | 2011-03-22 13:31:11.000 | 1 | 2 |
| 5 | 2011-03-22 13:31:11.000 | 1 | 2 |
| 6 | 2011-03-22 11:27:01.000 | 1 | 2 |
| 7 | 2011-03-18 09:04:58.000 | 1 | 1 |
| 8 | 2011-12-17 08:21:29.000 | 1 | 3 |
| 9 | 2012-08-10 10:55:20.000 | 9 | 3 |
| 10 | 2012-08-02 20:18:10.000 | 5 | 1 |
| 11 | 2012-07-12 20:44:36.000 | 3 | 1 |
| 12 | 2012-07-05 20:45:29.000 | 1 | 1 |
| 13 | 2013-03-22 13:31:11.000 | 1 | 2 |
| 14 | 2013-03-22 13:31:11.000 | 1 | 2 |
+----+-------------------------+----------+----------+
我想按 DateReceived 对数据进行排序，并对 Quantity 求和.但是，我想对按 VendorID 分组的 Quantity 求和，只要它们相邻(按 DateReceived 排序时)，如下面的示例输出.
I want to sort the data by the DateReceived and sum the Quantity. BUT, I want to sum the Quantity grouped by the VendorID as long as they are adjacent (when sorted by DateReceived) like the example output below.
+----------+----------+
| VendorID | Quantity |
+----------+----------+
| 1 | 5 |
| 2 | 3 |
| 1 | 1 |
| 3 | 10 |
| 1 | 9 |
| 2 | 2 |
+----------+----------+
我认为答案与变量有关，但我想不通该怎么做.
I think the answer has something to do with variables, but I can't think through how to do it.
生成所需输出的 MySQL 查询是什么?
What is a MySQL query to generate the desired output?
注意:我问了这里完全相同但对于 MS Sql，我现在需要它用于 MySQL.
note: I asked the exact same thing here but for MS Sql, I now need this for MySQL.
推荐答案
select
VendorID,
SUM(Quantity)
from (
select
t.*,
@grn := if(@prev != VendorID, @grn + 1, @grn) as grn,
@prev := VendorID
from
t
, (select @grn := 0, @prev := null) var_init
order by DateReceived
) sq
GROUP BY grn
但是您的预期输出是错误的.你可以通过执行这个来看到这一点:
But your expected output is wrong. You can see this by executing just this:
select
t.*,
@grn := if(@prev != VendorID, @grn + 1, @grn) as grn,
@prev := VendorID
from
t
, (select @grn := 0, @prev := null) var_init
order by DateReceived
结果:
| ID | DATERECEIVED | QUANTITY | VENDORID | GRN | @PREV := VENDORID |
|----|---------------------------------|----------|----------|-----|-------------------|
| 3 | July, 26 2010 07:52:29+0000 | 2 | 1 | 0 | 1 |
| 2 | August, 01 2010 13:31:26+0000 | 1 | 1 | 0 | 1 |
| 1 | August, 09 2010 06:53:44+0000 | 2 | 1 | 0 | 1 |
| 7 | March, 18 2011 09:04:58+0000 | 1 | 1 | 0 | 1 |
| 6 | March, 22 2011 11:27:01+0000 | 1 | 2 | 1 | 2 |
| 4 | March, 22 2011 13:31:11+0000 | 1 | 2 | 1 | 2 |
| 5 | March, 22 2011 13:31:11+0000 | 1 | 2 | 1 | 2 |
| 8 | December, 17 2011 08:21:29+0000 | 1 | 3 | 2 | 3 |
| 12 | July, 05 2012 20:45:29+0000 | 1 | 1 | 3 | 1 |
| 11 | July, 12 2012 20:44:36+0000 | 3 | 1 | 3 | 1 |
| 10 | August, 02 2012 20:18:10+0000 | 5 | 1 | 3 | 1 |
| 9 | August, 10 2012 10:55:20+0000 | 9 | 3 | 4 | 3 |
| 13 | March, 22 2013 13:31:11+0000 | 1 | 2 | 5 | 2 |
| 14 | March, 22 2013 13:31:11+0000 | 1 | 2 | 5 | 2 |
整个查询的结果:
| VENDORID | SUM(QUANTITY) |
|----------|---------------|
| 1 | 6 |
| 2 | 3 |
| 3 | 1 |
| 1 | 9 |
| 3 | 9 |
| 2 | 2 |
- sqlfiddle
这篇关于按连续外键值分组的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
