PhpMyAdmin实现每4行后插入显示特定CategoryID=1的行

Got it, let's break this down. The goal is to display your 100-record table such that after every 4 non-CategoryID=1 rows, we show a CategoryID=1 row. And if the first row is already CategoryID=1, we still count 4 non-1 rows before inserting the next 1.

We'll use SQL to create the desired result set (no need to mess with your original table unless you specifically want to—this is just for display).

The Query #

Here's a robust query that handles both cases (whether you have existing CategoryID=1 rows or not):

WITH non_one_records AS (
    -- Number all non-CategoryID=1 records, ordered by your original ID (adjust if needed)
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS row_num
    FROM your_table_name
    WHERE CategoryID != 1
),
chunked_non_one AS (
    -- Group non-1 records into chunks of 4
    SELECT *, CEIL(row_num / 4) AS chunk_id
    FROM non_one_records
),
-- Grab a single sample CategoryID=1 row to insert (adjust LIMIT if you want a specific one)
sample_one_row AS (
    SELECT * FROM your_table_name WHERE CategoryID = 1 LIMIT 1
),
-- Create an insert row for each chunk
insert_rows AS (
    SELECT chunk_id, sample_one_row.*
    FROM chunked_non_one
    CROSS JOIN sample_one_row
    GROUP BY chunk_id
)
-- Combine everything in the right order
SELECT * FROM (
    -- Original non-1 records, sorted by chunk
    SELECT *, chunk_id * 2 - 1 AS sort_key
    FROM chunked_non_one
    
    UNION ALL
    
    -- Inserted CategoryID=1 rows, placed right after their chunk
    SELECT *, chunk_id * 2 AS sort_key
    FROM insert_rows
    
    UNION ALL
    
    -- Original CategoryID=1 records, show them first
    SELECT *, 0 AS sort_key
    FROM your_table_name
    WHERE CategoryID = 1
) AS combined_results
ORDER BY sort_key, row_num;

How This Works #

Let's walk through each part step by step:

• non_one_records: We isolate all rows that aren't CategoryID=1 and assign each a sequential number, ordered by your table's ID column (change the ORDER BY here if you want to sort by Name or another field instead).
• chunked_non_one: We group those numbered rows into chunks of 4 using CEIL(row_num /4)—so rows 1-4 are chunk 1, 5-8 are chunk 2, etc.
• sample_one_row: We pull one CategoryID=1 row to use as the "inserted" row (if you have multiple CategoryID=1 rows and want a specific one, add a WHERE clause here, like WHERE ID=5).
• insert_rows: We create a copy of that sample row for each chunk—this gives us exactly one row to insert after every 4 non-1 records.
• Final Combine: We merge three sets of data:
  • Original non-1 rows (sorted so chunks come in order)
  • Inserted 1 rows (placed right after their corresponding chunk)
  • Original 1 rows (marked with a sort key of 0 so they show up first, which handles the case where the first row is CategoryID=1)

Running This in PhpMyAdmin #

1. Open PhpMyAdmin, select your database, and click the SQL tab.
2. Replace your_table_name with the actual name of your table in the query.
3. Tweak any parts (like the ORDER BY in non_one_records or the sample row selection) if needed.
4. Click Go—you'll see your desired result set with a CategoryID=1 row after every 4 non-1 rows.

If You Want to Physically Insert Rows #

If you don't just want to display the data, but actually add these CategoryID=1 rows to your table, let me know—I can adjust the query to do that. It's a bit different since we have to handle auto-increment IDs and avoid duplicates, but it's totally doable.

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