-- Create table
create table ODC_OPERATION_RECORD
(
ID NUMBER,
OPERATOR VARCHAR2(100),
OPERATION_DATE DATE,
OPERATION_NAME VARCHAR2(100),
OPERATION_NUMBER INTEGER,
REMARK VARCHAR2(200)
)
tablespace ODC_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column ODC_OPERATION_RECORD.ID
is '唯一标识id';
comment on column ODC_OPERATION_RECORD.OPERATOR
is '操作员';
comment on column ODC_OPERATION_RECORD.OPERATION_DATE
is '操作日期';
comment on column ODC_OPERATION_RECORD.OPERATION_NAME
is '操作名称';
comment on column ODC_OPERATION_RECORD.OPERATION_NUMBER
is '操作条数';
comment on column ODC_OPERATION_RECORD.REMARK
is '备注';
SELECT TO_CHAR(RE.OPERATION_DATE, 'YYYY') || '.' ||
TO_CHAR(RE.OPERATION_DATE, 'MM') YEAR_MONTH,
--TO_CHAR(RE.OPERATION_DATE, 'DD') DAY,
RE.OPERATION_NAME,
COUNT(RE.OPERATION_NAME),
SUM(RE.OPERATION_NUMBER)
FROM ODC_OPERATION_RECORD RE
GROUP BY TO_CHAR(RE.OPERATION_DATE, 'YYYY') || '.' ||
TO_CHAR(RE.OPERATION_DATE, 'MM'),
RE.OPERATION_NAME;