Mysql连接使用日期
Mysql连接使用日期
提问于 2013-10-15 07:33:04
我有一张桌子analytics_metrics。我正试图从visitorsStatistics和pageviewsStatistics那里得到最后x天的计数。日期范围可以更改。
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'如果我使用此查询一天，它可以正常工作，但不适用于一个日期范围。
希望有人能帮忙。
先谢谢你
Stack Overflow用户
发布于 2013-10-15 07:43:58
尝尝这个
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 DATE_ADD(fromDate, INTERVAL 1 DAY) and DATE_SUB(tillDate, INTERVAL 1 DAY) AND ga_visits.metrics = 'visitsStatistics'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19375580
复制相关文章
相似问题
腾讯云开发者
