合并列数据
问题:
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

取得数据:

dPlanDate Squantity Smoney Profit Rate dPlanDate Pquantity Pmoney
NULL NULL NULL NULL NULL 2012-6-1 5 657
2012-6-7 11 175 175 8 NULL NULL NULL
2012-6-8 0 0 0 0 NULL NULL NULL
NULL NULL NULL NULL NULL 2012-6-13 4 91
2012-6-14 9 228 112 0.93 2012-6-14 5 60

想得到结果:

dPlanDate Squantity Smoney Profit Rate Pquantity Pmoney
2012-6-1 NULL NULL NULL NULL 5 657
2012-6-7 11 175 175 8 NULL NULL
2012-6-8 0 0 0 0 NULL NULL
2012-6-13 NULL NULL NULL NULL 4 91
2012-6-14 9 228 112 0.93 5 60

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


------解决方案--------------------

SQL code

SELECT isnull(a.dPlanDate,b.dPlanDate) as dPlanDate, a.Squantity, a.Smoney, a.Profit Rate, b.Pquantity, b.Pmoney
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

------解决方案--------------------

COALESCE,ISNULL都行