如何在关联表时基于单条记录生成多行SQL查询结果？

Yes, you can absolutely achieve this requirement using SQL! The key idea is to use UNION ALL to generate the split record for the target post and combine it with the original records from your existing query.

Here's a query that produces exactly the output you're looking for:

-- Generate the split row for Post ID 1 where User's base is less than Post's min
SELECT 
    u.id AS user_id,
    u.name AS user_name,
    u.base AS user_base,
    p.id AS post_id,
    p.content AS post_content,
    u.base AS post_min,
    p.min AS post_max,
    p.user_id AS post_user_id
FROM users u
-- Include your existing joins here
JOIN table_1 ON condition_1
JOIN table_2 ON condition_2
JOIN posts p ON u.id = p.user_id
WHERE p.id = 1 AND u.base < p.min

UNION ALL

-- Get all original post records (including Post ID 1 and others)
SELECT 
    u.id AS user_id,
    u.name AS user_name,
    u.base AS user_base,
    p.id AS post_id,
    p.content AS post_content,
    p.min AS post_min,
    p.max AS post_max,
    p.user_id AS post_user_id
FROM users u
-- Include your existing joins here
JOIN table_1 ON condition_1
JOIN table_2 ON condition_2
JOIN posts p ON u.id = p.user_id

How this works: #

1. The first part of the UNION ALL creates the split record for Post ID 1, replacing the post's min with the user's base and setting the post's max to its original min—but only when the user's base is less than the post's min.
2. The second part pulls in all original post records, including the original Post ID 1 and Post ID 2.
3. Combining these with UNION ALL gives you the 3 rows you need: two for Post ID 1 (split + original) and one for Post ID 2.

Generalizing for all posts: #

If you want to split any post where the user's base is less than the post's min (not just Post ID 1), simply remove the p.id = 1 condition from the first query's WHERE clause. This will split all qualifying posts automatically.

You can also extend this logic to handle cases where the user's base falls between the post's min and max—just add another UNION ALL block to split the post into min→base and base→max intervals.

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