如何让这个mysql查询包含空值？

Im writing a pruning script, to delete content from my site that was uploaded over a week ago, and been accessed 0 or 1 times, also in the last week.

我正在写一个修剪脚本,删除我的网站上一周前上传的内容,并在上周访问了0或1次。

there are 2 tables:

有2个表:

daily_hits - which stores the item id, date, and number of hits that item got on that date.

daily_hits - 存储项目ID,日期和项目在该日期获得的点击数。

videos - stores actual content

视频 - 存储实际内容

I came up with this.

我想出了这个。

$last_week_date = date('Y-m-d',$now-(60*60*24*7));
$last_week_timestamp = $now-(60*60*24*7);

SQL

SELECT 
            vid_id, 
            COALESCE(sum(hit_hits),0) as total_hits 
        FROM videos
        LEFT JOIN daily_hits
            ON vid_id = hit_itemid 
        WHERE (hit_date >= '$last_week_date') AND vid_posttime <= '$last_week_timestamp' 
        GROUP BY hit_itemid 
        HAVING  total_hits < 2

This does output the items that were access once in the last week.... but not the ones that haven't been accessed at all. If an item wasn't accessed at all in that last week, there wont be any entries in the daily_hits table. I figured COALESE should take care of that, but that didnt work.

这会输出上周访问过一次的项目....但不会输出那些根本没有访问过的项目。如果上周没有访问任何项目,则daily_hits表中不会有任何条目。我认为COALESE应该照顾它,但那没有用。

How can I fix this?

我怎样才能解决这个问题?

2 个解决方案

#1

 HAVING  total_hits < 2 or total_hits is null

#2

total_hits < 2

total_hits <2

This guarantees "null hits" won't show up.

这可以保证“空命”不会出现。

Make a second query that finds the nulls (it will show records from videos that have no corresponding key in daily_hits).

创建第二个查找空值的查询(它将显示来自daily_hits中没有相应键的视频的记录)。

Make a UNION query to present the two datasets as one.

创建UNION查询以将两个数据集显示为一个。

#1

 HAVING  total_hits < 2 or total_hits is null

#2