mysql用法进阶

1.按中文拼音排序：

表中中文为utf8编码，先转成gbk。

select name from table_name order by convert(name using gbk) asc

2.多行转多列：

参考文献：http://www.cnblogs.com/small8/p/6211009.html

例如要行转列的字段为subject(学科)，要赋值的字段score(成绩)，为可以通过如下方式转换：

select  name,

MAX(CASE subject when '语文' THEN score ELSE 0 END) '语文',

           MAX(CASE subject when '数学' THEN score ELSE 0 END) '数学',

 

           MAX(CASE subject when '英语' THEN score ELSE 0 END) '英语'

from exam_result

group by name

 

3.分组内编序号

题目：假设表table_1的字段为group_name,log_time,field_name1,field_name2。需求是想取每个group_name中log_time最小的记录。

实现方法：用变量记录上次的分组的值，如果相同，则计数+1，否则重新计数

SQL如下：

select group_name,log_time,field_name1

from

(

select group_name,log_time,field_name1, 

(@index:=CASE WHEN @last_group_name=group_name THEN @index+1 ELSE 1 END) record_index,(@last_group_name=group_name) last_group_name 

from 

(

select * 

from table_1 

order by group_name,log_time

)A,

(

select @index:=1,@last_group_name=''

)B

)C where record_index=1

4.生成序列

参考文献：http://yuanding.us/miscellany/4806/

例如生成上周的日期序列：

select sequence_date
from 
(
select subdate(curdate(),t.i) sequence_date
from 
(
select 1 i union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union select 13 union select 14
)t
)date_list
where yearweek(sequence_date,1)=yearweek(DATE_SUB(now(),INTERVAL 7 DAY),1) 
order by sequence_date

注意：判断上周日期的地方按下面写才不会有跨年的BUG：

yearweek(sequence_date,1)=yearweek(DATE_SUB(now(),INTERVAL 7 DAY),1) 

5.按分隔符取子串

例如，原始字符串格式如下：

1.2.3.456

目标是取出1.2.3

实现方式： substring_index('2.3.0.456','.',3)

6.多个select结果拼接：

select  * from a

union select * from b

7.关联赋值

两个表A，B。A.field1与B.filed2关联，想使A表filed2字段的值等于B表对应field2记录中的fieldN中的值

update A,B set A.field2=B.fieldN where A.field1=B.field2