你提到的 `temp_tips_30`、`temp_tips_60`、`temp_tips_90` 等临时表用于存储不同账龄区间的小票、结算、调整数据，以提高查询效率并避免重复子查询。
下面是这些**临时表的完整创建与插入逻辑**，包括 `temp_tips_60`、`temp_tips_90`、`temp_settlement_60`、`temp_adjust_90` 等完整示例。
---
## ✅ 1. 创建 `temp_tips_60`（31-60天小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_60;
CREATE TEMPORARY TABLE temp_tips_60 AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) BETWEEN 31 AND 60
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 2. 创建 `temp_tips_90`（61-90天小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_90;
CREATE TEMPORARY TABLE temp_tips_90 AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) BETWEEN 61 AND 90
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 3. 创建 `temp_tips_120`（91-120天小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_120;
CREATE TEMPORARY TABLE temp_tips_120 AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) BETWEEN 91 AND 120
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 4. 创建 `temp_tips_180`（121-180天小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_180;
CREATE TEMPORARY TABLE temp_tips_180 AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) BETWEEN 121 AND 180
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 5. 创建 `temp_tips_365`（181-365天小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_365;
CREATE TEMPORARY TABLE temp_tips_365 AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) BETWEEN 181 AND 365
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 6. 创建 `temp_tips_730`（1-2年小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_730;
CREATE TEMPORARY TABLE temp_tips_730 AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) BETWEEN 366 AND 730
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 7. 创建 `temp_tips_1095`（2-3年小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_1095;
CREATE TEMPORARY TABLE temp_tips_1095 AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) BETWEEN 731 AND 1095
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 8. 创建 `temp_tips_1460`（3-4年小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_1460;
CREATE TEMPORARY TABLE temp_tips_1460 AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) BETWEEN 1096 AND 1460
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 9. 创建 `temp_tips_1825`（4-5年小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_1825;
CREATE TEMPORARY TABLE temp_tips_1825 AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) BETWEEN 1461 AND 1825
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 10. 创建 `temp_tips_more`（5年以上小票数据）
```sql
DROP TEMPORARY TABLE IF EXISTS temp_tips_more;
CREATE TEMPORARY TABLE temp_tips_more AS
SELECT
a.contract_code,
SUM(IFNULL(a.tip_total_price, 0)) AS 累计销售额
FROM b_salesdeliver_electronic_tip a
INNER JOIN zlfxb_ht zl ON a.contract_id = zl.id
WHERE a.issue_date BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.issue_date) > 1825
AND a.is_accumulate_car_num = 1
AND IFNULL(a.settlement_id, 0) = 0
AND IFNULL(a.settlement_code, '') = ''
AND a.is_deleted = 0
GROUP BY a.contract_code;
```
---
## ✅ 同理可创建结算表和调整表的临时表
### 示例：结算表 `temp_settlement_60`
```sql
DROP TEMPORARY TABLE IF EXISTS temp_settlement_60;
CREATE TEMPORARY TABLE temp_settlement_60 AS
SELECT
a.contract_code,
SUM(IFNULL(b.total_money, 0)) AS 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
WHERE a.is_deleted = 0
AND DATE(b.time) BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, b.time) BETWEEN 31 AND 60
GROUP BY a.contract_code;
```
### 示例：调整表 `temp_adjust_90`
```sql
DROP TEMPORARY TABLE IF EXISTS temp_adjust_90;
CREATE TEMPORARY TABLE temp_adjust_90 AS
SELECT
a.contract_id,
SUM(adjustment_money) AS tzje
FROM b_salesdeliver_schedule_money_alteration a
WHERE a.is_deleted = 0
AND a.pour_time BETWEEN '2000-01-01' AND fdate
AND DATEDIFF(fdate, a.pour_time) BETWEEN 61 AND 90
AND a.settlement_id IS NULL
GROUP BY a.contract_id;
```
---
## ✅ 总结：这些临时表的作用
| 表名 | 区间 | 用途说明 |
|--------------------|-------------|------------------------------|
| `temp_tips_30` | 1-30天 | 小票累计销售额 |
| `temp_tips_60` | 31-60天 | 小票累计销售额 |
| `temp_tips_90` | 61-90天 | 小票累计销售额 |
| `temp_settlement_30` | 1-30天 | 结算累计销售额 |
| `temp_adjust_60` | 31-60天 | 调整金额 |
| ... | ... | ... |
---
###
博客围绕投资安全给出实用技巧，包括选择好目标，把握买卖时机，如缩量不跌是底部、放量不涨是顶部等，还提及社保相关、国运ETF300以及不同季节投资倾向，同时建议持续下跌时分段补仓。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
