这两天正在被mysql玩,借这个帖子说些想法。 俺发现mysql好像无法支持在遍历过程中对变量赋值,举个例子说: select @x=case when column1<10 then 10 else column1 end from table1 但这样的语句在sqlserver中就能运行 请大家谈谈想法
可直接使用:create table tab1(col1 int, col2 int); insert into tab1 values(1,1); insert into tab1 values(10,10); insert into tab1 values(100,100); insert into tab1 values(null,0);mysql> select ifnull(case when col1>1 then col1 end, 0), col2 from tab1; +-------------------------------------------+------+ | ifnull(case when col1>1 then col1 end, 0) | col2 | +-------------------------------------------+------+ | 0 | 1 | | 10 | 10 | | 100 | 100 | | 0 | 0 | +-------------------------------------------+------+ 4 rows in set (0.00 sec)
to 二楼,你给 case 取个别名能满足吗?mysql> select case when col1<10 then 10 else col1 end x, col1, col2 from tab1; +------+------+------+ | x | col1 | col2 | +------+------+------+ | 10 | 1 | 1 | | 10 | 10 | 10 | | 100 | 100 | 100 | | NULL | NULL | 0 | +------+------+------+ 4 rows in set (0.00 sec)
俺发现mysql好像无法支持在遍历过程中对变量赋值,举个例子说:
select
@x=case when column1<10 then 10 else column1 end
from
table1
但这样的语句在sqlserver中就能运行
请大家谈谈想法
insert into tab1 values(1,1);
insert into tab1 values(10,10);
insert into tab1 values(100,100);
insert into tab1 values(null,0);mysql> select ifnull(case when col1>1 then col1 end, 0), col2 from tab1;
+-------------------------------------------+------+
| ifnull(case when col1>1 then col1 end, 0) | col2 |
+-------------------------------------------+------+
| 0 | 1 |
| 10 | 10 |
| 100 | 100 |
| 0 | 0 |
+-------------------------------------------+------+
4 rows in set (0.00 sec)
+------+------+------+
| x | col1 | col2 |
+------+------+------+
| 10 | 1 | 1 |
| 10 | 10 | 10 |
| 100 | 100 | 100 |
| NULL | NULL | 0 |
+------+------+------+
4 rows in set (0.00 sec)
不过还是谢谢wenzhulz(触丝)的想法。:)