mysql使用笔记（一）静态实现POVIT() 行转列

mysql静态实现sqlserver PIVOT()函数

写Sql语句的时候没经常会遇到将查询结果行转列，列转行的需求在 SqlServer2005中我们有了PIVOT/UNPIVOT函数可以快速实现行转列和列转行的操作。

1. sqlserver中的PIVOT()函数

1.1函数参数与说明：

PIVOT(<聚合函数>([聚合列值]) FOR [行转列前的列名] IN([行转列后的列名1],[行转列后的列名2],[行转列后的列名3],.......[行转列后的列名N]))

(1) <聚合函数> : 就是我们使用的SUM,COUNT,AVG等Sql聚合函数，也就是行转列后计算列的聚合方式；
(2) [聚合列值]: 要进行聚合的列名；
(3) [行转列前的列名]: 这个就是需要将行转换为列的列名；
(4) [行转列后的列名]: 这里需要声明将行的值转换为列后的列名，因为转换后的列名其实就是转换前行的值，所以上面格式中的[行转列后的列名1],[行转列后的列名2],[行转列后的列名3],......[行转列后的列名N]其实就是[行转列前的列名]每一行的值；

1.2 sqlserver初始需求

得益于这篇大兄弟的文章:https://blog.csdn.net/weixin_33721427/article/details/89781701?utm_medium=distribute.pc_relevant.none-task-blog-title-11&spm=1001.2101.3001.4242

2.mysql实现上述功能

2.1 通过子查询与case when判断实现

SELECT  plant_id,workshop,date_
		,count(c0) as count0
		,count(c1) as count1
		,count(c2) as count2
		,count(c3) as count3
		,count(c4) as count4
		,count(c5) as count5
	
from
   ( select 
		 a.plant_id
		,a.workshop
		,a.date_
		,case when a.priority_sort = 0 then  a.priority_sort   end  c0
		,case when a.priority_sort = 1 then  a.priority_sort   end  c1
		,case when a.priority_sort = 2 then  a.priority_sort   end  c2
		,case when a.priority_sort = 3 then  a.priority_sort   end  c3
		,case when a.priority_sort = 4 then  a.priority_sort   end  c4
		,case when a.priority_sort = 5 then  a.priority_sort   end  c5
		from 
		(SELECT plant_id,workshop
		,NOW() as date_
		,IFNULL(po_priority_sort, 0) AS priority_sort
		FROM pdm_production_order) a
	) b
group by workshop,plant_id

2.2 通过IF聚合函数实现

SELECT 
     plant_id
	,workshop
	,date_
	,COUNT(IF(a.priority_sort = 0 , a.priority_sort,  null ))  AS  'count0' 
	,COUNT(IF(a.priority_sort = 1,  a.priority_sort,  null ))  AS  'count1' 
	,COUNT(IF(a.priority_sort = 2 , a.priority_sort,  null ))  AS  'count2' 
	,COUNT(IF(a.priority_sort = 3 , a.priority_sort,  null ))  AS  'count3' 
	,COUNT(IF(a.priority_sort = 4 , a.priority_sort,  null ))  AS  'count4' 
	,COUNT(IF(a.priority_sort = 5,  a.priority_sort,  null ))  AS  'count5' 
FROM
	(  SELECT plant_id
			,workshop
			,NOW() as date_
			,IFNULL(po_priority_sort, 0) AS priority_sort
			FROM pdm_production_order) a
group by  workshop,plant_id

查询结果

3. 附：NOW()与SYSDATE()异同

mysql中日期函数主要是NOW()和SYSDATE()两种;NOW()取的是语句开始执行的时间，SYSDATE()取的是动态的实时时间。

NOW()取自mysql的一个变量”TIMESTAMP”，而这个变量在语句开始执行的时候就设定好,因此在整个语句执行过程中都不会变化。

执行下面这个例子就明白了：

SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE()

先查询了NOW()和SYSDATE()，然后sleep了3秒，再查询NOW()和SYSDATE()，结果如下：

NOW()还有3个同义词，效果跟NOW()一样，而且都有2种形式写法：
LOCALTIME或LOCALTIME()
LOCALTIMESTAMP或LOCALTIMESTAMP()
CURRENT_TIMESTAMP或CURRENT_TIMESTAMP()

参考文章链接: MySQL DML操作--------实现pivot行转列功能最佳实战.