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'如果我使用此查询一天，它可以正常工作，但不适用于一个日期范围。
希望有人能帮忙。
先谢谢你
回答 4
Stack Overflow用户
回答已采纳
发布于 2013-10-15 07:39:59
如果我正确地理解了这一点，您希望在一个日期内组合成对的行，例如：
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-see这个小提琴
Stack Overflow用户
发布于 2013-10-15 07:39:59
试试这个：
SELECT
sum(if(metrics = 'visitorsStatistics', `count`, 0)) visitorsStatistics,
sum(if(metrics = 'pageviewsStatistics', `count`, 0)) pageviewsStatistics
FROM analytics_metrics am
WHERE <WHATEVER YOU NEED>
GROUP BY `date`小提琴这里.
Stack Overflow用户
发布于 2013-10-15 07:40:47
像这样检查一下。
SELECT if(metrics='visitorsStatistics',count) AS visitorsStatistics,if(metrics='pageviewsStatistics',count) AS pageviewsStatistics
FROM metrics_table
WHERE date BETWEEN '2013-10-10' AND '2013-10-15'
GROUP BY date页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19375580
复制相关文章
相似问题
腾讯云开发者
