MySQL按性别筛选指定数量数据的单查询实现及扩展问题

The Single Query to Get 2 Males and 4 Females #

Assuming your table is named users, here's the query that will fetch exactly what you need:

(SELECT id, username, sex 
 FROM users 
 WHERE sex = 1  -- Male
 ORDER BY id 
 LIMIT 2)
UNION ALL
(SELECT id, username, sex 
 FROM users 
 WHERE sex = 2  -- Female
 ORDER BY id 
 LIMIT 4);

Explanation:

• We use UNION ALL to combine two separate result sets. Unlike UNION, UNION ALL doesn’t waste time removing duplicates (which we don’t need here since our two sets are mutually exclusive by gender), so it’s faster.
• Each subquery filters for one gender, sorts by id (you can replace this with another column like username if you want a different ordering), and limits results to the number you specified.
• Note: Since your sample data only has 2 females, this query will return all 2 females instead of 4—MySQL automatically returns as many rows as exist when the limit is higher than available entries.

Multi-Condition Filtering in MySQL #

Multi-condition filtering lets you narrow down results using multiple rules. Here are the most common implementation methods:

1. Combine Conditions with AND

Use AND when you want all conditions to be true for a row to be included:

SELECT * FROM users 
WHERE sex = 1 
AND username LIKE 'A%';  -- Gets males whose username starts with 'A'

2. Combine Conditions with OR

Use OR when any of the conditions being true is enough to include the row:

SELECT * FROM users 
WHERE sex = 2 
OR username = 'H';  -- Gets all females OR the user with username 'H'

3. Use Parentheses for Precedence

When mixing AND and OR, always use parentheses to avoid unexpected results (since AND has higher precedence than OR):

SELECT * FROM users 
WHERE (sex = 1 AND username LIKE 'F%') 
OR (sex = 2 AND username LIKE 'G%');  -- Gets males starting with 'F' OR females starting with 'G'

4. Use IN for Multiple Values

Instead of writing multiple OR conditions, use IN to check if a value exists in a predefined list:

SELECT * FROM users 
WHERE username IN ('A', 'D', 'H');  -- Gets users with these exact usernames

5. Use BETWEEN for Range Checks

For numeric or date columns, BETWEEN simplifies range-based filtering:

-- Example if you had an age column:
SELECT * FROM users 
WHERE age BETWEEN 18 AND 30;  -- Gets users aged 18 to 30 inclusive

6. Complex Conditions with Subqueries

For advanced scenarios, use subqueries with EXISTS or IN to link data across tables:

-- Example: Get users who have placed at least one order (assuming an `orders` table)
SELECT * FROM users u 
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);

内容的提问来源于stack exchange，提问作者Saef Myth