选择一列中具有非空值的最新记录 [英] SELECT the newest record with a non null value in one column
本文介绍了选择一列中具有非空值的最新记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有看起来像这样的表格数据
I have table data which looks like this
id | keyword | count | date
1 | ipod | 200 | 2009-08-02
2 | ipod | 250 | 2009-09-01
3 | ipod | 150 | 2009-09-04
4 | ipod | NULL | 2009-09-07
现在我所追求的是获取具有最新日期但计数不为空的行的计数.在这种情况下,第 3 行的计数为 150.)
Now what I am after is getting the count of the row which has the newest date but has a not null count. In which case row 3 with count 150.)
例如
SELECT `keyword`, `count` , max( `date` )
FROM `keywords`
WHERE keyword = "ipod"
AND `count` IS NOT NULL
GROUP BY keyword
这返回了正确的日期,但没有正确的计数(返回 200).我也试过对它自己做一个左连接.
This returned the right date but not the right count (returned 200). I also tried doing a left join on it's self.
SELECT `t1`.`keyword` , `t2`.`count` , max( `t1`.`id` )
FROM `keywords` `t1`
LEFT JOIN `keywords` `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`keyword` = 'ipod'
AND `t1`.`count` IS NOT NULL
GROUP BY `t1`.`keyword`
这确实获得了最大 id,但它没有像我希望的那样左加入那个,只返回 200.
And this did get the max id but it didn't left-join onto that like I hoped and returned only 200.
帮助?
推荐答案
SELECT `keyword`, `count` , `date`
FROM `keywords`
WHERE keyword = "ipod"
AND `count` IS NOT NULL
order by date desc
limit 1
这篇关于选择一列中具有非空值的最新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文