横向合并查询结果
来源:互联网 发布:app拍照软件 编辑:程序博客网 时间:2024/05/22 04:41
表信息:
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:
1
2
3
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
原始合并方法:
<pre class=
"brush:sql;gutter:false;"
>
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
</pre>
优化的方法:
<pre class=
"brush:sql;gutter:false;"
>
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
</pre>
0 0
- 横向合并查询结果
- oracle 查询结果横向union
- 合并查询数据结果
- ORACLE-合并查询结果
- mysql查询结果合并
- mysql合并查询结果
- mysql查询结果合并
- mysql合并查询结果
- union合并查询结果
- MSSQL将查询结果横向显示
- oracle 将查询结果纵向横向显示。
- SQL如何合并查询结果
- union(联合)合并查询结果
- oracle的查询结果合并
- mysql 合并两个查询结果
- MYSQL之合并查询结果
- sql查询结果的纵向变横向排列
- SQL 查询结果的纵向变横向排列
- 《穹顶之下》的思考:柴静改变不了什么!
- Android性能专项测试之battery-historian试用
- “default”标签跳过“ ”的初始化操作问题解决
- 【图像处理】透视变换 Perspective Transformation
- 【ssh】——Struts2从前端传值到后台的三种方法(从属性到ModelDriven)
- 横向合并查询结果
- javaMail异常 NoClassDefFoundError: com/sun/mail/util/LineInputStream
- 不用第三个变量交换2个变量的值
- select与pselect的信号屏蔽
- Codeforces Round #295 (Div. 1) B. Cubes (STL+类拓扑)
- MFC创建线程(工作线程和界面线程)
- Git客户端使用 —— TortoiseGit
- leetcode Reverse Integer
- AutoCompleteExtender详细介绍(二)