MySQL查询相同数量的状态返回一组记录

Question

如果我有电话列和州列。我可以查询特定的 LIMIT(假设为 500)，但我希望状态列中可用的每个状态在 500 计数查询中具有相同的数量。

例如: table =铁人

appid
phone(PK)
firstname
lastname
address
city
state
zip
called_count

查询:

SELECT 
FROM ironman
(A scripts that gives me (x amount) of phone numbers from each state)
WHERE called_count <= 4
LIMIT 500 OFFSET 0;

Answer 1

使用 ROW_NUMBER() 为行分配状态组内的位置。然后按该位置对行进行排序。然后，当您应用 LIMIT 时，每个州的行数将相同 (+-1) -(假设每个州都有足够的电话号码)。

with numbered as (
  select i.*
       , row_number() over (partition by state order by phone) pos
  from ironman i
  -- put your WHERE clause here
), limited as (
  select *
  from numbered
  order by pos asc, state asc
  limit 6 -- set your limit here
)
select *
from limited
order by state, phone

db-fiddle

如果您希望随机选择电话号码 - 更改位置的顺序

row_number() over (partition by state order by rand()) pos