我有下面的模式和用于获取运行总数的qrys。以此为起点，我如何获得移动的三天运行总数，如果行名为2012-09-02，那么运行总数应该是2012-09-01+2012-09-02+2012-09-03的总和？理想的结果如下
理想结果：
| ORDER_DATE | QUANTITY | RUNNINGTOTAL |
|------------|----------|--------------|
| 2012-09-01 | 11 | 36 | -> 11 + 25
| 2012-09-02 | 25 | 86 | -> 11 + 25 + 50
| 2012-09-03 | 50 | 125 | -> 25 + 50 + 50
| 2012-09-04 | 50 | 101 | -> 50 + 50 + 1
| 2012-09-05 | 1 | -- |
SQL Fiddle
MySQL 5.5.32架构设置:
CREATE TABLE Table1
(`order_id` int, `order_date` varchar(10), `cust_id` int, `order_line` int, `book_id` int, `quantity` int, `order_price` int)
;
INSERT INTO Table1
(`order_id`, `order_date`, `cust_id`, `order_line`, `book_id`, `quantity`, `order_price`)
VALUES
(1002, '2012-09-08', 234138, 1, 1107, 11, 25.00),
(1002, '2012-09-08', 234138, 2, 1106, 12, 25.50),
(1003, '2012-09-08', 200368, 1, 1104, 5, 45.00),
(1004, '2012-09-18', 221297, 1, 1106, 2, 18.25),
(1005, '2012-09-28', 290298, 1, 1142, 2, 42.45),
(1005, '2012-09-28', 290298, 2, 1107, 4, 21.50),
(1006, '2012-09-28', 208950, 1, 1103, 10, 10.95),
(1007, '2012-09-03', 276381, 1, 1448, 50, 25.00),
(1008, '2012-09-10', 276381, 1, 1670, 50, 40.00),
(1010, '2012-09-10', 218709, 1, 1537, 50, 25.00),
(1011, '2012-09-10', 261502, 1, 1103, 2, 10.95),
(1012, '2012-09-05', 260368, 1, 1128, 1, 49.95),
(1027, '2012-09-18', 234709, 1, 2001, 21, 49.99),
(1027, '2012-09-18', 234709, 2, 1077, 22, 10.99),
(1028, '2012-09-20', 234709, 1, 2001, 1, 19.78),
(1028, '2012-09-20', 234709, 2, 2002, 22, 40.00),
(1028, '2012-09-20', 234709, 3, 2004, 1, 49.95),
(1028, '2012-09-20', 234709, 4, 2006, 1, 46.95),
(1030, '2012-09-22', 234709, 1, 1279, 1, 40.49),
(1035, '2012-09-22', 221297, 1, 1689, 1, 55.19),
(1039, '2012-09-22', 212921, 1, 1448, 1, 30.00),
(1039, '2012-09-22', 212921, 2, 1162, 2, 34.95),
(1040, '2012-09-28', 263119, 1, 2025, 560, 39.00),
(1040, '2012-09-28', 263119, 2, 2018, 2, 49.99),
(1602, '2012-09-14', 217796, 1, 1103, 2, 10.95),
(1602, '2012-09-14', 217796, 2, 1106, 1, 29.00),
(1603, '2012-09-10', 261502, 1, 2009, 5, 34.95),
(1603, '2012-09-10', 261502, 3, 2007, 1, 39.00),
(1603, '2012-09-10', 261502, 4, 2001, 1, 39.00),
(1604, '2012-09-10', 217796, 1, 1103, 25, 10.95),
(1604, '2012-09-10', 217796, 2, 1106, 15, 29.00),
(1606, '2012-09-14', 217796, 1, 1106, 5, 34.95),
(1606, '2012-09-14', 217796, 2, 1107, 5, 20.95),
(1606, '2012-09-14', 217796, 3, 2001, 5, 39.00),
(1610, '2012-09-15', 263119, 1, 1103, 2, 12.00),
(1615, '2012-09-15', 261502, 1, 1103, 2, 12.00),
(22803, '2012-09-02', 224038, 1, 1128, 25, 45.00),
(22803, '2012-09-02', 224038, 2, 1301, 15, 45.50),
(22803, '2012-09-02', 224038, 3, 1304, 5, 59.99),
(22804, '2012-09-04', 239427, 1, 1304, 50, 45.00),
(22804, '2012-09-04', 239427, 2, 1305, 50, 9.99),
(22805, '2012-09-06', 224038, 1, 1104, 5, 45.00),
(22805, '2012-09-06', 224038, 2, 1306, 5, 250.12),
(22806, '2012-09-23', 239427, 1, 1107, 1, 25.00),
(22807, '2012-09-23', 224038, 1, 1175, 1, 34.99),
(22808, '2012-09-24', 290298, 1, 1182, 1, 45.00),
(22809, '2012-09-25', 239427, 1, 1104, 5, 45.00),
(22810, '2012-09-25', 218709, 1, 1678, 5, 49.99),
(22810, '2012-09-25', 218709, 2, 1162, 5, 35.00),
(22811, '2012-09-30', 261502, 1, 1478, 5, 45.00),
(22812, '2012-09-30', 239427, 1, 1357, 50, 26.00),
(22812, '2012-09-30', 239427, 2, 1425, 50, 28.09),
(22813, '2012-09-30', 239427, 1, 1175, 1, 45.00),
(22813, '2012-09-30', 239427, 2, 1180, 1, 49.99),
(22813, '2012-09-30', 239427, 3, 1182, 1, 45.00),
(22813, '2012-09-30', 239427, 4, 1184, 1, 49.99),
(22813, '2012-09-30', 239427, 5, 1185, 1, 49.99),
(22813, '2012-09-30', 239427, 6, 1188, 1, 49.99),
(22813, '2012-09-30', 239427, 7, 1877, 1, 45.00),
(22813, '2012-09-30', 239427, 8, 1175, 1, 34.90),
(22813, '2012-09-30', 239427, 9, 1425, 1, 25.90),
(31884, '2012-09-22', 290298, 1, 1278, 1, 48.00),
(31884, '2012-09-22', 290298, 2, 1199, 9, 17.99),
(31885, '2012-09-22', 217796, 1, 1448, 50, 25.00),
(31889, '2012-09-22', 227105, 1, 1109, 18, 50.60),
(32800, '2012-09-29', 217796, 1, 1128, 50, 25.00),
(32890, '2012-09-01', 212921, 1, 1103, 11, 15.00),
(32891, '2012-09-05', 212921, 1, 1142, 5, 15.00),
(32892, '2012-09-10', 272611, 1, 2002, 5, 15.00),
(32893, '2012-09-15', 200368, 1, 1689, 1, 55.19),
(32894, '2012-09-18', 234138, 1, 1894, 1, 35.99),
(32895, '2012-09-18', 218709, 1, 1689, 1, 55.19),
(32896, '2012-09-18', 218709, 1, 1894, 1, 35.99),
(32897, '2012-09-22', 261502, 1, 1110, 2, 50.00),
(32898, '2012-09-22', 261502, 1, 2006, 3, 20.00),
(32899, '2012-09-29', 261502, 1, 1128, 50, 25.00)
;
CREATE TABLE Table1
(`OrderDate` varchar(10), `3 Day Order Count` int, `3 Day Total Quantity` int)
;
INSERT INTO Table1
(`OrderDate`, `3 Day Order Count`, `3 Day Total Quantity`)
VALUES
('2012-09-01', 99, 999),
('2012-09-02', 99, 999),
('2012-09-03', 99, 999),
('2012-09-05', 99, 999),
('2012-09-06', 99, 999),
('2012-09-07', 99, 999)
;
查询1：
set @runningorder := 0
结果：
查询2：
set @runningqty := 0
结果：
查询3：
select order_date
, @runningorder := @runningorder + TotalOrder as TotalOrder
, @runningqty := @runningqty + TotalQty as TotayQuantity
from (
select order_date
, count(order_id) as TotalOrder
, sum(quantity) as TotalQty
from table1
group by order_date) t1
结果：
| ORDER_DATE | TOTALORDER | TOTAYQUANTITY |
|------------|------------|---------------|
| 2012-09-01 | 1 | 11 |
| 2012-09-02 | 4 | 56 |
| 2012-09-03 | 5 | 106 |
| 2012-09-04 | 7 | 206 |
| 2012-09-05 | 9 | 212 |
| 2012-09-06 | 11 | 222 |
| 2012-09-08 | 14 | 250 |
| 2012-09-10 | 23 | 404 |
| 2012-09-14 | 28 | 422 |
| 2012-09-15 | 31 | 427 |
| 2012-09-18 | 37 | 475 |
| 2012-09-20 | 41 | 500 |
| 2012-09-22 | 51 | 588 |
| 2012-09-23 | 53 | 590 |
| 2012-09-24 | 54 | 591 |
| 2012-09-25 | 57 | 606 |
| 2012-09-28 | 62 | 1184 |
| 2012-09-29 | 64 | 1284 |
| 2012-09-30 | 76 | 1398 |
试试这样的东西：
已编辑
SELECT a.order_date, a.quantity, (ifnull(a.quantity, 0) +
ifnull((SELECT b.quantity FROM table1 b
WHERE a.order_date > b.order_date
ORDER BY order_date DESC, quantity desc LIMIT 1), 0) +
ifnull((SELECT c.quantity FROM table1 c
WHERE a.order_date < c.order_date
ORDER BY order_date ASC, quantity desc LIMIT 1), 0)) AS totalquantity
FROM table1 a group by a.order_date ORDER BY 1;
示例