首先准备一个测试的表以及数据 create table code_line_record( id_ int primary key auto_increment, name varchar(20), create_time_ date, line_number_ int ); insert into code_line_record values(null, "qiaolin", "2018-03-01", 100); insert into code_line_record values(null, "qiaolin", "2018-03-02", 200); insert into code_line_record values(null, "qiaolin", "2018-03-03", 300); insert into code_line_record values(null, "qiaolin", "2018-03-04", 400); insert into code_line_record values(null, "qiaolin", "2018-03-05", 500); insert into code_line_record values(null, "qiaolin", "2018-03-06", 600); insert into code_line_record values(null, "qiaolin", "2018-03-07", 700); insert into code_line_record values(null, "qiaolin", "2018-03-08", 800); insert into code_line_record values(null, "qiaolin", "2018-03-09", 900); insert into code_line_record values(null, "qiaolin", "2018-03-10", 1000);
我这里是查询代码写到1000行的是哪一天,反正给大家的是个案例,实际的需求看自己的运用!代码如下: select * from ( select clr.*, if(@sum + line_number_ >= 1000 and @isOk = 'no', @isOk := 'yes', @sum := @sum + line_number_) result, @sum , @isOk from code_line_record clr ,(SELECT @sum := 0, @isOk := 'no') AS it ) temp where temp.result = 'yes'
下面是运行前和运行后的效果截图:转自 https://blog.csdn.net/qq_35170213/article/details/79502685 :=和=的区别:=是赋值的意思,=是等于的意思(特例:update里set xxx='xxx'是将=用于赋值) @num:=@num+1,:=是赋值的作用,所以,先执行@num+1,然后再赋值给@num,所以能正确实现行号的作用。
if 表达式IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL), 则 IF() 的返回值为 expr2;否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。 select if(sva=1,"男","女") as ssva from taname where id = '111'
作为表达式的 IF 也可以使用 CASE WHEN 来实现:
select CASE sva WHEN 1 THEN "男" ELSE "女" END as ssva from taname where id = '1';
转自:https://www.jianshu.com/p/79b25bd005d4
|