SQL入門
SQL基礎問題10：非正規化（カンマ区切り）の結合と集計

SELECT A.ID,A.B_ID,B.C_IDs,IFNULL(SUM(C.値), 0) AS 値合計
FROM TBLA AS A
LEFT JOIN TBLB AS B
       ON A.B_ID = B.ID
LEFT JOIN TBLC AS C
       ON (',' || B.C_IDs || ',') LIKE ('%,' || C.ID || ',%')
      AND (C.終了日 > STRFTIME('%Y/%m/%d', DATE('now')) OR C.終了日 IS NULL)
GROUP BY A.ID,A.B_ID,B.C_IDs

SELECT A.ID,A.B_ID,B_SUM.C_IDs,IFNULL(B_SUM.合計値, 0) AS 値合計
FROM TBLA AS A
LEFT JOIN (SELECT B.ID AS B_ID,B.C_IDs,SUM(C.値) AS 合計値
           FROM TBLB AS B
           LEFT JOIN TBLC AS C
                  ON (',' || B.C_IDs || ',') LIKE ('%,' || C.ID || ',%')
                 AND (C.終了日 > DATE('now') OR C.終了日 IS NULL)
           GROUP BY B.ID, B.C_IDs
          ) AS B_SUM
       ON A.B_ID = B_SUM.B_ID
GROUP BY A.ID, A.B_ID, B_SUM.C_IDs

WITH BC_Join AS
    (SELECT B.ID AS B_ID,B.C_IDs,SUM(C.値) AS 合計値
     FROM TBLB AS B
     LEFT JOIN TBLC AS C
            ON (',' || B.C_IDs || ',') LIKE ('%,' || C.ID || ',%')
           AND (C.終了日 > STRFTIME('%Y/%m/%d', DATE('now')) OR C.終了日 IS NULL)
     GROUP BY B.ID, B.C_IDs
)
SELECT A.ID,A.B_ID,BCJ.C_IDs,IFNULL(BCJ.合計値, 0) AS 値合計
FROM TBLA AS A
LEFT JOIN BC_Join AS BCJ
       ON A.B_ID = BCJ.B_ID
GROUP BY A.ID, A.B_ID, BCJ.C_IDs

=LET(sql,"
SELECT A.ID,A.B_ID,B.C_IDs,IIF(IsNull(SUM(C.値)), 0, SUM(C.値)) AS 値合計
FROM ((?TBLA AS A
LEFT JOIN ?TBLB AS B
       ON A.B_ID = B.ID)
LEFT JOIN ?TBLC AS C
       ON (',' & B.C_IDs & ',') LIKE ('%,' & C.ID & ',%')
      AND (FORMAT(C.終了日,'yyyymmdd') > FORMAT(DATE(),'yyyymmdd') OR C.終了日 =''))
GROUP BY A.ID,A.B_ID,B.C_IDs
",
sql_1,SUBSTITUTE(sql,"?TBLA","[CSV$A2:B8]"),
sql_2,SUBSTITUTE(sql_1,"?TBLB","[CSV$D2:E10]"),
sql_3,SUBSTITUTE(sql_2,"?TBLC","[CSV$G2:I12]"),
QUERY(sql_3))

=LET(sql,"
SELECT A.ID,A.B_ID,B_SUM.C_IDs,IIF(IsNull(SUM(B_SUM.合計値)), 0, SUM(B_SUM.合計値)) AS 値合計
FROM ?TBLA AS A
LEFT JOIN (SELECT B.ID AS B_ID,B.C_IDs,SUM(C.値) AS 合計値
           FROM ?TBLB AS B
           LEFT JOIN ?TBLC AS C
                  ON (',' & B.C_IDs & ',') LIKE ('%,' & C.ID & ',%')
                 AND (FORMAT(C.終了日,'yyyymmdd') > FORMAT(DATE(),'yyyymmdd') OR C.終了日 ='')
           GROUP BY B.ID, B.C_IDs
          ) AS B_SUM
       ON A.B_ID = B_SUM.B_ID
GROUP BY A.ID, A.B_ID, B_SUM.C_IDs
",
sql_1,SUBSTITUTE(sql,"?TBLA","[CSV$A2:B8]"),
sql_2,SUBSTITUTE(sql_1,"?TBLB","[CSV$D2:E10]"),
sql_3,SUBSTITUTE(sql_2,"?TBLC","[CSV$G2:I12]"),
QUERY(sql_3))