分类: LINUX
2015-09-09 15:24:53
1.group_concat函数
语法:group_concat(字段名,[DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段]
[Separator '分隔符'])
例子:
mysql>select * from a;
+------+------+
job name
+------+------+
A | DD
A | GG
B | SS
B | SS
B | XX
4 rows in set (0.00 sec)
可以以job分组,把name打印在一行,逗号分隔(为默认)
mysql> select job,group_concat(name) as name from a ;
id | name |
+---+------------+
|A | DD,GG |
|B | SS,SS,XX|
+---+------------+
2 rows in set (0.00 sec)
可以以job分组,打印name中间的打印分隔符号是;
mysql>select job,group_concat(name separator';') from a;
id | name |
+---+------------+
|A | DD;GG |
|B | SS;SS;XX|
+---+------------+
2 rows in set (0.00 sec)
可以以job分组,把去重的name字段的值打印在一行;分隔符默认
mysql>select job,group_concat(distinct name) from a;
id | name |
+---+------------+
|A | DD,GG |
|B | SS,XX |
+---+------------+
2 rows in set (0.00 sec)
可以以job分组,以name倒序列打印出来,不加desc默认是升序 asc
mysql>select job ,group_concat(name order by name desc) from a;
id | name |
+---+------------+
|A | DD,GG |
|B | XX,SS,SS|
+---+------------+
2 rows in set (0.00 sec)
2.limit函数
语法:select * from tablename limit m,n
其中m是指记录开始的index,从0开始,表示第一条记录n是指从第m+1条开始,取n条 n不能
为负数。
当n为负数是会报错:
错误:#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '-1'
at line 1
例子:
select * from a limit 2,4 即取出第3条至第6条,4条记录
select * from a limit 1 就是默认去第一条记录