需求:将表格OrderLine里相同的LPart的LQuantity相加之后,传到表格Part的Total属性里。
表格如下:
CREATE TABLE Part( /* Part description */
PNumber NUMBER(10) NOT NULL, /* Number */
PName VARCHAR(30) NOT NULL, /* Name */
PManufacturer VARCHAR(255) NOT NULL, /* Manufacturer */
PPrice NUMBER(7,2) NOT NULL, /* Price */
PRating VARCHAR(10) NULL, /* Rating */
Total NUMBER(6) NULL, /*Total number*/
CONSTRAINT Part_pkey PRIMARY KEY( PNumber ),
CONSTRAINT Part_fkey1 FOREIGN KEY( PRating )
);
CREATE TABLE Orders( /* Order description */
ONumber NUMBER(10) NOT NULL, /* Number */
ODate DATE NOT NULL, /* Date when issued */
OCustomer VARCHAR(255) NOT NULL, /* Customer involved */
CONSTRAINT Orders_pkey PRIMARY KEY( ONumber )
);
CREATE TABLE OrderLine( /* Ordered parts */
LOrder NUMBER(10) NOT NULL, /* Order number */
LLine NUMBER(3) NOT NULL, /* Line number */
LPart NUMBER(10) NOT NULL, /* Part number */
LQuantity NUMBER(6) NOT NULL, /* Quantity */
CONSTRAINT OrderLine_pkey PRIMARY KEY( LOrder, LLine ),
CONSTRAINT OrderLine_fkey1 FOREIGN KEY( LPart )
REFERENCES Part( PNumber ),
CONSTRAINT OrderLine_fkey2 FOREIGN KEY( LOrder )
REFERENCES Orders( ONumber )
);
表格如下:
CREATE TABLE Part( /* Part description */
PNumber NUMBER(10) NOT NULL, /* Number */
PName VARCHAR(30) NOT NULL, /* Name */
PManufacturer VARCHAR(255) NOT NULL, /* Manufacturer */
PPrice NUMBER(7,2) NOT NULL, /* Price */
PRating VARCHAR(10) NULL, /* Rating */
Total NUMBER(6) NULL, /*Total number*/
CONSTRAINT Part_pkey PRIMARY KEY( PNumber ),
CONSTRAINT Part_fkey1 FOREIGN KEY( PRating )
);
CREATE TABLE Orders( /* Order description */
ONumber NUMBER(10) NOT NULL, /* Number */
ODate DATE NOT NULL, /* Date when issued */
OCustomer VARCHAR(255) NOT NULL, /* Customer involved */
CONSTRAINT Orders_pkey PRIMARY KEY( ONumber )
);
CREATE TABLE OrderLine( /* Ordered parts */
LOrder NUMBER(10) NOT NULL, /* Order number */
LLine NUMBER(3) NOT NULL, /* Line number */
LPart NUMBER(10) NOT NULL, /* Part number */
LQuantity NUMBER(6) NOT NULL, /* Quantity */
CONSTRAINT OrderLine_pkey PRIMARY KEY( LOrder, LLine ),
CONSTRAINT OrderLine_fkey1 FOREIGN KEY( LPart )
REFERENCES Part( PNumber ),
CONSTRAINT OrderLine_fkey2 FOREIGN KEY( LOrder )
REFERENCES Orders( ONumber )
);
解决方案 »
- 刚学到oracle回滚段这块,问下如何 查看回滚段名称及大小?
- 9i的isqlplusdba权限问题
- 触发器 求助
- 执行execute dbms_stats.gather_schema_stats('username',cascade=>true),对ORACLE有什么作用?执行后在哪里查找到结果?
- 继续请教++++++++++++++
- Oracle BLOB字段(我存的是图片)怎么更新不了啊?
- Oracle managment server 启动问题
- 新手问题:如何实现在JOB中的时间判断?
- 为什么我用刚创建的用户不能登录ORAClE呢?望各位帮忙解答一下
- 请给个toad7.5.2的注册码或7.4的下栽地址啊~~
- 虚拟机上AS3下安装oracle9i的问题
- sql语句,取当前时间前24小时内的数据,oracle
如果part表是空的,新插入数据insert into part(pnumber,total)
select lpart,sum(LQuantity )
from orderline
group by lpart
如果part表已经有数据update part a
set total=(
select lpart,sum(LQuantity )
from orderline
where lpart=a.pnumber
group by lpart
)
update part a
set total=(
select sum(LQuantity )
from orderline
where lpart=a.pnumber
group by lpart
)
ERROR at line 3:
ORA-00913: too many values
因为在OrderLine这表里:
LORDER LLINE LPART LQUANTITY
---------- ---------- ---------- ----------
1 1 3 25
1 2 1 1
1 3 2 11
1 4 3 40
2 1 1 10
2 2 3 606 rows selected.例如LPART=3的数量分别有25,40,60.但是按照楼上兄弟的,怎么没有实现啊!求救!