Oracle常见SQL示例

-- 分组查询统计示例，就仅供参考

SELECT A.*
FROM (
    SELECT
        NVL(q."unit", '未知单位') AS unit,
        TO_CHAR(q."create_time", 'YYYY') AS iYear,
        TO_CHAR(q."create_time", 'MM') AS iMonth,
        q."hidden_level" AS hiddenLevel,
        COUNT(q."id") AS iCount
    FROM
        "hidden" q
    WHERE
        q."create_time" >= ADD_MONTHS(TRUNC(SYSDATE), -12)
        AND q."create_time" <= TRUNC(SYSDATE) + 1 - INTERVAL '1' SECOND
    GROUP BY
        NVL(q."unit", '未知单位'),
        TO_CHAR(q."create_time", 'YYYY'),
        TO_CHAR(q."create_time", 'MM'),
        q."hidden_level"
    ORDER BY
        unit,
        iYear DESC,
        iMonth DESC,
        hiddenLevel
) A
WHERE A.UNIT = 'yy'

 

-- 按照年统计：平均数

SELECT b.XCYEAR as xcYear,
　　ROUND(SUM(b.KLCOUNT) / COUNT(DISTINCT TRUNC(b.XCDATE)), 2) AS klCount 
FROM BIZ_XC_DATA b 
WHERE b.XCYEAR >=:minYear  GROUP BY b.XCYEAR
;

-- 按照年统计：平均数
SELECT b.XCYEAR, 
　　SUM(b.KLCOUNT) AS total_kl, -- 查看总KLCOUNT 
　　COUNT(DISTINCT TRUNC(b.XCDATE)) AS date_count -- 查看有效日期数量 
FROM BIZ_XC_DATA b WHERE b.XCYEAR > 2019 
GROUP BY b.XCYEAR 
ORDER BY b.XCYEAR;