17,377
社区成员
发帖
与我相关
我的任务
分享
select a.data,a.htid,sum(b.m_money) m_money
from tab1 a left join hetMony b on a.data = b.m_data
group by a.data,a.htid
select a.data,a.htid,sum(b.m_money) m_money
from tab1 a left join hetMony a on a.data = b.m_data
group by a.data,a.htid
-- Create table
create table TAB1
(
ID NUMBER not null,
NAME VARCHAR2(20),
DWID NUMBER,
DATA DATE,
HTID NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table TAB1
is '学员表';
-- Add comments to the columns
comment on column TAB1.ID
is 'id';
comment on column TAB1.NAME
is '姓名';
comment on column TAB1.DWID
is '单位ID';
comment on column TAB1.DATA
is '报名日期';
comment on column TAB1.HTID
is '合同ID';
-- Create/Recreate primary, unique and foreign key constraints
alter table TAB1
add constraint QQ primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table TAB1
add constraint FA foreign key (HTID)
references HET (HET_ID);
-- Create table
create table HET
(
HET_ID NUMBER not null,
NAME VARCHAR2(30)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table HET
is '合同表';
-- Add comments to the columns
comment on column HET.HET_ID
is '合同id';
comment on column HET.NAME
is '合同名称';
-- Create/Recreate primary, unique and foreign key constraints
alter table HET
add constraint AAAS primary key (HET_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create table
create table HETMONY
(
MID NUMBER not null,
M_HTID NUMBER,
M_DATA DATE,
M_MONEY NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table HETMONY
is '学员交费表';
-- Add comments to the columns
comment on column HETMONY.MID
is 'id';
comment on column HETMONY.M_HTID
is '合同表ID';
comment on column HETMONY.M_DATA
is '交费日期';
comment on column HETMONY.M_MONEY
is '交费金额';
-- Create/Recreate primary, unique and foreign key constraints
alter table HETMONY
add constraint DS primary key (MID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table HETMONY
add constraint F2 foreign key (M_HTID)
references HETMONY (MID);