SQL分组

我喜欢分步骤工作:先去掉重复的A记录,然后分组。不是最有效的,但它适用于您的示例。

with t1 as (
    select A, max(B) as B, C 
        from YourTable
        group by A, C
)
select count(A) as CountA, B, C
    from t1
    group by B, C

我已经测试过了:

SELECT 
    MAX( B ) AS B,
    C,
    Count 
FROM
(
    SELECT
        B, C, COUNT(DISTINCT A) AS Count
    FROM
        t
    GROUP BY
    B, C
) X
GROUP BY C, Count

它给了我:

 B     C     Count    
 ----  ----  -------- 
 15    X     1        
 15    y     1        
 20    y     2        

WITH cteA AS 
(
    SELECT 
        A, C, 
        MAX(B) OVER(PARTITION BY A, C) [Max]
    FROM T1
)

SELECT 
    [Max] AS B, C, 
    COUNT(DISTINCT A) AS [Count]
FROM cteA
GROUP BY C, [Max];

看看这个。这应该在Oracle中有效,尽管我没有测试过它;

select count(a), BB, CC from
(
select a, max(B) BB, Max(C) CC
from yourtable
group by a
)
group by BB,CC