SQL常用转换格式的方法

日期格式的转换：

DATE_FORMAT( '需要转换的数据字段', "%Y-%m" ) 
DATE_FORMAT( '需要转换的数据字段', "%Y-%m-%d" ) 

将查询结果的字段转换为数据（即横向数据转为纵向数据）

SELECT C.`项`,
	( CASE WHEN C.`项` = '财务管理月度' THEN B.`财务管理月度` WHEN C.`项` = '资产总额' THEN B.`资产总额` ELSE 0 END ) AS '值' 
FROM
	( SELECT 'XSDFG123' AS '公司', '财务管理月度' AS '项' UNION ALL SELECT 'XSDFG123' AS '公司', '资产总额' AS '项' ) C
	LEFT JOIN (
SELECT
	A.`公司`,
	A.`财务管理月度`,
	A.`资产总额` 
FROM
	(
SELECT
	'XSDFG123' AS '公司',
	c.field0001 AS '财务管理月度',
	c.field0012 AS '资产总额' 
FROM
	( SELECT * FROM FORMMAIN_130918 ORDER BY field0001 DESC ) c 
GROUP BY
	DATE_FORMAT( c.field0001, "%Y-%m" ) 
	) A 
WHERE
	A.`公司` = 'XSDFG123' 
	) B ON C.公司 = B.公司

对数据分组之后需要获取分组后每组的第一条数据（最后一条数据）

SELECT
	c.field0001 AS '财务管理月度',
	c.field0012 AS '资产总额' 
FROM
	( SELECT * FROM FORMMAIN_130918 ORDER BY field0001 DESC ) c 
GROUP BY
	DATE_FORMAT( c.field0001, "%Y-%m" )

先对查询的表进行一次排序全查询，如果需要分组后的第一条数据，就升序排序，需要最后一条数据，就降序排序（一般针对用时间分组之后，获取最新或者最久的数据）

把数据为null的字段值给与默认值

IFNULL( '需要判断的字段', 0 )

查询过程中剔除字段值为null的数据

where '字段' is not null

把查询结果的几类数据合并成一类数据

SELECT A.PSNCLASSNAME,
       A.人数
FROM
(
  SELECT 
      CASE  WHEN PSNCLASSNAME IN ('劳务派遣','劳务派遣人员') THEN '劳务派遣' 
            WHEN PSNCLASSNAME IN ('合同制人员') THEN '合同制'
            WHEN PSNCLASSNAME IN ('引用人员(HR或财务)') THEN '引用人员'
            WHEN PSNCLASSNAME IN ('临时人员') THEN '临时'
            WHEN PSNCLASSNAME IN ('政府委派') THEN '政府委派'
            END  PSNCLASSNAME,
       COUNT(PSNNAME) AS 人数
 FROM  NC_EMPLOYEES_ROSTER
WHERE  PSNCLASSNAME IS NOT NULL 
GROUP  BY 
       CASE WHEN PSNCLASSNAME IN ('劳务派遣','劳务派遣人员') THEN '劳务派遣' 
            WHEN PSNCLASSNAME IN ('合同制人员') THEN '合同制'
            WHEN PSNCLASSNAME IN ('引用人员(HR或财务)') THEN '引用人员'
            WHEN PSNCLASSNAME IN ('临时人员') THEN '临时'
            WHEN PSNCLASSNAME IN ('政府委派') THEN '政府委派'
            ELSE PSNCLASSNAME
            END 
)A
WHERE A.PSNCLASSNAME IS NOT NULL

按照自定义规则对数据进行分组统计

SELECT
CASE
WHEN
  (age < 30 ) THEN
  '30岁以下' 
  WHEN ( age >= 30 AND age < 35 ) THEN
  '30-35岁' 
  WHEN ( age >= 35 AND age < 40 ) THEN
  '35-40岁' 
  WHEN ( age >= 40 AND age < 45 ) THEN
  '40-45岁'
  WHEN ( age >= 45 AND age < 50 ) THEN
  '45-50岁'
  ELSE '50以上' 
  END '年龄结构',
 count( * ) '人数' 
FROM
 ( SELECT FLOOR( DATEDIFF( CURDATE( ), BIRTHDATE ) / 365.25 ) AS age FROM NC_EMPLOYEES_ROSTER where BIRTHDATE is not null) ages 
GROUP BY
CASE  
  WHEN (age < 30 ) THEN
  '30' 
  WHEN ( age >= 30 AND age < 35 ) THEN
  '30-35' 
  WHEN ( age >= 35 AND age < 40 ) THEN
  '35-40'
  WHEN ( age >= 40 AND age < 45 ) THEN
  '40-45'
  WHEN ( age >= 45 AND age < 50 ) THEN
  '45-50'
 ELSE '50以上' 
 END 

在结果集前面增加一列递增的序列号

select (@rowNO := @rowNo+1) AS rowno,a.* from FORMMAIN_77221 a,(select @rowNO :=0) b ;