Mysql加入使用日期之间

Question

我有一个表 analytics_metrics。我正在尝试从过去 x 天的 visitorsStatistics 和 pageviewsStatistics 中获取计数。日期范围可以更改。

id  metrics             count   date
67  visitorsStatistics  15779   2013-10-10
69  pageviewsStatistics 282141  2013-10-10
90  visitorsStatistics  14588   2013-10-11
92  pageviewsStatistics 265042  2013-10-11
108 pageviewsStatistics 278523  2013-10-12
106 visitorsStatistics  15015   2013-10-12
122 visitorsStatistics  16474   2013-10-13
124 pageviewsStatistics 312752  2013-10-13
138 visitorsStatistics  16829   2013-10-14
140 pageviewsStatistics 320614  2013-10-14
85  pageviewsStatistics 67976   2013-10-15
83  visitorsStatistics  5452    2013-10-15

我希望得到这样的输出:

visitorsStatistics   pageviewsStatistics
15779                282141 
14588                265042 
15015                278523 
16474                312752 
16829                320614 
5452                 67976  

我已经尝试了 4 个多小时的不同查询，现在我似乎无法找到正确的方法:-(。

这是我到目前为止得到的:

SET @fromDate = '2013-10-10'; 
set @tillDate = '2013-10-11';

SELECT 
  *
   /* ga_visits.count as visits,
    ga_pageviews.count as pageviews
*/
 FROM analytics_metrics as ga_visits
    LEFT JOIN analytics_metrics as ga_pageviews on (ga_pageviews.date BETWEEN @fromDate AND @tillDate AND ga_pageviews.metrics = 'pageviewsStatistics')
    WHERE ga_visits.date BETWEEN @fromDate AND @tillDate AND ga_visits.metrics = 'visitsStatistics'

如果我使用此查询一天它工作正常但不适用于日期范围。

希望有人能提供帮助。

提前谢谢你

Answer 1

如果我没记错的话，您想在一个日期内合并成对的行，例如:

SELECT
  l.count AS visitorsStatistics,
  r.count AS pageviewsStatistics
FROM
  (SELECT * FROM analytics_metrics WHERE metrics='visitorsStatistics') AS l
  LEFT JOIN
  (SELECT * FROM analytics_metrics WHERE metrics='pageviewsStatistics') AS r
    ON l.date=r.date
WHERE
  l.date BETWEEN @fromDate AND @tillDate

-看这个fiddle