select B.ID, MAX(B.Cenben) Cenben, MAX(A.KUAIHAO)KEEP(DENSE_RANK LAST ORDER BY A.S,A.KUAIHAO) kuaihao, MAX(B.Beizhu) Beizhu, SUM(A.S) ProductNum from ( SELECT KUAIHAO,Cenben,COUNT(1) S FROM Product GROUP BY KUAIHAO,Cenben ) A,Cenben B WHERE A.Cenben=B.Cenben GROUP BY B.ID
MAX(B.Cenben) Cenben,
MAX(A.KUAIHAO)KEEP(DENSE_RANK LAST ORDER BY A.S,A.KUAIHAO) kuaihao,
MAX(B.Beizhu) Beizhu,
SUM(A.S) ProductNum
from (
SELECT KUAIHAO,Cenben,COUNT(1) S
FROM Product
GROUP BY KUAIHAO,Cenben
) A,Cenben B
WHERE A.Cenben=B.Cenben
GROUP BY B.ID