如何根据两年销售额情况给所有店铺打上两年均有售、今年新增、今年流失、两年无动销的标，目前用下面方法无法用时间和shop_id关联别的表，寻求新方法
SELECT
MONTH,
shop_id,
CASE
WHEN gmv_qn > 0 AND gmv_jn > 0
THEN '留存'
WHEN gmv_jn > 0 AND gmv_qn <= 0
THEN '新增'
WHEN gmv_qn = 0 AND gmv_jn = 0
THEN '两年无动销'
WHEN gmv_qn > 0 AND gmv_jn <= 0
THEN '流失'
ELSE '流失'
END AS 今年分层,
CASE
WHEN gmv_qn > 0 AND gmv_qqn > 0
THEN '留存'
WHEN gmv_qn > 0 AND gmv_qqn <= 0
THEN '新增'
WHEN gmv_qn = 0 AND gmv_qqn = 0
THEN '两年无动销'
WHEN gmv_qqn > 0 AND gmv_qn <= 0
THEN '流失'
ELSE '流失'
END AS 去年分层
FROM
(
SELECT
a.MONTH, a.shop_id, SUM(IF(a.YEAR = '2023', gmv_sum, 0)) AS gmv_jn, SUM(IF(a.YEAR = '2022', gmv_sum, 0)) AS gmv_qn, SUM(IF(a.YEAR = '2021', gmv_sum, 0)) AS gmv_qqn
from ...
【以下回答由 GPT 生成】
对于无法正确关联时间和店铺ID到其他表的问题，可以尝试使用JOIN语句来连接相关表格。以下是改进后的查询语句：
SELECT
t.MONTH,
t.shop_id,
CASE
WHEN t.gmv_qn > 0 AND t.gmv_jn > 0
THEN '留存'
WHEN t.gmv_jn > 0 AND t.gmv_qn <= 0
THEN '新增'
WHEN t.gmv_qn = 0 AND t.gmv_jn = 0
THEN '两年无动销'
WHEN t.gmv_qn > 0 AND t.gmv_jn <= 0
THEN '流失'
ELSE '流失'
END AS 今年分层,
CASE
WHEN t.gmv_qn > 0 AND t.gmv_qqn > 0
THEN '留存'
WHEN t.gmv_qn > 0 AND t.gmv_qqn <= 0
THEN '新增'
WHEN t.gmv_qn = 0 AND t.gmv_qqn = 0
THEN '两年无动销'
WHEN t.gmv_qqn > 0 AND t.gmv_qn <= 0
THEN '流失'
ELSE '流失'
END AS 去年分层
FROM
(
SELECT
a.MONTH,
a.shop_id,
SUM(IF(a.YEAR = '2023', gmv_sum, 0)) AS gmv_jn,
SUM(IF(a.YEAR = '2022', gmv_sum, 0)) AS gmv_qn,
SUM(IF(a.YEAR = '2021', gmv_sum, 0)) AS gmv_qqn
FROM
your_table a
LEFT JOIN
other_table b
ON
a.shop_id = b.shop_id
WHERE
a.MONTH = '2023' -- 选择对应需要分类的年份
GROUP BY
a.MONTH,
a.shop_id
) AS t;
在上述查询语句中，使用了LEFT JOIN将两个表格相关联，可以根据具体情况修改your_table和other_table为实际的表名。另外，使用WHERE子句选择对应分类的年份，可以根据具体需求进行修改。
请注意，这只是一个示例查询语句，具体的表格结构和业务逻辑可能会有所不同，需要根据实际情况进行调整。