oracle统计查询 sql语句应该怎么写

select substrb(create_time,1,4) "年份",
sum(decode(substrb(create_time,6,2),'01',commission,0)) "1月",
sum(decode(substrb(create_time,6,2),'02',commission,0)) "2月",
sum(decode(substrb(create_time,6,2),'03',commission,0)) "3月",
sum(decode(substrb(create_time,6,2),'04',commission,0)) "4月",
sum(decode(substrb(create_time,6,2),'05',commission,0)) "5月",
sum(decode(substrb(create_time,6,2),'06',commission,0)) "6月",
sum(decode(substrb(create_time,6,2),'07',commission,0)) "7月",
sum(decode(substrb(create_time,6,2),'08',commission,0)) "8月",
sum(decode(substrb(create_time,6,2),'09',commission,0)) "9月",
sum(decode(substrb(create_time,6,2),'10',commission,0)) "10月",
sum(decode(substrb(create_time,6,2),'11',commission,0)) "11月",
sum(decode(substrb(create_time,6,2),'12',commission,0)) "12月"
from test
group by substrb(create_time,1,4)
此语句是按create_time字段是字符型给出的，如果你的表中此字段是日期型，则进行一下转化

SELECT CREATETION AS "年份"
,SUM(DATE_ROW01) AS "01月"
,SUM(DATE_ROW02) AS "02月"
,SUM(DATE_ROW03) AS "03月"
,SUM(DATE_ROW04) AS "04月"
,SUM(DATE_ROW05) AS "05月"
,SUM(DATE_ROW06) AS "06月"
,SUM(DATE_ROW07) AS "07月"
,SUM(DATE_ROW08) AS "08月"
,SUM(DATE_ROW09) AS "09月"
,SUM(DATE_ROW10) AS "10月"
,SUM(DATE_ROW11) AS "11月"
,SUM(DATE_ROW12) AS "12月"
FROM
(
SELECT CREATETION
,NVL(CASE
WHEN MONTH = '01' THEN SUM(COMMISSION) END,0) AS DATE_ROW01
,NVL(CASE
WHEN MONTH = '02' THEN SUM(COMMISSION) END,0) AS DATE_ROW02
,NVL(CASE
WHEN MONTH = '03' THEN SUM(COMMISSION) END,0) AS DATE_ROW03
,NVL(CASE
WHEN MONTH = '04' THEN SUM(COMMISSION) END,0) AS DATE_ROW04
,NVL(CASE
WHEN MONTH = '05' THEN SUM(COMMISSION) END,0) AS DATE_ROW05
,NVL(CASE
WHEN MONTH = '06' THEN SUM(COMMISSION) END,0) AS DATE_ROW06
,NVL(CASE
WHEN MONTH = '07' THEN SUM(COMMISSION) END,0) AS DATE_ROW07
,NVL(CASE
WHEN MONTH = '08' THEN SUM(COMMISSION) END,0) AS DATE_ROW08
,NVL(CASE
WHEN MONTH = '09' THEN SUM(COMMISSION) END,0) AS DATE_ROW09
,NVL(CASE
WHEN MONTH = '10' THEN SUM(COMMISSION) END,0) AS DATE_ROW10
,NVL(CASE
WHEN MONTH = '11' THEN SUM(COMMISSION) END,0) AS DATE_ROW11
,NVL(CASE
WHEN MONTH = '12' THEN SUM(COMMISSION) END,0) AS DATE_ROW12
FROM (
SELECT TO_CHAR(CREATE_TIME,'YYYY') AS CREATETION
,TO_CHAR(CREATE_TIME,'MM') AS MONTH
,COMMISSION,ROW_NUMBER() OVER(PARTITION BY to_char(CREATE_TIME,'YYYY') ORDER BY TO_CHAR(CREATE_TIME,'MM') ) AS RN
FROM TEST_LYH
)
GROUP BY CREATETION,MONTH
)
GROUP BY CREATETION
ORDER BY CREATETION

您的统计查询是指统计函数吗？还是指查询统计信息？

oracle的统计函数有很多，如sum、row_number，一般都是结合group by来用，例如：
SELECT PRODUCT_ID, SUM(SALES) FROM SALES_TABLE GROUP BY PRODUCT_ID;
如果要一次按照多个维度做统计，可以使用over子句，如：
SELECT SUM(SALES) OVER (PARTITION BY PRODUCT_ID) SALES_BY_PRODUCT,
AVG(QUANTITY) OVER (PARTITION BY DEPARTMENT_ID) QUANTITY_BY_DEPARTMENT
FROM SALES_TABLE
另外，在group by里面，还有roll up、cube等子句

如果是查询统计信息，可以参照以下视图：
SELECT VIEW_NAME FROM DBA_VIEWS WHERE VIEW_NAME LIKE '%STAT%';

select
case when t.month like '2011%' then 2011 end,
case when t.month ='201101' then t.prem end,
....
from
(select
to_char(CREATE_TIME,'yyyymm') month,
sum(COMMISSION) prem
group by to_char(CREATE_TIME,'yyyymm'))t
union all
select
case when t.month like '2012%' then 2012 end,
case when t.month ='201201' then t.prem end,
....
from
(select
to_char(CREATE_TIME,'yyyymm') month,
sum(COMMISSION) prem
group by to_char(CREATE_TIME,'yyyymm'))t

oracle查询语句：
select 字段名 from 表名 （where 判断条件）；

oracle的统计函数（count）
select count(字段名),字段1,字段2 from 表名 （where 判断条件） group by 字段1,字段2；