Can I do that in the SQL statement?

Let say I have a post table. But I want to query all today post. But if today post is less than 10 post, I will get back the yesterday post to query. If it is more than 10 posts, no开发者_如何学Go need to query yesterday post....If SQL statement can't do it. Is this only achieve it by calling the post manually....? Thank you.

***The database is MySQL

Let me clarify the question in a typical example:

If today have 5 posts....ONLY. And yesterday have 10 posts.

return : 5 today posts, and 5 posts from yesterday

If today have 12 posts....ONLY.
And yesterday have 10 posts. 

return : 12 today posts.

If today have 10 posts....ONLY. And yesterday have 10 posts.

return : 10 today posts.

If today have 2 posts....ONLY. yesterday have 5 posts, and the day before yesterday 5posts. 

return : 2 today posts, 5 yesterday posts, 3 the day before yesterday posts.

You can try

select count(*) from post_table
where date = todays_date

and if the result is > 10 then

select * from post_table
where date = today's date

else

select * from post_table  
order by date desc
limit 10

Just another idea, a little bit shorter:

set @i = 0;
select *, @i := @i + 1
from post_table
where @i < 10 or date = today
order by date desc;

Not sure it is very effective.

Update: it is fast! I tested on the such sample:

create table a(i int primary key, d date not null, index idx(d)) 
set @i = 0;

insert into a(i, d)
select @i := @i + 1, adddate(curdate(), interval -(@i % 1000) day) 
from <100 records> a, <100 records> b, <100 records> c

A tiny development on Jan S's solution (combines the two conditional SELECTs into one with a parametrised LIMIT):

SELECT @count := COUNT(*)
FROM post_table
WHERE date = today;

IF @count < 10 SET @count = 10;

SELECT *
FROM post_table
ORDER BY date DESC
LIMIT @count;

UPDATE

As stated in the documentation:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

• Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

That means, you can only use code like above in a stored procedure, not in a plain query you are issuing in your client application.