mysql语法积累

1.纵向数据转横向，取今天数据、昨天数据。

SELECT
	Max(
		CASE st.name
		WHEN 'pv_all' THEN
			st.value
		ELSE
			0
		END
	) 'pv_all',
	Max(
		CASE st.name
		WHEN 'like_all' THEN
			st.value
		ELSE
			0
		END
	) 'like_all',
	Max(
		CASE st.name
		WHEN 'pv_today' THEN
			st.value
		ELSE
			0
		END
	) 'pv_today',
	Max(
		CASE st.name
		WHEN 'like_today' THEN
			st.value
		ELSE
			0
		END
	) 'like_today',
	Max(
		CASE st.name
		WHEN 'pv_yesterday' THEN
			st.value
		ELSE
			0
		END
	) 'pv_yesterday',
	Max(
		CASE st.name
		WHEN 'like_yesterday' THEN
			st.value
		ELSE
			0
		END
	) 'like_yesterday'
FROM
	(
		SELECT 'pv_all' AS name,COUNT(1) AS value FROM visit_log WHERE visit_type in ('0','1','2')
		UNION
		SELECT 'like_all' AS name,COUNT(1) AS value FROM visit_log WHERE visit_type = '3'
		UNION
		SELECT 'pv_today' AS name,COUNT(1) AS value FROM visit_log WHERE TO_DAYS(visit_datetime) = TO_DAYS(NOW()) AND visit_type in ('0','1','2')
		UNION
		SELECT 'like_today' AS name,COUNT(1) AS value FROM visit_log WHERE TO_DAYS(visit_datetime) = TO_DAYS(NOW()) AND visit_type = '3'
		UNION
		SELECT 'pv_yesterday' AS name,COUNT(1) AS value FROM visit_log WHERE DATEDIFF(visit_datetime,NOW())=-1 AND visit_type in ('0','1','2')
		UNION
		SELECT 'like_yesterday' AS name,COUNT(1) AS value FROM visit_log WHERE DATEDIFF(visit_datetime,NOW())=-1 AND visit_type = '3'
	) st