56,679
社区成员
发帖
与我相关
我的任务
分享
DROP TABLE IF EXISTS t_g;
CREATE TABLE t_g
(t_id BIGINT AUTO_INCREMENT,
t_userid VARCHAR(10) DEFAULT '' COMMENT '用户id',
t_je VARCHAR(10) DEFAULT '' COMMENT '交易金额',
t_date VARCHAR(20) DEFAULT '' COMMENT '交易时间',
PRIMARY KEY(t_id)
);
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160701');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','10','20160702');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','15','20160703');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','15','20160704');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160705');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','50','20160706');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160707');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160708');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','40','20160701');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','20','20160702');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','15','20160703');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','15','20160704');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','20','20160701');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','50','20160702');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','20','20160703');
INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','20','20160704');
select * from t_g a
where (select @sum:=sum(t_je) from t_g c where c.t_userid=a.t_userid and c.t_date <= a.t_date) > 70
and @sum - a.t_je <= 70
;
mysql> select * from t_g;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date |
+------+----------+------+----------+
| 1 | A | 20 | 20160701 |
| 2 | A | 10 | 20160702 |
| 3 | A | 15 | 20160703 |
| 4 | A | 15 | 20160704 |
| 5 | A | 20 | 20160705 |
| 6 | A | 50 | 20160706 |
| 7 | A | 20 | 20160707 |
| 8 | A | 20 | 20160708 |
| 9 | B | 40 | 20160701 |
| 10 | B | 20 | 20160702 |
| 11 | B | 15 | 20160703 |
| 12 | B | 15 | 20160704 |
| 13 | C | 20 | 20160701 |
| 14 | C | 50 | 20160702 |
| 15 | C | 20 | 20160703 |
| 16 | C | 20 | 20160704 |
+------+----------+------+----------+
16 rows in set (0.00 sec)
mysql> select *
-> from t_g t
-> where (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)<70
-> and (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)>=70-t.t_je;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date |
+------+----------+------+----------+
| 5 | A | 20 | 20160705 |
| 11 | B | 15 | 20160703 |
| 14 | C | 50 | 20160702 |
+------+----------+------+----------+
3 rows in set (0.00 sec)
mysql>
select * from
(
select *,(select sum(t_je) from t_g where t_userid = a.t_userid and t_date <= a.t_date)total from t_g a where a.t_userid = 'A'
)x where x.total > 70 order by x.total asc limit 1;
//希望楼下有更好的