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