创建金三角月度业绩总和视图.sql
1.85 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- 创建金三角月度业绩总和视图（简化版）
-- 功能：获取每个金三角的每个月开单业绩总和
-- 主要关联：lq_kd_jksyj（健康师业绩表）和 lq_ycsd_jsj（金三角设定表）
CREATE VIEW v_jsj_monthly_summary AS
SELECT
jsj.F_Id AS jsj_id, -- 金三角ID
jsj.jsj AS jsj_name, -- 金三角名称
jsj.yf AS month, -- 月份
jsj.md AS store_id, -- 门店ID
md.dm AS store_name, -- 门店名称
SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS total_performance -- 月度业绩总和
FROM
lq_ycsd_jsj jsj
LEFT JOIN lq_kd_jksyj jksyj ON jsj.F_Id = jksyj.jsj_id
LEFT JOIN lq_mdxx md ON jsj.md = md.F_Id
WHERE
jsj.yf IS NOT NULL
AND jksyj.yjsj IS NOT NULL
AND jksyj.jksyj IS NOT NULL
AND jksyj.jksyj != ''
AND jksyj.jksyj != '0'
GROUP BY
jsj.F_Id,
jsj.jsj,
jsj.yf,
jsj.md,
md.dm
ORDER BY
jsj.yf DESC,
total_performance DESC;
-- 创建相关索引以优化查询性能
CREATE INDEX IF NOT EXISTS idx_lq_kd_jksyj_jsj_id ON lq_kd_jksyj(jsj_id);
CREATE INDEX IF NOT EXISTS idx_lq_kd_jksyj_yjsj ON lq_kd_jksyj(yjsj);
CREATE INDEX IF NOT EXISTS idx_lq_ycsd_jsj_yf ON lq_ycsd_jsj(yf);
-- 使用示例：
-- 1. 查询所有金三角的月度业绩总和
-- SELECT * FROM v_jsj_monthly_summary ORDER BY month DESC, total_performance DESC;
-- 2. 查询指定月份的金三角业绩总和
-- SELECT * FROM v_jsj_monthly_summary WHERE month = '202412' ORDER BY total_performance DESC;
-- 3. 查询指定金三角的业绩历史
-- SELECT * FROM v_jsj_monthly_summary WHERE jsj_id = '具体金三角ID' ORDER BY month DESC;
-- 4. 查询指定门店的金三角业绩
-- SELECT * FROM v_jsj_monthly_summary WHERE store_id = '具体门店ID' ORDER BY month DESC, total_performance DESC;