基于CASE与GROUP BY结果新增列续：按id_laporan分组生成test2列

Alright, let's tackle this. You've already got the test column sorted out using Gordon Linoff's approach, and now you need to group by id_laporan to add the new test2 column with the rule: if any value in test for that id_laporan is 0, set test2 to 0. Here are two straightforward ways to make this happen, depending on your end goal:

方法1：仅获取id_laporan级别的聚合结果 #

If you just need one row per id_laporan with its corresponding test2 value, wrap your existing query in a CTE (or subquery) and use an aggregate check to simplify the logic:

WITH existing_results AS (
    -- 替换成你之前用Gordon方案得到的查询
    -- 该查询需包含 id_laporan, id_laporan_rekomendasi, test 三列
    SELECT 
        id_laporan,
        id_laporan_rekomendasi,
        CASE WHEN [your original test logic] THEN 0 ELSE 1 END AS test
    FROM your_table
    GROUP BY id_laporan, id_laporan_rekomendasi
)
SELECT
    id_laporan,
    -- 核心逻辑：如果分组内有任何test=0，MIN(test)会返回0，否则为1
    CASE WHEN MIN(test) = 0 THEN 0 ELSE 1 END AS test2
FROM existing_results
GROUP BY id_laporan;

If your test column might have values other than 0 and 1 (unlikely, but just in case), use an EXISTS check for more precise matching:

WITH existing_results AS (
    -- 同上，替换为你的原有查询
)
SELECT
    DISTINCT id_laporan,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM existing_results er2 
            WHERE er2.id_laporan = er.id_laporan 
            AND er2.test = 0
        ) THEN 0 
        ELSE 1 
    END AS test2
FROM existing_results er;

方法2：保留明细行并添加test2列 #

If you want to keep all original rows from your previous result (with id_laporan_rekomendasi and test) but add the test2 value for each row's parent id_laporan group, use a window function instead of re-grouping:

WITH existing_results AS (
    -- 同上，替换为你的原有查询
)
SELECT
    id_laporan,
    id_laporan_rekomendasi,
    test,
    -- 窗口函数：基于id_laporan分组，自动匹配对应组的test2值
    CASE WHEN MIN(test) OVER (PARTITION BY id_laporan) = 0 THEN 0 ELSE 1 END AS test2
FROM existing_results;

This way, every row gets the test2 value that applies to its parent id_laporan, without collapsing the rows into a single group per id_laporan.

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