MySQL - 为每X行选择1 [英] MySQL - Select 1 for each X rows

问题描述

我有一个数据库，其中包含 id 和 store_id 。 id 是AI和 store_id 一个介于0和7之间的整数。

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.

我的目标是为每2行选择例如1，所以我会得到行id = 0，id = 2，id = 4 ..等。我可以这样做：

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

但我的问题是，我 store_id ，因为我想获得1每个x行 WHERE store_id = y

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

你能帮我一下吗？

推荐答案

首先：

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

看起来像是一个想法，但是所有的store_id = y有奇怪的id呢？

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

这篇关于MySQL - 为每X行选择1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！