我正在创建一个图形,每天可以获得一定范围的总体视图,或者只要返回。
我遇到的问题是填写默认值 0 当某天没有任何视图时,有一天可能绝对没有任何视图,所以我需要MySQL返回默认值 0 当没有找到 - 我不知道该怎么做。
这是我使用的查询获取每天的总浏览量:
SELECT DATE(FROM_UNIXTIME(v.date))AS date_views, COUNT v.view_id)AS total_views FROM (视图v ) GROUP BY date_views ORDER BY v.date DESC我的结果返回:
+ ------------ + ------------- + | date_views | total_views | + ------------ + ------------- + | 2012-10-17 | 2 | | 2012-10-15 | 5 | | 2012-10-14 | 1 | | 2012-10-10 | 7 | + ------------ + ------------- +但是,我想要返回 0 的缺少日子,因为 2012-10-16 , 2012-10-11 , 2012-10-12 , 2012-10-13 不包括在内。
所以,例如:
+ ------------ + ------------- + | date_views | total_views | + ------------ + ------------- + | 2012-10-17 | 2 | | 2012-10-16 | 0 | | 2012-10-15 | 5 | | 2012-10-14 | 1 | | 2012-10-13 | 0 | | 2012-10-12 | 0 | | 2012-10-11 | 0 | | 2012-10-10 | 7 | + ------------ + ------------- +将被退回。
如何处理?
解决方案几年前我做了这个,我创建了一个空数组,日期为key,默认值为0.然后我简单地循环结果att更改了这些日期的值每个$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ]] = $ row ['value']; }
I am creating a graph where I can get the total views everyday for a certain range, or as long it goes back.
The problem I am having is to fill a default number of 0 when no views has been made for a certain day, some days there may be absolutely no views in a day so I need MySQL to return a default of 0 when none is found - I have no idea how to do this.
This is the query I use to get the total views a day:
SELECT DATE(FROM_UNIXTIME(v.date)) AS date_views, COUNT(v.view_id) AS total_views FROM ( views v ) GROUP BY date_views ORDER BY v.date DESCMy results return this:
+------------+-------------+ | date_views | total_views | +------------+-------------+ | 2012-10-17 | 2 | | 2012-10-15 | 5 | | 2012-10-14 | 1 | | 2012-10-10 | 7 | +------------+-------------+However there are missing days that I want to return 0 for it, as 2012-10-16, 2012-10-11, 2012-10-12, 2012-10-13 is not included.
So, for example:
+------------+-------------+ | date_views | total_views | +------------+-------------+ | 2012-10-17 | 2 | | 2012-10-16 | 0 | | 2012-10-15 | 5 | | 2012-10-14 | 1 | | 2012-10-13 | 0 | | 2012-10-12 | 0 | | 2012-10-11 | 0 | | 2012-10-10 | 7 | +------------+-------------+Would be returned.
How would this be approached?
解决方案When I did this a couple of years ago I created an empty array with the date as key and the default value 0. Then I simply looped through the result att changed the value for those dates I had.
for each($result as $row){ $date_stats_array[$row['date']] = $row['value']; }
更多推荐
获取每天的总观看次数,包括离开“0视图”一天没有记录
发布评论