一、问题描述
已知3~5月的进销存数据,现统计各月份的总结存数量最大的前5个品牌,当不指定月份时,显示的是5月末的总结存数量。新建计算字段“总结存数量=if(count(distinct [月份])=1,sum([结存数量]),lod_include{[月份]:SUM([结存数量]):[月份]='5月'})”,即当”月份”个数为1时,直接对“结存数量”求和,当“月份”个数不为1时,只对“5月”的“结存数量“求和。
1)当不指字月份时,显示结果正常,如下图
2)当指定月份为“5月”时,显示结果也正常,如下图
3)当指定月份为”3月”或”4月”时,数据返回为空,显示结果不正确,如下图
二、原因分析
查看当指定月份为”3月”时SQL代码:
SELECT
LOD_TM.`LOD_7D5CED68F83735` AS LOD_0,
IF(
LOD_TM.`temp_calculation_0` = 1,
LOD_TM.`temp_calculation_1`,
LOD_TP_0.`LOD_4C2968B366F62E`
) AS LOD_1
FROM
(
SELECT
A_T_2_.`卷烟品牌` AS LOD_7D5CED68F83735,
SUM(
A_T_1_.`结存数量`
) AS temp_calculation_1,
COUNT(
DISTINCT A_T_1_.`月份`
) AS temp_calculation_0
FROM
`终端经营数据`.`商品月台账` AS A_T_1_
LEFT JOIN `终端经营数据`.`商品管理` AS A_T_2_ ON A_T_1_.`商品条码` = A_T_2_.`商品条码`
WHERE
A_T_2_.`是否卷烟` = '卷烟'
AND A_T_1_.`当月是否在销` = '在销'
AND A_T_1_.`月份` = '3月'
GROUP BY
A_T_2_.`卷烟品牌`
LIMIT
0, 10000
) AS LOD_TM
INNER JOIN (
SELECT
LOD_TL.`LOD_7D5CED68F83735` AS LOD_7D5CED68F83735,
LOD_TR.`LOD_measure_result` AS LOD_4C2968B366F62E
FROM
(
SELECT
A_T_2_.`卷烟品牌` AS LOD_7D5CED68F83735,
A_T_1_.`月份` AS LOD_2C07D23FC7BBAA
FROM
`终端经营数据`.`商品月台账` AS A_T_1_
LEFT JOIN `终端经营数据`.`商品管理` AS A_T_2_ ON A_T_1_.`商品条码` = A_T_2_.`商品条码`
WHERE
A_T_2_.`是否卷烟` = '卷烟'
AND A_T_1_.`当月是否在销` = '在销'
AND A_T_1_.`月份` = '3月'
GROUP BY
A_T_2_.`卷烟品牌`,
A_T_1_.`月份`
) AS LOD_TL
INNER JOIN (
SELECT
A_T_2_.`卷烟品牌` AS LOD_7D5CED68F83735,
SUM(
A_T_1_.`结存数量`
) AS LOD_measure_result,
A_T_1_.`月份` AS LOD_2C07D23FC7BBAA
FROM
`终端经营数据`.`商品月台账` AS A_T_1_
LEFT JOIN `终端经营数据`.`商品管理` AS A_T_2_ ON A_T_1_.`商品条码` = A_T_2_.`商品条码`
WHERE
A_T_2_.`是否卷烟` = '卷烟'
AND A_T_1_.`当月是否在销` = '在销'
AND A_T_1_.`月份` = '3月'
AND A_T_1_.`月份` = '5月'
GROUP BY
A_T_2_.`卷烟品牌`,
A_T_1_.`月份`
) AS LOD_TR ON LOD_TL.`LOD_7D5CED68F83735` = LOD_TR.`LOD_7D5CED68F83735`
AND LOD_TL.`LOD_2C07D23FC7BBAA` = LOD_TR.`LOD_2C07D23FC7BBAA`
GROUP BY
LOD_TL.`LOD_7D5CED68F83735`
) AS LOD_TP_0 ON LOD_TM.`LOD_7D5CED68F83735` = LOD_TP_0.`LOD_7D5CED68F83735`
其中上述代码第19行~55行代码返回的是“count(distinct [月份])=1”即指定月份(“3月”)各品牌结存数量,如下图1:
其中上述代码第61行~141行,返回的是当指定月份为“3月”时,“count(distinct [月份])”不等于1时,各品牌结存数量(返回结果为空),结果如下图2:
然后由上述第59行代码可知图1与图2是用“INNER JOIN”进行的内连接,结果必然为空。考虑到当不指定月份时,图1包含了5月的结存数量,所以此处的”INNER JOIN”应改为左外连接“LEFT JOIN”。经测试,修改后结果显示正常。
三、再思考
此处的INNER JOIN更理想的修改应为“FULL JOIN”全外连接,可惜MySQL不支持“FULL JOIN”。