合拢列数据

合并列数据
问题:
SELECT * FROM (SELECT dPlanDate, sum(CASE WHEN iQuantity>0 THEN iQuantity else 0 END) SQuantity,
sum(CASE WHEN iQuantity>0 THEN iMoney else 0 END) SMoney,
sum(CASE WHEN iQuantity>0 THEN iQuantity*(iPrice-iCost) else 0 END) Profit,
sum(CASE WHEN iQuantity>0 THEN iQuantity*(iPrice-iCost)/iMoney else 0 END) Rate 
FROM OrderDetail od GROUP BY od.dPlanDate) a full outer join
(SELECT pd.dPlanDate, sum(CASE WHEN iQuantity>0 THEN iQuantity else 0 END) PQuantity,
sum(CASE WHEN iQuantity>0 THEN iMoney else 0 END) PMoney 
FROM PurchaseDetail pd GROUP BY pd.dPlanDate) b ON a.dPlanDate=b.dPlanDate

取得数据:

dPlanDateSquantitySmoneyProfitRatedPlanDatePquantityPmoney
NULL NULL NULLNULLNULL2012-6-15 657
2012-6-711 1751758NULL NULL NULL
2012-6-80 000NULL NULL NULL
NULL NULL NULLNULLNULL2012-6-134 91
2012-6-149 2281120.932012-6-145 60

想得到结果:

dPlanDateSquantitySmoneyProfitRatePquantityPmoney
2012-6-1 NULL NULLNULLNULL5 657
2012-6-711 1751758 NULL NULL
2012-6-80 000 NULL NULL
2012-6-13 NULL NULLNULLNULL4 91
2012-6-149 2281120.935 60

就是说合并dPlanDate,有没有比较简单的方法.这个是外连接得到的数据,是不是可以以别的方式连接?


[解决办法]

SQL code

SELECT isnull(a.dPlanDate,b.dPlanDate) as dPlanDate, a.Squantity, a.Smoney, a.Profit Rate, b.Pquantity, b.PmoneyFROM (SELECT dPlanDate, sum(CASE WHEN iQuantity>0 THEN iQuantity else 0 END) SQuantity,sum(CASE WHEN iQuantity>0 THEN iMoney else 0 END) SMoney,sum(CASE WHEN iQuantity>0 THEN iQuantity*(iPrice-iCost) else 0 END) Profit,sum(CASE WHEN iQuantity>0 THEN iQuantity*(iPrice-iCost)/iMoney else 0 END) Rate  FROM OrderDetail od GROUP BY od.dPlanDate) a full outer join(SELECT pd.dPlanDate, sum(CASE WHEN iQuantity>0 THEN iQuantity else 0 END) PQuantity,sum(CASE WHEN iQuantity>0 THEN iMoney else 0 END) PMoney  FROM PurchaseDetail pd GROUP BY pd.dPlanDate) b ON a.dPlanDate=b.dPlanDate
[解决办法]
COALESCE,ISNULL都行