创建开单详细记录视图.sql
3.53 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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
-- 创建开单详细记录视图（简化版）
-- 功能：联合开单记录表、开单品项明细表、项目资料表，呈现开单详细记录
-- 包含：开单编号、开单会员、会员手机号、开单时间、开单品项、项目数量、项目单价、项目总价、来源类型、项目分类3
CREATE VIEW v_order_detail_simple AS
SELECT
kd.F_Id AS order_id, -- 开单编号
kd.kdhy AS order_member, -- 开单会员
kd.kdhysjh AS member_phone, -- 会员手机号
kd.kdrq AS order_time, -- 开单时间
px.px AS item_code, -- 开单品项（项目编号）
px.pxmc AS item_name, -- 品项名称
COALESCE(px.F_ProjectNumber, 1) AS project_count, -- 项目数量（默认为1）
px.pxjg AS project_unit_price, -- 项目单价
(COALESCE(px.F_ProjectNumber, 1) * px.pxjg) AS project_total_price, -- 项目总价（数量*单价）
px.F_SourceType AS source_type, -- 来源类型
xm.fl3 AS project_category3 -- 项目分类3
FROM
lq_kd_kdjlb kd
LEFT JOIN lq_kd_pxmx px ON kd.F_Id = px.glkdbh
LEFT JOIN lq_xmzl xm ON px.px = xm.F_Id
WHERE
kd.F_Id IS NOT NULL
AND px.F_Id IS NOT NULL -- 只显示有品项明细的开单
ORDER BY
kd.kdrq DESC,
kd.F_Id,
px.F_Id;
-- 创建相关索引以优化查询性能
CREATE INDEX IF NOT EXISTS idx_lq_kd_pxmx_glkdbh ON lq_kd_pxmx(glkdbh);
CREATE INDEX IF NOT EXISTS idx_lq_kd_pxmx_px ON lq_kd_pxmx(px);
CREATE INDEX IF NOT EXISTS idx_lq_xmzl_fl3 ON lq_xmzl(fl3);
CREATE INDEX IF NOT EXISTS idx_lq_kd_kdjlb_kdrq ON lq_kd_kdjlb(kdrq);
-- 使用示例查询：
-- 1. 查询所有开单详细记录
-- SELECT * FROM v_order_detail_simple ORDER BY order_time DESC;
-- 2. 查询指定开单编号的详细记录
-- SELECT * FROM v_order_detail_simple WHERE order_id = '具体开单编号';
-- 3. 查询指定会员的开单记录
-- SELECT * FROM v_order_detail_simple WHERE order_member = '具体会员ID' ORDER BY order_time DESC;
-- 4. 查询指定分类3的开单记录
-- SELECT * FROM v_order_detail_simple WHERE project_category3 = '具体分类3名称' ORDER BY order_time DESC;
-- 5. 查询指定来源类型的开单记录
-- SELECT * FROM v_order_detail_simple WHERE source_type = '具体来源类型' ORDER BY order_time DESC;
-- 6. 查询指定日期范围的开单记录
-- SELECT * FROM v_order_detail_simple
-- WHERE order_time >= '2024-01-01' AND order_time <= '2024-12-31'
-- ORDER BY order_time DESC;
-- 7. 按分类3统计开单数量和总金额
-- SELECT
-- project_category3,
-- COUNT(*) AS record_count,
-- SUM(project_total_price) AS total_amount
-- FROM v_order_detail_simple
-- WHERE project_category3 IS NOT NULL
-- GROUP BY project_category3
-- ORDER BY total_amount DESC;
-- 8. 按来源类型统计开单数量和总金额
-- SELECT
-- source_type,
-- COUNT(*) AS record_count,
-- SUM(project_total_price) AS total_amount
-- FROM v_order_detail_simple
-- WHERE source_type IS NOT NULL
-- GROUP BY source_type
-- ORDER BY total_amount DESC;
-- 9. 查询项目总价大于指定金额的记录
-- SELECT * FROM v_order_detail_simple WHERE project_total_price > 1000 ORDER BY project_total_price DESC;
-- 10. 按会员统计开单数量和总金额
-- SELECT
-- order_member,
-- member_phone,
-- COUNT(*) AS order_count,
-- SUM(project_total_price) AS total_amount
-- FROM v_order_detail_simple
-- GROUP BY order_member, member_phone
-- ORDER BY total_amount DESC;