从3表中获取数据。

问题描述

我有3张桌子



客户：

cid（PK），custid，company，product，productdate，total，note
pid（PK），pcid（FK），custid，cleint，total，receiveamt，restamt，

PaymentMode，BankName，BranchName，ChequeNo，ReceiveDate，ReceiveAmt，

DueDate，DueAmt



付款方式：

id（PK），pid（FK），custid，product，receiveamt，restamt





1）在Customer表中创建维护

2）他们在paymentdata表中的每个交易商店

paymentdata可以有1个产品的多个记录。

3）最终计算的记录存储在支付每个客户的一次表，

付款每个产品只有1个记录。



i只想记录一次，

 ClientID，公司，产品，ProducttDate，PaymentMode，BankName，BranchName，ChequeNo，ReceiveDate，ReceiveAmt，DueDate，DueAmt，

i尝试......

  SELECT  CM.custclientid，CM.company，CM.product，CM.producttakendate，CM.total，SUM（PM .reciveamt） as  reciveamt，SUM（PM.restamt） as  restamt，
 PD.paymentmode ，PD.bankname，PD.branchname，PD.chequeno，PD.rcvdate，PD.rcvamt，PD.nxtdate，PD.dueamt，PD.productname 
  FROM  
 customermaintenance  AS  CM 
  LEFT   OUTER   JOIN  paymentdata  AS  PD  ON  PD.pcid = CM.cid 
  LEFT   OUTER   JOIN  payment  AS  PM  ON  PD.pid = PM.pid 
  GROUP   BY  CM.custclientid，CM.company，CM.product，CM.producttakendate，CM.total 
，PD.paymentmode，PD.bankname，PD.branchname，PD.chequeno ，PD.rcvdate，PD.rcvamt，PD.nxtdate，PD.dueamt，PD.productname 

但它检索的数据超过一个产品（来自客户之一的paymentdata），我希望你理解。

解决方案

i have 3 table

customer:
cid(PK),custid,company,product,productdate,total,note

paymentData:
pid(PK),pcid(FK),custid,cleint,total,receiveamt,restamt,
PaymentMode,BankName,BranchName,ChequeNo,ReceiveDate,ReceiveAmt,
DueDate,DueAmt

Payment:
id(PK),pid(FK),custid,product,receiveamt,restamt


1)Create Maintenance in Customer table
2)they every transaction store in paymentdata table
paymentdata can have many record of 1 product.
3) finally computed record store in payment table one time of every customers,
payment have only 1 record of each one product.

i want record only one time ,

ClientID,Company,Product,ProducttDate,PaymentMode,BankName,BranchName,ChequeNo,ReceiveDate,ReceiveAmt,DueDate,DueAmt,

i try...

SELECT CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.total ,SUM(PM.reciveamt) as reciveamt,SUM(PM.restamt) as restamt,
PD.paymentmode,PD.bankname,PD.branchname,PD.chequeno,PD.rcvdate,PD.rcvamt,PD.nxtdate,PD.dueamt,PD.productname
FROM
    customermaintenance AS CM
    LEFT OUTER JOIN paymentdata AS PD ON PD.pcid=CM.cid
    LEFT OUTER JOIN payment AS PM ON PD.pid=PM.pid
GROUP BY CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.total
        ,PD.paymentmode,PD.bankname,PD.branchname,PD.chequeno,PD.rcvdate,PD.rcvamt,PD.nxtdate,PD.dueamt,PD.productname

But it retrieve more then one data of product(one from customer one of paymentdata), i hope u understand.

解决方案

这篇关于从3表中获取数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！