从CTE中的CTE进行分组

Question

我有一个像这样的CTE -

with cte (
    org1, org2, org3
    , ind1, ind2, ind3
  ) as (
    -- a bunch of queries
    ) 
  SELECT 
    org1 as org, 
    ind2 as ind, 
    count(*) as count_final 
  from 
    cte 
  group by 
    org1, 
    ind2 
  union all 
  SELECT 
    org1 as org, 
    ind3 as ind, 
    count(*) as count_final 
  from 
    cte 
  group by 
    org1, 
    ind3 
  union all 
  SELECT 
    org2 as org, 
    ind1 as ind, 
    count(*) as count_final 
  from 
    cte 
  group by 
    org2, 
    ind1 
  union all 
  SELECT 
    org2 as org, 
    ind3 as ind, 
    count(*) as count_final 
  from 
    cte 
  group by 
    org2, 
    ind3 
  union all 
  SELECT 
    org3 as org, 
    ind1 as ind, 
    count(*) as count_final 
  from 
    cte 
  group by 
    org3, 
    ind1 
  union all 
  SELECT 
    org3 as org, 
    ind2 as ind, 
    count(*) as count_final 
  from 
    cte 
  group by 
    org3, 
    ind2;

一旦我有了org，ind和count_final，我该怎么做-

select org, ind, sum(count_final) as count from cte_final
group by org, ind

我试图在这个cte之上创建另一个cte，这样我就可以运行这个查询了，但是我得到了一个错误，指出存在对org的不明确引用。这是我试过的-

with cte_final (org, ind)
as (
with cte (org1, org2, org3, ind1, ind2, ind3)
as (

--a bunch of queries

    )

SELECT org1 as org , ind2 as ind, count(*) as count_final from cte
group by org1 , ind2 
union all
SELECT org1 as org , ind3 as ind, count(*) as count_final from cte
group by org1 , ind3 
union all
SELECT org2 as org , ind1 as ind, count(*) as count_final from cte
group by org2 , ind1
union all
SELECT org2 as org , ind3 as ind, count(*) as count_final from cte
group by org2 , ind3
union all
SELECT org3 as org , ind1 as ind, count(*) as count_final from cte
group by org3 , ind1
union all
SELECT org3 as org , ind2 as ind, count(*) as count_final from cte
group by org3 , ind2
;
)
select org, ind, sum(count_final) as count from cte_final
group by org, ind;

输入-

Org             Ind             Count_Final
"1023043221"    "156832423986"  "17"
"1023043221"    "156832423986"  "1"
"14325495102"   "142722349564"  "1"

我想要这个结果-

Org             Ind             Count_Final
"1023043221"    "156832423986"  "18"
"14325495102"   "142722349564"  "1"

Answer 1

我想你只需要一个cte。尝试：

WITH cte
AS (

--bunch of queries

    )
SELECT org
    , ind
    , sum(count_final) AS count
FROM (
    SELECT org1 AS org
        , ind2 AS ind
        , count(*) AS count_final
    FROM cte
    GROUP BY org1
        , ind2

    UNION ALL

    SELECT org1 AS org
        , ind3 AS ind
        , count(*) AS count_final
    FROM cte
    GROUP BY org1
        , ind3

    UNION ALL

    SELECT org2 AS org
        , ind1 AS ind
        , count(*) AS count_final
    FROM cte
    GROUP BY org2
        , ind1

    UNION ALL

    SELECT org2 AS org
        , ind3 AS ind
        , count(*) AS count_final
    FROM cte
    GROUP BY org2
        , ind3

    UNION ALL

    SELECT org3 AS org
        , ind1 AS ind
        , count(*) AS count_final
    FROM cte
    GROUP BY org3
        , ind1

    UNION ALL

    SELECT org3 AS org
        , ind2 AS ind
        , count(*) AS count_final
    FROM cte
    GROUP BY org3
        , ind2
    ) x
GROUP BY org
    , ind;