假如数据库中有个test表,表中有id,Name,num,记录时间
id为自增长主键,name是名字,num表示其数量1 nike 100 系统时间
2 nike 300 系统时间
3 nike 2000 系统时间
现在我想实现从数据库中检索出name为nike的记录,减去500,结果为
1 nike 0 系统时间
2 nike 0 系统时间
3 nike 1900 系统时间 在数据库段该如何实现?
id为自增长主键,name是名字,num表示其数量1 nike 100 系统时间
2 nike 300 系统时间
3 nike 2000 系统时间
现在我想实现从数据库中检索出name为nike的记录,减去500,结果为
1 nike 0 系统时间
2 nike 0 系统时间
3 nike 1900 系统时间 在数据库段该如何实现?
id int auto_increment primary key,
Name varchar(50),
num int,
rtime datetime
);insert test(name,num,rtime) values('nike',100,now());
insert test(name,num,rtime) values('nike',300,now());
insert test(name,num,rtime) values('nike',2000,now());select * from test/**
id Name num rtime
------ ------ ------ -------------------
1 nike 100 2009-09-21 13:36:47
2 nike 300 2009-09-21 13:36:58
3 nike 2000 2009-09-21 13:37:02
**/select
id,
name,
IF(((select sum(num) from test where name='nike' and rtime<=t.rtime)-500)<0,0,(select sum(num) from test where name='nike' and rtime<=t.rtime)-500)
rtime
from
test t/**
id name rtime
------ ------ ------
1 nike 0
2 nike 0
3 nike 1900
**/
id,
name,
IF(
((select sum(num) from test where name='nike' and rtime<=t.rtime)-500)<0,
0,
(select sum(num) from test where name='nike' and rtime<=t.rtime)-500
)
rtime
from
test t
where
name='nike'
mysql> select * from test;
+------+------+------+---------------------+
| id | name | num | rtime |
+------+------+------+---------------------+
| 1 | nike | 600 | 2009-04-21 12:45:12 |
| 2 | nike | 200 | 2009-06-04 20:03:42 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)#2楼的方法如果数据是这样的话就不能满足要求了。
+-----------------------+
| if(5>2,"5 > 2",'5<2') |
+-----------------------+
| 5 > 2 |
+-----------------------+
1 row in set (0.28 sec)
select id,name,
if(
((select sum(num) from test where name='nike' and rtime<=t.rtime)-500)<0,
0,
if(
((select sum(num) from test where name='nike' and rtime<t.rtime)-500)<0,
(select sum(num) from test where name='nike' and rtime<=t.rtime)-500,
t.num
)
) num
from test t
where name='nike'
;修改了一下2楼的语句,不会出现4楼的情况了。
BEGIN
select
id,
name,
IF(
((select sum(num) from test where name=P_NAME and rtime<=t.rtime)-500)<0,
0,
(select sum(num) from test where name=P_NAME and rtime<=t.rtime)-500
)
rtime
from
test t
where
name=P_NAME;
END$$DELIMITER ;$$/**调用存储过程**/
CALL proc_test('nike');
/**
id name rtime
------ ------ ------
1 nike 0
2 nike 0
3 nike 1900
**/
如果还有第四行
4 nike 500, 则不需要减了。