SQL count(*) with having

Question

我在oracle上有这个问题。

SELECT CBG.refs, CBG.cuo, CBG.date, CBG.nber, CG.date, CBG.conso, 
                (SELECT COUNT(*) 
                    FROM MAD.VIN CBV
                    WHERE CBV.CUO = CBG.CUO AND 
                        CBV.NBER = CBG.NBER AND
                        CBV.DATE = CBG.DATE AND
                        CBV.REFS = CBG.REFS 
                        GROUP BY CUO , DATE , NBER , REFS )  AS COUNTS ,
                CBG.CONSO_CONCESS AS CONCESS
                FROM MAD.GEN CBG, MAD.CAR_GEN CG
                WHERE CBG.cuo = CG.cuo AND 
                CBG.CONSO_DATE IS NOT NULL AND
                CBG.date = CG.date AND 
                CBG.nber = CG.nber
                HAVING COUNTS > 0;

当我运行这个sql查询时，它给我一个错误:无效的标识符计数。

只有当count大于给定的参数时，我们才能获得结果？

谢谢。

Answer 1

与在MySQL中不同，在Oracle中，我们不能在HAVING子句中引用别名(别名只能在ORDER BY子句中引用)。一种解决方法是将当前逻辑放入CTE中，然后对其进行过滤。

WITH cte AS (
     SELECT CBG.refs, CBG.cuo, CBG.date AS cbg_date, CBG.nber, CG.date AS cg_date,
            CBG.conso,
            (SELECT COUNT(*) 
             FROM MAD.VIN CBV
             WHERE CBV.CUO = CBG.CUO AND 
                   CBV.NBER = CBG.NBER AND
                   CBV.DATE = CBG.DATE AND
                   CBV.REFS = CBG.REFS 
            GROUP BY CUO, DATE, NBER, REFS) AS COUNTS,
            CBG.CONSO_CONCESS AS CONCESS
    FROM MAD.GEN CBG
    INNER JOIN MAD.CAR_GEN CG
        ON CBG.cuo = CG.cuo AND 
           CBG.date = CG.date AND 
           CBG.nber = CG.nber
    WHERE CBG.CONSO_DATE IS NOT NULL
)

SELECT refs, cuo, cbg_date, nber, cg_date, conso, COUNTS, CONCESS
FROM cte
WHERE COUNTS > 0;