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

 

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)