问题描述
我有桌子：
客户：cid（PK），custid，公司，产品，productdate，total，note
paymentData ：pid（PK），pcid（FK），custid，cleint，total，receiveamt，restamt
Paymet：id（PK），pid（FK），custid，client，product，receiveamt，restamt
> custid在所有表中相同
>产品可以在paymentdata中付款，付款
1）我想要客户的所有数据
2）我想要数据
custid，company，product，productdate，total，notem，reciveamt，restamt
- 我试试
SELECT CM.custclientid，CM.company，CM.product，CM.producttakendate，CM.total，CM.notem
，PM.reciveamt，PM.restamt
FROM
customermaintenance AS CM
LEFT JOIN paymentdata AS PD ON PD.pcid = CM.cid
INNER JOIN付款AS PM ON PD.pid = PM.pid
- 但它没有从客户那里得到所有数据
- ????
i have table:
customer:cid(PK),custid,company,product,productdate,total,note
paymentData:pid(PK),pcid(FK),custid,cleint,total,receiveamt,restamt
Paymet:id(PK),pid(FK),custid,client,product,receiveamt,restamt
>custid same in all table
>product can be diffrent in paymentdata,payment
1)i want all data of customer
2)i want data
custid,company,product,productdate,total,notem,reciveamt,restamt
--i try
SELECT CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.total,CM.notem
,PM.reciveamt,PM.restamt
FROM
customermaintenance AS CM
LEFT JOIN paymentdata AS PD ON PD.pcid=CM.cid
INNER JOIN payment AS PM ON PD.pid=PM.pid
--but it not get all data from customer
--????
推荐答案
SELECT CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.total,CM.notem
,PM.reciveamt,PM.restamt
FROM
payment PM, customermaintenance CM
WHERE PM.pclientid=CM.custclientid
SELECT C.custid,C.company,C.product,C.producttakendate,C.total,C.note,PM.reciveamt,PM.restamt
FROM payment AS PM
INNER JOIN (SELECT C.custid,MAX(Payment_Id) MAX_PAYMENT_ID
FROM customer AS C
INNER JOIN payment AS PM ON PM.pclientid=C.custid
) T ON PM.MAX_PAYMENT_ID = T.MAX_PAYMENT_ID AND PM.pclientid = T.custid
你提到过的一部分作为客户，但后来您引用了另一个表CustomerMaintenance？
A part from that you have mentioned initially as Customer but later you have referenced a different table CustomerMaintenance?
这篇关于如何从三个表中选择数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！