mysql 一些常见应用 concat/@a/case/limit/

来源：互联网发布：最好的股票行情软件编辑：程序博客网时间：2024/06/06 16:37

use test;
create table tt
(f1 varchar(30), f2 int );

INSERT tt values('a',1);
INSERT tt values('a',1);
INSERT tt values('a',2);
INSERT tt values('a',1);
INSERT tt values('b',1);
INSERT tt values('b',1);
INSERT tt values('b',1);

mysql> select * from tt;
+------+------+
| f1   | f2   |
+------+------+
| a    |    1 |
| a    |    1 |
| a    |    2 |
| a    |    1 |
| b    |    1 |
| b    |    1 |
| b    |    1 |
+------+------+
7 rows in set (0.00 sec)

mysql> select @a:=f1,@b:=f1 from tt order by f1 limit 1;
+--------+--------+
| @a:=f1 | @b:=f1 |
+--------+--------+
| a | a |
+--------+--------+
1 row in set (0.00 sec)

mysql> select case flag when 0 then '' else concat('total ',@b,':') end,@b:=f1,f1,f2 from (select f1,f2,(case f1 when @a then 0 else 1 end) flag ,@a:=f1 from tt order by f1 ) tb;
+-----------------------------------------------------------+--------+------+------+
| case flag when 0 then '' else concat('total ',@b,':') end | @b:=f1 | f1   | f2   |
+-----------------------------------------------------------+--------+------+------+
|                                                           | a      | a    |    1 |
|                                                           | a      | a    |    1 |
|                                                           | a      | a    |    2 |
|                                                           | a      | a    |    1 |
| total a:                                                  | b      | b    |    1 |
|                                                           | b      | b    |    1 |
|                                                           | b      | b    |    1 |
+-----------------------------------------------------------+--------+------+------+
7 rows in set (0.00 sec)