[mysql]关于order by

Question

我设置了topics和comments两个表如下
topics
--id
--create_timestamp
--author
--content

comments
--id
--topic_id
--create_timestamp
--author
--content

由于懒得想Join,Groupby那一套然,就用嵌套select写了这样很囧的sql

[code]select *,(select create_timestamp from comments where comments.id=topics.id) as update_timestamp order by update_timestamp,create_timestamp desc[/code]

然后问题就出现了,结果的排列顺序并非理想化的新Topic自动到达顶部,由于一个新topic没有comments,上面得到的update_timestamp为null,我想实现的是如果update_timestamp为null,则令update_timestamp=create_timestamp,不知道该怎样实现?

Answer 1

select *, (select ifnull(create_timestamp, update_timestamp) from comments where comments.id=topics.id) as update_timestamp order by update_timestamp, create_timestamp desc

mysql有ifnull(a,b)函数