如果我理解您的问题，您希望能够从第二个查询生成输出，而不改变初始CTE a（它可能表示实际的表或其他内容）。
有多种方法可以做到这一点，但有一种可能性是：

with a(shop_id, MON, TUE, WED, THUR, FRI, SAT, SUN) as (values (1,0,0,1,1,1,0,0),(2,1,1,1,1,0,0,0))
, b as (SELECT shop_id, MON + TUE + WED + THUR + FRI + SAT + SUN cnt FROM a)
SELECT shop_id, 'Working', cnt
FROM b
GROUP BY shop_id, cnt
UNION ALL
SELECT shop_id, 'Non-working', 7-cnt
FROM b
GROUP BY shop_id, cnt

在这里，CTE b包含每个shop_id的工作日总和。然后将union与非工作日（7-working）艾德。使用第二个CTE不是必要的，但可以避免重复将所有工作日相加的步骤。如果您愿意，可以执行以下操作：

with a(shop_id, MON, TUE, WED, THUR, FRI, SAT, SUN) as (values (1,0,0,1,1,1,0,0),(2,1,1,1,1,0,0,0))
SELECT shop_id, 'Working', MON + TUE + WED + THUR + FRI + SAT + SUN cnt
FROM a
UNION ALL
SELECT shop_id, 'Non-working', 7-(MON + TUE + WED + THUR + FRI + SAT + SUN)
FROM a

另一种选择是实际上取消透视表（将列转换为行），然后group byshop_id和day_type。

with a(shop_id, MON, TUE, WED, THUR, FRI, SAT, SUN) as (values (1,0,0,1,1,1,0,0),(2,1,1,1,1,0,0,0))
,
b AS (SELECT shop_id, CASE P.w WHEN 0 THEN 'Non-working' ELSE 'Working' END day_type
FROM a, TABLE (VALUES(a.MON),
                  (a.TUE),
                  (a.WED),
                  (a.THUR),
                  (a.FRI),
                  (a.SAT),
                  (a.SUN)) AS P(w))
SELECT shop_id, day_type, count(*) cnt
FROM b
GROUP BY shop_id, day_type

型
在上述所有情况下，都可以使用ORDER BY按照所需的顺序获取行。
这是一个Fiddle of these working。