SQL实现双有序表交错查询：按2产品+1艺术品的规则交替排列结果集

Got it, let's tackle this interleaving merge problem. The goal is to alternate between batches of 2 product records and 1 artwork record, sticking to their original date-sorted order. Here's how you can pull this off with SQL:

Step-by-Step SQL Query #

WITH products_with_groups AS (
    -- Assign group numbers to products: every 2 records form a single group
    SELECT 
        date,
        name,
        FLOOR((ROW_NUMBER() OVER(ORDER BY date) - 1) / 2) AS group_num,
        1 AS display_priority, -- Makes sure products show first in each group
        ROW_NUMBER() OVER(ORDER BY date) AS inner_sequence
    FROM products
),
artworks_with_groups AS (
    -- Assign group numbers to artworks: each record matches a product batch
    SELECT 
        date,
        name,
        ROW_NUMBER() OVER(ORDER BY date) - 1 AS group_num,
        2 AS display_priority, -- Artworks come after products in the same group
        1 AS inner_sequence
    FROM artworks
)
-- Combine and sort to get the interleaved result
SELECT date, name
FROM (
    SELECT date, name, group_num, display_priority, inner_sequence FROM products_with_groups
    UNION ALL
    SELECT date, name, group_num, display_priority, inner_sequence FROM artworks_with_groups
) combined_records
ORDER BY 
    group_num,        -- Keep batches grouped together first
    display_priority, -- Show products before artworks in each batch
    inner_sequence;   -- Preserve original date order from each table

How This Works #

• Product Grouping: Using FLOOR((ROW_NUMBER() - 1)/2) creates groups of 2 records. For example:
  • The first 2 products get group_num = 0
  • The next 2 (if any) get group_num = 1, and so on
• Artwork Grouping: Each artwork is assigned a group_num that lines up with product batches—so the first artwork pairs with the first 2 products, the second artwork pairs with the next 2 products, etc.
• Sorting Logic:
  1. First sort by group_num to keep matching batches together
  2. display_priority ensures products always appear before artworks in the same batch
  3. inner_sequence maintains the original date-sorted order from each source table

Test Result with Your Sample Data #

Running this query on your provided tables will return exactly the output you expected:

date name
09/01/2021 P1
12/01/2021 P2
19/01/2018 A1
22/01/2021 P3
27/02/2019 A2

(Note: The third artwork A3 will appear at the end if you include it, since there are no remaining product batches to pair it with)

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