CREATE TABLE `userInfo` (
`userName` char(64) NOT NULL COMMENT '用户名\0e\0',
`` int(11) NOT NULL,
PRIMARY KEY (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `userDetail` (
`userName` char(64) NOT NULL COMMENT '用户名\0e\0',
`DoTime` int(11) NOT NULL,
PRIMARY KEY (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;我有两个表userInfo,同userDetail,现在我想实现 对DoTime>100的所有用户积分 都减去DoTime*100; 请问存储过程怎么写
`userName` char(64) NOT NULL COMMENT '用户名\0e\0',
`` int(11) NOT NULL,
PRIMARY KEY (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `userDetail` (
`userName` char(64) NOT NULL COMMENT '用户名\0e\0',
`DoTime` int(11) NOT NULL,
PRIMARY KEY (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;我有两个表userInfo,同userDetail,现在我想实现 对DoTime>100的所有用户积分 都减去DoTime*100; 请问存储过程怎么写
解决方案 »
- 求linux下如何设置timeouts,如何autoReconnect=true
- mysql设置输出没有head
- 新手想学习my sql,请大家推荐书籍
- 求一条SQL语句,各位大哥帮忙看看。。
- 终于编译通过Win32版的MySQL了(支持中文全文索引的),散分……
- 用客户端如ODBC访问远程的MySQL,每次都要set names gbk才可以
- 头一回来,小的给各位请安了!
- 各位大哥,我的计算机是INTEL的32位的,在哪能下到MYSQL呀?????
- 关于论坛的数据库!
- 再送100分,mysql同时访问的问题
- mysql远程访问次数限制问题???
- load data infile 时文件中域分隔符的文件难题
set DoTime=DoTime-DoTime*100
where DoTime>100;
inner join
`userDetail` b on a.`userName`=b.`userName`
set a.``=b.DoTime*100where b.DoTime>100
update userDetail set Dotime=Dotime-DoTime*100 where Dotime>100;
set =-DoTime*100
where DoTime>100;
inner join
`userDetail` b on a.`userName`=b.`userName`
set a.``=a.``-b.DoTime*100 where b.DoTime>100
set =-DoTime*100
where DoTime>100;
update userDetail d ,userInfo i
set =-DoTime*100
where DoTime>100
and d.userName=i.userName
update userDetail d inner join userInfo i using (userName)
set =-DoTime*40
where DoTime>100;update userDetail d inner join userInfo i on d.userName=i.userName
set =-DoTime*40
where DoTime>100;update userDetail d ,userInfo i
set =-DoTime*40
where DoTime>100
and d.userName=i.userName
and username = '' ;
是这个意思吗?
create procedure test(out resultvalue int)
begin
declare v_userName varchar(32);
declare v_doTime int(11);
declare done int default 0;
declare cur1 cursor for select userName,doTime from userDetail;
declare exit handler for not found set done=1;
set resultvalue=0;
open cur1;
repeat
fetch cur1 into v_userName,v_doTime;
if v_doTime>=100 then
update userInfo set =-v_doTime*40 where userName=v_userName;
else
set resultvalue=1;
end if;
until done=1 end repeat;
close cur1;
end $$
delimiter ;
inner join
`userDetail` b on a.`userName`=b.`userName`
set a.``=a.``-b.DoTime*40 where b.DoTime>=100
update `userInfo` a ,`userDetail` b
set a.``=a.``-b.DoTime*40
where b.DoTime>=100 and a.`userName`=b.`userName`