27,582
社区成员




select CusChange.*,Area.Area_Name,cus.Customer_Code,cus.Customer_Name,Cus.Address CusAddress,cus.Telphone CusTelphone,DicChangeType.FieldValue Change_Type,Rep.Actual_Fee Open_Cus_Price,Repcard.Actual_Fee Card_Price
from CB_CustomerChange CusChange
left join CB_Customer Cus on Cus.ID=CusChange.Customer_ID
left join CB_Area Area on Area.ID=Cus.Area_ID
left join (select * from SYS_Dictionary where FieldName='Change_Type') DicChangeType on DicChangeType.FiledExplain=CusChange.Change_Type
left join (select * from CI_ReplaceCard where Fee_Type='1') Rep on Rep.Customer_ID=Cus.ID
left join (select * from CI_ReplaceCard where Fee_Type='2') Repcard on Repcard.Customer_ID=Cus.ID
where Change_Type='1'
select distinct CusChange.*,Area.Area_Name,cus.Customer_Code,cus.Customer_Name,Cus.Address CusAddress,cus.Telphone CusTelphone,DicChangeType.FieldValue Change_Type,Rep.Actual_Fee Open_Cus_Price,Repcard.Actual_Fee Card_Price
from CB_CustomerChange CusChange
left join CB_Customer Cus on Cus.ID=CusChange.Customer_ID
left join CB_Area Area on Area.ID=Cus.Area_ID
left join (select * from SYS_Dictionary where FieldName='Change_Type') DicChangeType on DicChangeType.FiledExplain=CusChange.Change_Type
left join (select * from CI_ReplaceCard where Fee_Type='1') Rep on Rep.Customer_ID=Cus.ID
left join (select * from CI_ReplaceCard where Fee_Type='2') Repcard on Repcard.Customer_ID=Cus.ID
where Change_Type='1'
cross apply (select top 1 * from CI_ReplaceCard as rc where rc.Fee_Type='2' and rc.Customer_ID=Cus.ID ) as Repcard
---或者是
left join (select Customer_ID,sum(Actual_Fee)/*不清楚你实际应该怎么处理*/ as Actual_Fee from CI_ReplaceCard as rc where rc.Fee_Type='2' group by Customer_ID) as Repcard on Repcard.Customer_ID=Cus.ID
--另外其他Leftjoin ,如
left join (select * from SYS_Dictionary where FieldName='Change_Type') DicChangeType on DicChangeType.FiledExplain=CusChange.Change_Type
--也可以写成
left join SYS_Dictionary as DicChangeType on FieldName='Change_Type' AND DicChangeType.FiledExplain=CusChange.Change_Type