查询所有会员开单耗卡项目数.sql
8.2 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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
-- ============================================
-- 查询所有会员的开单项目数和耗卡项目数
-- ============================================
-- 说明：统计每个会员在指定时间范围内的开单项目总数和耗卡项目总数
-- ============================================
-- 版本1：基础查询（不限制时间范围）
-- ============================================
SELECT
COALESCE(billing.F_MemberId, consume.F_MemberId) as member_id,
COALESCE(billing.billing_project_count, 0) as billing_project_count,
COALESCE(consume.consume_project_count, 0) as consume_project_count,
(COALESCE(billing.billing_project_count, 0) - COALESCE(consume.consume_project_count, 0)) as remaining_project_count
FROM (
-- 开单项目数（按会员分组）
SELECT
px.F_MemberId,
SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
WHERE px.F_IsEffective = 1
AND kd.F_IsEffective = 1
AND px.F_MemberId IS NOT NULL
AND px.F_MemberId != ''
GROUP BY px.F_MemberId
) billing
FULL OUTER JOIN (
-- 耗卡项目数（按会员分组）
SELECT
xhpx.F_MemberId,
SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
FROM lq_xh_pxmx xhpx
INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
WHERE xhpx.F_IsEffective = 1
AND xh.F_IsEffective = 1
AND xhpx.F_MemberId IS NOT NULL
AND xhpx.F_MemberId != ''
GROUP BY xhpx.F_MemberId
) consume ON billing.F_MemberId = consume.F_MemberId
ORDER BY billing_project_count DESC;
-- ============================================
-- 版本2：带时间范围限制（推荐使用）
-- ============================================
-- 使用方法：修改下面的时间范围
SELECT
COALESCE(billing.F_MemberId, consume.F_MemberId) as member_id,
COALESCE(billing.billing_project_count, 0) as billing_project_count,
COALESCE(consume.consume_project_count, 0) as consume_project_count,
(COALESCE(billing.billing_project_count, 0) - COALESCE(consume.consume_project_count, 0)) as remaining_project_count
FROM (
-- 开单项目数（按会员分组）
SELECT
px.F_MemberId,
SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
WHERE px.F_IsEffective = 1
AND kd.F_IsEffective = 1
AND px.F_MemberId IS NOT NULL
AND px.F_MemberId != ''
AND kd.kdrq >= '2025-10-01 00:00:00' -- 开始时间（修改这里）
AND kd.kdrq <= '2025-10-30 23:59:59' -- 结束时间（修改这里）
GROUP BY px.F_MemberId
) billing
FULL OUTER JOIN (
-- 耗卡项目数（按会员分组）
SELECT
xhpx.F_MemberId,
SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
FROM lq_xh_pxmx xhpx
INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
WHERE xhpx.F_IsEffective = 1
AND xh.F_IsEffective = 1
AND xhpx.F_MemberId IS NOT NULL
AND xhpx.F_MemberId != ''
AND xh.hksj >= '2025-10-01 00:00:00' -- 开始时间（修改这里）
AND xh.hksj <= '2025-10-30 23:59:59' -- 结束时间（修改这里）
GROUP BY xhpx.F_MemberId
) consume ON billing.F_MemberId = consume.F_MemberId
ORDER BY billing_project_count DESC;
-- ============================================
-- 版本3：兼容MySQL的写法（MySQL不支持FULL OUTER JOIN）
-- ============================================
SELECT
COALESCE(billing.F_MemberId, consume.F_MemberId) as member_id,
COALESCE(billing.billing_project_count, 0) as billing_project_count,
COALESCE(consume.consume_project_count, 0) as consume_project_count,
(COALESCE(billing.billing_project_count, 0) - COALESCE(consume.consume_project_count, 0)) as remaining_project_count
FROM (
-- 开单项目数（按会员分组）
SELECT
px.F_MemberId,
SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
WHERE px.F_IsEffective = 1
AND kd.F_IsEffective = 1
AND px.F_MemberId IS NOT NULL
AND px.F_MemberId != ''
AND kd.kdrq >= '2025-10-01 00:00:00' -- 开始时间（修改这里）
AND kd.kdrq <= '2025-10-30 23:59:59' -- 结束时间（修改这里）
GROUP BY px.F_MemberId
) billing
LEFT JOIN (
-- 耗卡项目数（按会员分组）
SELECT
xhpx.F_MemberId,
SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
FROM lq_xh_pxmx xhpx
INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
WHERE xhpx.F_IsEffective = 1
AND xh.F_IsEffective = 1
AND xhpx.F_MemberId IS NOT NULL
AND xhpx.F_MemberId != ''
AND xh.hksj >= '2025-10-01 00:00:00' -- 开始时间（修改这里）
AND xh.hksj <= '2025-10-30 23:59:59' -- 结束时间（修改这里）
GROUP BY xhpx.F_MemberId
) consume ON billing.F_MemberId = consume.F_MemberId
UNION
SELECT
consume.F_MemberId as member_id,
0 as billing_project_count,
consume.consume_project_count,
(0 - consume.consume_project_count) as remaining_project_count
FROM (
-- 耗卡项目数（按会员分组）
SELECT
xhpx.F_MemberId,
SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
FROM lq_xh_pxmx xhpx
INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
WHERE xhpx.F_IsEffective = 1
AND xh.F_IsEffective = 1
AND xhpx.F_MemberId IS NOT NULL
AND xhpx.F_MemberId != ''
AND xh.hksj >= '2025-10-01 00:00:00' -- 开始时间（修改这里）
AND xh.hksj <= '2025-10-30 23:59:59' -- 结束时间（修改这里）
GROUP BY xhpx.F_MemberId
) consume
LEFT JOIN (
-- 开单项目数（按会员分组）
SELECT
px.F_MemberId,
SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
WHERE px.F_IsEffective = 1
AND kd.F_IsEffective = 1
AND px.F_MemberId IS NOT NULL
AND px.F_MemberId != ''
AND kd.kdrq >= '2025-10-01 00:00:00' -- 开始时间（修改这里）
AND kd.kdrq <= '2025-10-30 23:59:59' -- 结束时间（修改这里）
GROUP BY px.F_MemberId
) billing ON consume.F_MemberId = billing.F_MemberId
WHERE billing.F_MemberId IS NULL
ORDER BY billing_project_count DESC;
-- ============================================
-- 版本4：最简单版本（推荐，使用UNION ALL + GROUP BY实现FULL OUTER JOIN效果）
-- ============================================
SELECT
member_id,
SUM(billing_project_count) as billing_project_count,
SUM(consume_project_count) as consume_project_count,
SUM(billing_project_count) - SUM(consume_project_count) as remaining_project_count
FROM (
-- 开单项目数
SELECT
px.F_MemberId as member_id,
SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count,
0 as consume_project_count
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
WHERE px.F_IsEffective = 1
AND kd.F_IsEffective = 1
AND px.F_MemberId IS NOT NULL
AND px.F_MemberId != ''
AND kd.kdrq >= '2025-10-01 00:00:00' -- 开始时间（修改这里）
AND kd.kdrq <= '2025-10-30 23:59:59' -- 结束时间（修改这里）
GROUP BY px.F_MemberId
UNION ALL
-- 耗卡项目数
SELECT
xhpx.F_MemberId as member_id,
0 as billing_project_count,
SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
FROM lq_xh_pxmx xhpx
INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
WHERE xhpx.F_IsEffective = 1
AND xh.F_IsEffective = 1
AND xhpx.F_MemberId IS NOT NULL
AND xhpx.F_MemberId != ''
AND xh.hksj >= '2025-10-01 00:00:00' -- 开始时间（修改这里）
AND xh.hksj <= '2025-10-30 23:59:59' -- 结束时间（修改这里）
GROUP BY xhpx.F_MemberId
) all_data
GROUP BY member_id
ORDER BY billing_project_count DESC;