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

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" ?

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.

Something along these lines might do the trick (didn't check any syntax). Get at least 5 cars and at least 10 general, then sort them by car and general before applying your final 10 limit. That gives you up to 5 cars if they exist, and fills the remaining with general (ie fulfills the 3 car, 7 general case).

SELECT x.ad
  FROM ( ( SELECT 1 AS pref, ad
             FROM ads
             WHERE ad_category = 'cars'
             LIMIT 5
          )

          UNION

          ( SELECT 2 AS pref, ad
              FROM ads
              WHERE ad_category = 'general'
              LIMIT 10
          )
        ) AS x
  ORDER BY x.pref, x.ad
  LIMIT 10