需求:将表格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 )
);
如果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.但是按照楼上兄弟的,怎么没有实现啊!求救!