一些sql语句

• 分组查询，并且拼接分组内字段值,（函数group_concat）

id	type	value	description
1	1	汽车	交通工具
2	1	火车	交通工具
3	2	狗	宠物
4	2	猫	宠物

 

sql: select type,description,group_concat(value) from table group by type ;

type	description	value
1	交通工具	汽车,火车
2	宠物	狗,猫

• 根据身份证自动计算
  SELECT identification,CAST(SUBSTRING(identification,7,8) AS DATETIME) AS bir,FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SUBSTRING(identification,7,8) AS DATETIME)),'%m-%d') AS TIME,IF(LEFT(SUBSTRING(identification,17),1)%2=1,"男","女") AS sex 
  FROM kdt