一对多的查询
Contract 表
字段:[ContractID],[ID],[ContractName],[Signer],[SignTime],[EndTime]
AccountPay表:
字段:ContractID,PaidMoney,PayDate
因为一个Contract有多次付款即AccountPay有多条记录
用left join 显示后的结果是:
[ContractID],[ContractName],[Signer],[SignTime],[EndTime],PaidMoney,PayDate
合同1 合同名称1 签名1 时间1 结束 1000 2006-6
合同1 合同名称1 签名1 时间1 结束 2000 2006-7
有没有办法显示成这样:
[ContractID],[ContractName],[Signer],[SignTime],[EndTime],PaidMoney,PayDate
合同1 合同名称1 签名1 时间1 结束 1000 2006-6
2000 2006-7
即相同的合同只显示一个
[解决办法]
Select
(Case When B.PayDate != C.PayDate Then Null Else A.ContractID End) As ContractID,
(Case When B.PayDate != C.PayDate Then Null Else A.ContractName End) As ContractName,
(Case When B.PayDate != C.PayDate Then Null Else A.Signer End) As Signer,
(Case When B.PayDate != C.PayDate Then Null Else A.SignTime End) As SignTime,
(Case When B.PayDate != C.PayDate Then Null Else A.EndTime End) As EndTime,
B.PaidMoney,
B.PayDate
From
Contract A
Left Join
AccountPay B
On A.ContractID = B.ContractID
Left Join
(Select ContractID, Min(PayDate) As PayDate From AccountPay Group By ContractID) C
On A.ContractID = C.ContractID
