Question

Consider this query:

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

感谢!

EDIT:作为一面问题,任何人都知道如何将这种争 que引出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;

见:。

如果你们总是想取得5个成果,那么像这一丑恶的黑板一样可能发挥作用。

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

友情链接