横向合并两个统计SQL语句。

表信息：

CM_ProduceBillDetail：

PB_ID                PBD_Order            PBD_Color            PBD_XS      PBD_S       PBD_M       PBD_L       PBD_XL      PBD_XXL     
-------------------- -------------------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- 
111                订单号                  白色                   0           10          0           40          0           60
111                订单号                  黑色                   0           0           0           50          0           0
111                订单号1                 白色                   0           0           80          0           0           0

CM_MeterialCard：

MC_ID      PB_ID    BSH_Card    MC_CardID    MC_Color    MC_XS      MC_S        MC_M        MC_L        MC_XL      MC_XXL 
---------------------------------------------------------
没记录

 

原始语句：

SELECT PBD_Color,SUM(PBD_XS) AS T_XS,SUM(PBD_S) AS T_S,SUM(PBD_M) AS T_M,
SUM(PBD_L) AS T_L,SUM(PBD_XL) AS T_XL,SUM(PBD_XXL) AS T_XXL
FROM CM_ProduceBillDetail
WHERE PB_ID = '111'
GROUP BY PBD_Color

SELECT ISNULL(SUM(MC_XS),0),ISNULL(SUM(MC_S),0),ISNULL(SUM(MC_M),0),ISNULL(SUM(MC_L),0),ISNULL(SUM(MC_XL),0),ISNULL(SUM(MC_XXL),0)
FROM CM_MeterialCard WHERE PB_ID = '111' AND BSH_Card = '1'
GROUP BY MC_Color

合并要要得到第一个语句的8列加第二个语句的7列，共15列。

合并条件：CM_ProduceBillDetail.PB_ID = CM_MeterialCard.PB_ID AND CM_ProduceBillDetail.PBD_Color = CM_MeterialCard.MC_Color

原始合并方法：

SELECT PBD_Color,SUM(PBD_XS) AS T_XS,SUM(PBD_S) AS T_S,SUM(PBD_M) AS T_M,
SUM(PBD_L) AS T_L,SUM(PBD_XL) AS T_XL,SUM(PBD_XXL) AS T_XXL,
(SELECT ISNULL(SUM(MC_XS),0) FROM CM_MeterialCard WHERE CM_MeterialCard.PB_ID=PB_ID 
AND PBD_Color=MC_Color AND BSH_Card = '1') AS F_XS,
(SELECT ISNULL(SUM(MC_S),0) FROM CM_MeterialCard WHERE CM_MeterialCard.PB_ID=PB_ID 
AND PBD_Color=MC_Color AND BSH_Card = '1') AS F_S,
(SELECT ISNULL(SUM(MC_M),0) FROM CM_MeterialCard WHERE CM_MeterialCard.PB_ID=PB_ID 
AND PBD_Color=MC_Color AND BSH_Card = '1') AS F_M,
(SELECT ISNULL(SUM(MC_L),0) FROM CM_MeterialCard WHERE CM_MeterialCard.PB_ID=PB_ID 
AND PBD_Color=MC_Color AND BSH_Card = '1') AS F_L,
(SELECT ISNULL(SUM(MC_XL),0) FROM CM_MeterialCard WHERE CM_MeterialCard.PB_ID=PB_ID 
AND PBD_Color=MC_Color AND BSH_Card = '1') AS F_XL,
(SELECT ISNULL(SUM(MC_XXL),0) FROM CM_MeterialCard WHERE CM_MeterialCard.PB_ID=PB_ID 
AND PBD_Color=MC_Color AND BSH_Card = '1') AS F_XXL
FROM CM_ProduceBillDetail
WHERE PB_ID = '111'
GROUP BY PBD_Color

优化的方法：

 
SELECT A.*,ISNULL(B.MC_XS,0) AS F_XS,ISNULL(B.MC_S,0) AS F_S,ISNULL(B.MC_M,0) AS F_M,
ISNULL(B.MC_L,0) AS F_L,ISNULL(B.MC_XL,0) AS F_XL,ISNULL(B.MC_XXL,0) AS F_XXL
FROM 
(
SELECT PBD_Color,SUM(PBD_XS) AS T_XS,SUM(PBD_S) AS T_S,SUM(PBD_M) AS T_M,
SUM(PBD_L) AS T_L,SUM(PBD_XL) AS T_XL,SUM(PBD_XXL) AS T_XXL
FROM CM_ProduceBillDetail
WHERE PB_ID = '111'
GROUP BY PBD_Color
) A 
LEFT JOIN 
(SELECT MC_Color,MC_XS=SUM(MC_XS),MC_S=SUM(MC_S),MC_M=SUM(MC_M),
MC_L=SUM(MC_L),MC_XL=SUM(MC_XL),MC_XXL=SUM(MC_XXL)
FROM CM_MeterialCard WHERE PB_ID = '111' AND BSH_Card = '1'
GROUP BY MC_Color
) B On A.PBD_Color=B.MC_Color