遇到问题说是要多个表生成一个VIEW, 那么应该用到连接,查了一个关于多个表连接的方法.如下:
CREATE VIEW VITEM AS ITEM.ID,CARGO.ID,CHARGE.ID,CUSTOMER.ID,ITEMUSERS.ITEMID
FROM ITEM
LEFT JOIN CARGO
ON ITEM.ID=CARGO.ID
LEFT JOIN CHARGE
ON ITEM.ID=CHARGE.ID
LEFT JOIN CUSTOMER
ON ITEM.ID=CUSTOMER.ID
LEFT JOIN ITEMUSERS
ON ITEM.ID=ITEMUSERS.ITEMID
这里只是测试了一个,可以了,然后就开始用视图,但是用视图不能有重的字段名,所以有些重复字段可以用 as ' XXXX ' 的方式从新命名,如:
CREATE VIEW VITEM AS
SELECT ITEM.ID,CARGO.ID AS 'CARGO.ID'
FROM ITEM
LEFT JOIN CARGO
ON ITEM.ID=CARGO.ID
LEFT JOIN CHARGE
ON ITEM.ID=CHARGE.ID
最后应用的代码相当麻烦,也不知道这样会对数据库有多大的负担.呵呵.
CREATE VIEW VITEM AS
select item.id,item.gid,item.pnum,item.partnerid,item.stime,item.splace,item.rtime,item.rplace,item.cplace,item.ctime,item.pretime,item.type,item.transtype,item.tcost,item.state,item.signer,item.getterid,item.senderid,item.memo,item.sunitid,item.runitid,item.cunitid,item.getplace,item.getphone,item.balancetype,item.receiver,item.gettype,item.typetime,
cargo.name,cargo.packagetype,cargo.amount,cargo.weight,cargo.volume,cargo.price,cargo.nametype,'cargo.memo' AS 'cargo.memo',cargo.state as 'cargo.state',cargo.chargetype,
charge.time,charge.jfzl,charge.ysf,charge.dff,charge.czf,charge.psf,charge.zyf,charge.bzf,charge.bxf,charge.snyf,charge.dshkf,charge.qtfy,charge.memo as 'charge.memo',charge.isPaid,charge.gettype as 'charge.gettype',charge.zhongpsf,
customer.sname,customer.scompany,customer.sphone,customer.saddress,customer.sfax,customer.szipcode,customer.sstate,customer.memo as 'customer.memo',customer.rcertificate,customer.rname,customer.rcompany,customer.rphone,customer.raddress,customer.rfax,customer.rzipcode,
itemusers.typeuserid,itemusers.checkuserid,itemusers.zhonguserid,itemusers.finaluserid
FROM ITEM
LEFT JOIN CARGO
ON ITEM.ID=CARGO.ID
LEFT JOIN CHARGE
ON ITEM.ID=CHARGE.ID
LEFT JOIN CUSTOMER
ON ITEM.ID=CUSTOMER.ID
LEFT JOIN ITEMUSERS
ON ITEM.ID=ITEMUSERS.ITEMID