Mysql选择10条记录（5种，另5种）

Question

Using Mysql and PHP...

I have a table of ads, ads can be from ad_category "cars" or "general", I need to select 10 rows but make sure I have 5 cars and 5 general records.

Ideally would be if it only finds 3 cars then select 7 general.

Is that done by "SELECT distinct" ? And/or "GROUP BY" ?

Answer 1

You can do the 5/5 with a union all:

(select *
 from ads
 where ad_category = 'cars'
 limit 5
) union all
(select *
 from ads
 where ad_category = 'general'
 limit 5
)

EDIT:

If you really want 10 records under your circumstances, you can do it with this trick:

select *
from ((select *, (@cars_rn := @cars_rn + 1) as rn
       from ads
       where ad_category = 'cars' cross join
             (select @cars_rn := 0) const
       limit 10
      ) union all
      (select *, (@general_rn = @general_rn + 1) as rn
       from ads
       where ad_category = 'general' cross join
             (select @general_rn := 0) const
       limit 10
      )
     ) t
order by rn
limit 10;

This chooses 10 cars and 10 general, assigning a sequential row number to each. It then orders by the row number and chooses the first 10. This will guarantee 10 records (assuming at least 10 match) and will get a 5/5 split, if possible. Otherwise, it will take all of one and fill in the 10 with the other category.