mysql从某些类别中选择一条记录，对于其他类别，此规则不受影响

Simplified view of issue.

Select only One record from category 2 and 3. From another categories any count allowed. id categoryid data 1 2 somedata 2 2 somedata 3 2 somedata 4 3 somedata 5 3 somedata 6 4 somedata 7 4 somedata 8 5 somedata 9 5 somedata 10 6 somedata 11 6 somedata 12 6 somedata 13 7 somedata 14 7 somedata 15 8 somedata

select * from table order by RAND() limit 10

Requirement is one query. Maybe stored procedure.

Expected result:

id categoryid data 8 5 somedata 6 4 somedata 14 7 somedata 9 5 somedata 1 2 somedata <---categoryid=2. only one record 12 6 somedata 4 3 somedata <---categoryid=3. only one record 13 7 somedata 7 4 somedata 15 8 somedata

Possibly using variables to come up with a sequence number:-

SELECT id, categoryid, data
FROM
(
    SELECT id, categoryid, data, @seq:=IF(@prev_cat = categoryid, @seq + 1, 1) AS CatSeq, @prev_cat = categoryid
    FROM someTable
    CROSS JOIN (SELECT @seq:=0, @prev_cat:=0) Sub1
    ORDER BY category_id, id
)
WHERE (categoryid IN (2,3) AND CatSeq = 1)
OR categoryid NOT IN (2,3)

Use a GROUP BY:

SELECT *
FROM your_table
GROUP BY categoryid
ORDER BY RAND()
LIMIT 10

As you want single row for category 2 and 3 but all rows for other categories, so you can Use below query:

SELECT *
FROM your_table
WHERE categoryid IN (2,3)
GROUP BY categoryid
UNION ALL
SELECT *
FROM your_table
WHERE categoryid NOT IN (2,3);

If you want random one value then you can use below:

SELECT *
FROM your_table
WHERE categoryid IN (2,3)
GROUP BY categoryid
ORDER BY RAND()
UNION ALL
SELECT *
FROM your_table
WHERE categoryid NOT IN (2,3);