mysql 每隔x行取,MySQL - 为每X行选择1

I have a database with the some fields including id and store_id. id is AI and store_id an int between 0 and 7.

My goal is to select for example 1 for each 2 rows, so i'd get row id=0, id=2, id=4 .. etc. I could do that with:

SELECT * FROM `table` WHERE MOD(id,2)=0

but my problem is that i that store_id because i wanted to get 1 for each x rows WHERE store_id=y

Can you help me with this? If you need any other detail feel free to ask, thanks

解决方案

At first :

SELECT * FROM table WHERE MOD(id, 2) = 0 AND store_id = y

Seems an idea, but what about having all "store_id = y" having odd id's ?

So, what about running a subquery to apply filters, adding a counter to results, and then filter over the counter :

set @counter = 0;

SELECT id, store_id FROM

(

SELECT id, store_id,

@counter := @counter + 1 as counter

FROM foo WHERE store_id = 2

) AS sub

WHERE MOD(counter, 2) = 0