调试之下的最终代码select a.*,b.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 得到的数据是:我left join 的作用主要是获取 postdate 这个字段,但是我发现我用这left join 之后,本来一行数据的,也就是同一客户左表只有一行数据,右表多行数据,我用left 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 GROUP BY a.CustomerFirstName , a.total , a.Collected , a.blance
楼主理解错了。 无论 A [JOIN / LEFT JOIN / RIGHT JOIN] B,只要两边有匹配的记录,结果都是出乘积的记录数。 只有一边无匹配的情况,结果才有不同。
如果两表数据不同,那inner join就会过滤掉不能join的数据,left join不能连接会用null填充right join同理
得到的数据是:我left join 的作用主要是获取 postdate 这个字段,但是我发现我用这left join 之后,本来一行数据的,也就是同一客户左表只有一行数据,右表多行数据,我用left 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
GROUP BY a.CustomerFirstName ,
a.total ,
a.Collected ,
a.blance
无论 A [JOIN / LEFT JOIN / RIGHT JOIN] B,只要两边有匹配的记录,结果都是出乘积的记录数。
只有一边无匹配的情况,结果才有不同。