表:
ID datatime 数量
01 2011-3-1 9:10:10 10
02 2011-3-2 12:01:13 5
03 2011-3-2 11:30:22 7
04 2011-3-2 14:50:1 11
05 2011-3-4 15:3:3 8如何查询返回数量总和刚超过20的datatime排序最后 记录
即需要返回 03 04 05 三条记录,因为8+11+7刚超过20
ID datatime 数量
01 2011-3-1 9:10:10 10
02 2011-3-2 12:01:13 5
03 2011-3-2 11:30:22 7
04 2011-3-2 14:50:1 11
05 2011-3-4 15:3:3 8如何查询返回数量总和刚超过20的datatime排序最后 记录
即需要返回 03 04 05 三条记录,因为8+11+7刚超过20
from 表 a
where 20>(select sum(数量) from 表 where datatime<a.datatime)
and 20<=(select sum(数量) from 表 where datatime<=a.datatime)
FROM ttg1 AS a ) b WHERE ss>=20;
仅返回 ID为02的一条记录
若增加记录:
06 2011-3-5 4:4:4 8
则返回 03 04 05 06共四条记录,需要返回 04 05 06 三条记录 ,请再改下
from 表 a
where 20>(select sum(数量) from 表 where datatime<a.datatime)
FROM ttg1 AS a ) b WHERE ss>=20 ORDER BY id DESC LIMIT 3
我试了下,返回 02 03两条记录
SELECT * FROM (SELECT *,IFNULL((SELECT SUM(数量) FROM ttg1 WHERE a.id >id ),0)+a.`数量` AS ss
FROM ttg1 AS a ) b WHERE ss>=20 ORDER BY id DESC LIMIT 3
返回记录正确(03 04 05),但若 05 记录 数量改为 10时,应只返回 04 05两条记录 因为10+11>20
请再指点下
from 表 a
where 20>(select sum(数量) from 表 where datatime>a.datatime)
先按时间排序:
1:05 数量为8 累计数量和为8<20
2:04 数量为11 累计数量和为19<20
3:03 数量为7 累计数量和为26>20 终止,返回 03 04 05 三条记录
若将 05记录数量改为10则
1:05 数量为10累计数量和为10<20
2:04 数量为11 累计数量和为21>20 终止,返回 04 05 两条记录
SELECT *,IFNULL((SELECT SUM(数量) FROM ttg1 WHERE a.id <id ),0)+a.`数量` AS ss
FROM ttg1 AS a ) a WHERE ss<=
(SELECT MIN(ss) FROM (
SELECT *,IFNULL((SELECT SUM(数量) FROM ttg1 WHERE a.id <id ),0)+a.`数量` AS ss
FROM ttg1 AS a ) c WHERE ss>=20)
from 表 a
where 20>(select sum(数量) from 表 where datatime<a.datatime)
+------+---------------------+------+
| id | datatime | qty |
+------+---------------------+------+
| 1 | 2011-03-01 09:10:10 | 10 |
| 2 | 2011-03-02 12:01:13 | 5 |
| 3 | 2011-03-02 11:30:22 | 7 |
| 4 | 2011-03-02 14:50:01 | 11 |
| 5 | 2011-03-04 15:03:03 | 8 |
+------+---------------------+------+
5 rows in set (0.02 sec)mysql> select *
-> from ywscr0000 a
-> where 20>ifnull((select sum(qty) from ywscr0000 where `datatime`>a.datatime),0);
+------+---------------------+------+
| id | datatime | qty |
+------+---------------------+------+
| 2 | 2011-03-02 12:01:13 | 5 |
| 4 | 2011-03-02 14:50:01 | 11 |
| 5 | 2011-03-04 15:03:03 | 8 |
+------+---------------------+------+
3 rows in set (0.00 sec)mysql>