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