1. set @TemQueryStr='SELECT a.CustomerFirstName,isnull(a.Collected,0) as Collected,b.Subtotal,(b.Subtotal+ExpressFee-Discount) AS Total FROM ProductOrder a INNER JOIN
(SELECT OrderId,SUM(Price*Quantity) AS Subtotal FROM ProductOrderItem GROUP BY OrderId) b
ON a.OrderId=b.OrderId where ISNULL(Deleted,0)=0 AND DisposalState <>6 ',
2. set @TemQueryStr=' select CustomerFirstName,sum(Total) as Total,sum(Collected) as Collected,sum(Total-Collected) as blance from ('+@TemQueryStr+') a group by CustomerFirstName '
3. set @QueryStr='select a.*,b.postdate from ('+@TemQueryStr+') as a left join ProductOrder b on a.CustomerFirstName=b.CustomerFirstName '
执行
select CustomerFirstName,Postdate from ProductOrder
得到
而执行上面的第二步得到
执行上面的第三步得到
我想问一下，为什么 left join应有的作用没有效果的，这里感觉应该是right join 的作用的，为什么，求大神指点！
------解决方案--------------------
类似这样，这个跟是否left还是inner join无关
SELECT a.CustomerFirstName ,
a.total ,
a.Collected ,
a.blance ,
MAX(b.postdate) postdate
FROM ( SELECT CustomerFirstName ,
SUM(Total) AS Total ,
SUM(Collected) AS Collected ,
SUM(Total - Collected) AS blance
FROM ( SELECT a.CustomerFirstName ,
ISNULL(a.Collected, 0) AS Collected ,
b.Subtotal ,
( b.Subtotal + ExpressFee - Discount ) AS Total
FROM ProductOrder a
INNER JOIN ( SELECT OrderId ,
SUM(Price * Quantity) AS Subtotal
FROM ProductOrderItem
GROUP BY OrderId
) b ON a.OrderId = b.OrderId
WHERE ISNULL(Deleted, 0) = 0
AND DisposalState <> 6
) a
GROUP BY CustomerFirstName
) AS a
LEFT JOIN ProductOrder b ON a.CustomerFirstName = b.CustomerFirstName