如何通过SQL实现按页码合并章节数据为单行（适配Datatables）

Hey there! Let's fix this so each page shows up as a single row with all chapter values filled in correctly. The issue with your current query is that you're grouping by both page and chapter, which creates separate rows for each chapter-page pair. Here's how to adjust it:

Key Changes Needed #

• Use aggregate functions to capture non-empty values: For each chapter column, wrap your IF() logic with MAX()—since only one row per page-chapter will have a non-empty string, MAX() will pull that valid value and ignore the empty ones.
• Group only by page: Remove mc_ee_chp_id from your GROUP BY clause so all rows for the same page are rolled up into one.

Updated SQL Query #

SELECT 
    `mc_ee_page_id` as Page,
    MAX(IF(mc_ee_chp_id = 1, 
        CASE 
            WHEN (is_marked = 0 AND mk_no_mark = 1) THEN "Y"
            WHEN (is_marked = 1 AND mk_no_mark = -1) THEN "N"
            WHEN (is_marked = 1 AND mk_no_mark = 1) THEN "O"
            ELSE "X" 
        END, 
        "")) AS chp_1,
    MAX(IF(mc_ee_chp_id = 2, 
        CASE 
            WHEN (is_marked = 0 AND mk_no_mark = 1) THEN "Y"
            WHEN (is_marked = 1 AND mk_no_mark = -1) THEN "N"
            WHEN (is_marked = 1 AND mk_no_mark = 1) THEN "O"
            ELSE "X" 
        END, 
        "")) AS chp_2,
    MAX(IF(mc_ee_chp_id = 3, 
        CASE 
            WHEN (is_marked = 0 AND mk_no_mark = 1) THEN "Y"
            WHEN (is_marked = 1 AND mk_no_mark = -1) THEN "N"
            WHEN (is_marked = 1 AND mk_no_mark = 1) THEN "O"
            ELSE "X" 
        END, 
        "")) AS chp_3,
    -- Repeat this MAX(IF(...)) pattern for chp_4 through chp_11
    MAX(IF(mc_ee_chp_id = 12, 
        CASE 
            WHEN (is_marked = 0 AND mk_no_mark = 1) THEN "Y"
            WHEN (is_marked = 1 AND mk_no_mark = -1) THEN "N"
            WHEN (is_marked = 1 AND mk_no_mark = 1) THEN "O"
            ELSE "X" 
        END, 
        "")) AS chp_12
FROM `maxcare_mc_ee_hw`
WHERE `stud_id` = '3312' AND `mc_ee_level_id` = '1'
GROUP BY mc_ee_page_id
ORDER BY mc_ee_page_id ASC
LIMIT 500

How This Works #

For each page, the query looks at all rows associated with that page. For each chapter column (chp_1 to chp_12), the MAX() function picks out the non-empty value generated by the IF() condition (since empty strings are treated as lower priority than "Y", "N", "O", or "X"). This collapses all chapter data for a page into a single row, exactly what you need.

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