php - 简单数据透视查询帮助

Question

考虑这个查询:

SELECT COUNT(*) AS pageviews 
FROM stats_hits 
WHERE pageID='1' AND entrytime>DATE_SUB(now(), INTERVAL 5 DAY) 
GROUP BY DATE(`entrytime`) LIMIT 5

过去五天的返回点击次数:类似于:

11
12
18
15
5

如何重写此查询以返回水平结果集？

11, 12, 18, 15, 5

谢谢!

编辑:作为一个附带问题，有谁知道如何调整此查询以显示 0 几天没有点击？目前，如果某一天没有点击，则仅返回 4 个结果。

Answer 1

SELECT GROUP_CONCAT(s.pageviews) AS pageviews
FROM
  (
  SELECT COUNT(*) AS pageviews 
  FROM stats_hits 
  WHERE pageID='1' AND entrytime>DATE_SUB(now(), INTERVAL 5 DAY) 
  GROUP BY DATE(`entrytime`) LIMIT 5
  ) s;

参见:http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

如果你想总是有 5 个结果，像这个丑陋的 hack 之类的东西可能会起作用。

SELECT GROUP_CONCAT(s2.pageviews) AS pageviews
FROM 
  (
  SELECT * FROM
    (
    SELECT COUNT(*) AS pageviews 
    FROM stats_hits 
    WHERE pageID='1' AND entrytime>DATE_SUB(now(), INTERVAL 5 DAY) 
    GROUP BY DATE(`entrytime`) LIMIT 5
    UNION ALL SELECT 0 AS pageviews
    UNION ALL SELECT 0 AS pageviews
    UNION ALL SELECT 0 AS pageviews
    UNION ALL SELECT 0 AS pageviews
    UNION ALL SELECT 0 AS pageviews
    ) s1
  ORDER BY pageviews = 0 ASC
  LIMIT 5) s2;

另一个选项是这个

CREATE TEMPORARY TABLE temp_table LIKE SELECT 0 as pageviews;
INSERT INTO temp_table VALUES (0),(0),(0),....,(0);
INSERT INTO temp_table 
  SELECT COUNT(*) AS pageviews 
  FROM stats_hits 
  WHERE pageID='1' AND entrytime>DATE_SUB(now(), INTERVAL x DAY) 
  GROUP BY DATE(`entrytime`) LIMIT x; //replace x with the number of days

SELECT GROUP_CONCAT(t.pageviews) AS pageviews
FROM temp_table t
ORDER BY t.pageview = 0 ASC 
LIMIT x;  //replace x with the number of days