]]>鍝巭錛佸彲鎬曠殑浠婂ぉ錛?/title>http://www.tkk7.com/tonylovexl/archive/2006/11/07/79700.html緇嗛洦娓擱緇嗛洦娓擱Tue, 07 Nov 2006 13:50:00 GMThttp://www.tkk7.com/tonylovexl/archive/2006/11/07/79700.htmlhttp://www.tkk7.com/tonylovexl/comments/79700.htmlhttp://www.tkk7.com/tonylovexl/archive/2006/11/07/79700.html#Feedback0http://www.tkk7.com/tonylovexl/comments/commentRss/79700.htmlhttp://www.tkk7.com/tonylovexl/services/trackbacks/79700.html聽聽聽 浠婂ぉ鍦ㄦ繁鍏ュ涔犱簡鏁版嵁搴撴柟闈㈢殑鐭ヨ瘑銆傛劅瑙夋尯鍚冨姏銆?浠婂ぉ鍙戠幇寰堝鐞嗚鐨勭煡璇嗘帉鎻″緢涓嶇墷鍥猴紝鐪熸槸寰堟儹鎰э紝浠婂悗榪樺緱緇х畫鍦ㄧ悊璁轟笂涓嬪伐澶傚啀璇翠粖澶╂帴瑙︾殑鐭ヨ瘑鍚э紝浠婂ぉ鑰佹繪繁鍏ヨ瑙d簡鍏充簬select鏌ヨ鏂規硶鐨勪嬌鐢紝鍙戠幇榪欎釜涓滆タ瀹炲湪鏄お鐏墊椿浜嗭紒鐩墠鐨勮嚜宸辨嬁鐫涓涓皬欏圭洰涔熸棤娉曟壘鍒頒笅鎵嬬殑鏂瑰悜錛岃繖搴旇灝辨槸鏋佸害緙轟箯緇忛獙鐨勮〃鐜幫紝浠婂悗榪樺緱澶氬緇冧範鎵嶈兘鏈夋樉钁楃殑鎻愰珮銆傚叧浜巗elect錛屾垜鍏堟槸寰堟棤璇紝紜疄鏄潪甯哥殑鐏墊椿錛屽氨鍏朵腑鐨勮繛鎺ヤ竴欏癸紝灝卞憶寰楁垜瑕佹錛岀湅鏉ヨ嚜宸卞湪榪欏嚑澶╄繕寰楀澶氱湅鐩稿叧鐨勮祫鏂欏澶氬仛緇冧範鎵嶈銆傚笇鏈涘湪鐮旂┒鏁版嵁搴撶殑鍚屽織浠篃鍔犳補錛屽鐪嬩功錛屽鐪嬬湅濂界殑浠g爜錛屽鍘葷悊瑙o紝榪欐牱鑷繁鎵嶈兘鏈夋彁楂樸?鏈鍚庯紝鎶婁粖澶╁湪鑰佹誨甫棰嗕笅瀹屾垚鐨勫叧浜庣數瀛愪功搴楁暟鎹簱緋葷粺鐨勪唬鐮佹斁鍦ㄨ繖閲岋紝瀵逛簬鑰佹墜鏉ヨ闈炲父鐨勭畝鍗曪紝鏈涙墍鏈夋柊鎵嬪叡鍕夛紒
錛堣浠g爜娌℃湁娑夊強insert into錛屽叾涓暟鎹彲浠ユ牴鎹疄闄呮儏鍐墊坊鍔狅級
create table customers (customerID int primary key not null, customername varchar(20) not null);聽
create table books (bookid int primary key not null, booktitle varchar(50) not null, unitprice int not null );聽
create table orders (orderid int primary key not null, orderdate date default sysdate not null, customerid int not null, constraint fk_c foreign key (customerid) references customers(customerid) );聽
create table orderitems (OrderItemID int primary key not null, orderid int not null, bookid int not null, quantity int default '1' not null, constraint fk_b foreign key (bookid) references books(bookid), constraint fk_o聽 foreign key (orderid) references orders(orderid));聽
select c.customername as 瀹㈡埛鍚嶇О, sum(b.unitprice * oi.quantity) from customers c left join orders o on c.customerid = o.customerid left join orderitems oi on o.orderid = oi.orderid left join books b on oi.bookid = b.bookid where to_char(o.orderdate,'yyyy') = to_char(sysdate,'yyyy') group by c.customername