帮我优化这个mysql存储过程 以提高查询速度为主要目标
CREATE DEFINER=`tongfun`@`%` PROCEDURE `销售账龄分析表`(IN fhth VARCHAR(100), IN fdate date, in zd VARCHAR(100))
begin
delete from temp_zlfxb ;
delete from temp_zlfxb1 ;
delete from zlfxb_ht ;
#0519调整账龄分析表，增加更多明细信息
#累计数据
insert into zlfxb_ht (
SELECT id from b_salesdeliver_sales_contract b
where
code like concat('%',fhth,'%')
and contract_type <> 9404
and b.is_deleted=0
and FIND_IN_SET(produce_station,zd)
and id not in (SELECT contract_id from b_salesdeliver_contract_settle_accounts where status=9333 and is_closeout=1)
)
;
insert into temp_zlfxb1(htid,FHTH,fkh,fgcxm,FJZQJXL,FJZQJJE,FJZQJSK,FZJQK,fdate_zhjz,fdate_zhhk,yqwsk)
#yqwsk：逾期时间后的回款金额，逾期录入时，已经减去对应收款作为逾期金额
select
a.id ,
a.code,
a1.name,
a2.name,
ifnull(b.销售量, 0) + ifnull(c.累计销售量, 0) + ifnull(d.累计销售量, 0) + ifnull(f.tzfl, 0),# 累计发货方量
ifnull(b.销售额, 0) + ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje,#截止期间金额
ifnull(b.`已收款`, 0) + IFNULL(e.skje, 0),#累计收款金额
(ifnull(b.销售额, 0) + ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0))- (ifnull(b.`已收款`, 0) + IFNULL(e.skje, 0)), #
c.zhjzrq,
e.zhhkrq,
IFNULL(e.skje, 0)
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取期初数据
SELECT contract_code,
IFNULL(sales_amount, 0) 销售量,#
IFNULL(sales_money, 0) 销售额,#
IFNULL(receivables_money, 0) 应收款,#
IFNULL(received_money, 0) 已收款,#
IFNULL(invoiced_money, 0) 已开票, #
IFNULL(invoiced_money, 0) 已结算金额 #
FROM b_delivery_config a
inner join zlfxb_ht zl on a.contract_id=zl.id
where is_deleted = 0) b on a.code = b.contract_code
left join (
#获取小票数据
SELECT a.contract_code,
max(issue_date) zhjzrq,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
inner join zlfxb_ht zl on a.contract_id=zl.id
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取收款数据
SELECT a.contract_code,
max(b.receipt_time) zhhkrq,
(sum(ifnull(receipt_price, 0)) - sum(ifnull(refund_price, 0))) skje
from b_salesdeliver_contract_receipt_refund_info a
INNER JOIN b_salesdeliver_contract_receipt_refund b on a.pid = b.id
inner join zlfxb_ht zl on a.contract_id=zl.id
where b.status = 9333
and b.is_deleted = 0
and a.is_deleted = 0
AND B.receipt_time BETWEEN '2000-01-01' and fdate
group by contract_code) e on a.code = e.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id
INNER JOIN b_basic_customer a1 on a.settlement_company_id=a1.id
INNER JOIN b_basic_project a2 on a.engineering_project_id=a2.id
;
#获取期间数据
insert into temp_zlfxb(htid,famount1,famount2,famount3,famount4,famount5,famount6,famount7
,famount8,famount9,famount10,famount11
)
select con.id,sum(h.ljxsje),sum(j.ljxsje),sum(k.ljxsje),sum(l.ljxsje),sum(m.ljxsje),sum(n.ljxsje),suM(o.ljxsje)
,suM(p.ljxsje),suM(q.ljxsje),suM(r.ljxsje),suM(s.ljxsje)
from
b_salesdeliver_sales_contract con
inner join zlfxb_ht zl on con.id=zl.id left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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 TIMESTAMPDIFF(DAY,a.issue_date,DATE_FORMAT(fdate, '%Y-%m-%d %H:%i:%S'))<=30
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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and TIMESTAMPDIFF(DAY,time,DATE_FORMAT(fdate, '%Y-%m-%d %H:%i:%S'))<=30
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and TIMESTAMPDIFF(DAY,a.pour_time,DATE_FORMAT(fdate, '%Y-%m-%d %H:%i:%S'))<=30
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id )
h on con.id=h.id left join
(
select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) between 31 and 60
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and datediff(fdate,a.pour_time) between 31 and 60
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id
) j on con.id=j.id
left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) between 61 and 90
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
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) f on a.id = f.contract_id)
k on con.id=k.id
left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) between 91 and 120
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and datediff(fdate,a.pour_time) between 91 and 120
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id)
l on con.id=l.id
left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) between 121 and 180
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and datediff(fdate,a.pour_time) between 121 and 180
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id)
m on con.id=m.id
left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) between 181 and 365
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
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 181 and 365
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id )
n on con.id=n.id
left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) between 366 and 730
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and datediff(fdate,a.pour_time) between 366 and 730
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id )
o on con.id=o.id
left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) between 731 and 1095
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and datediff(fdate,a.pour_time) between 731 and 1095
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id )
p on con.id=p.id
left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) between 1096 and 1460
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and datediff(fdate,a.pour_time) between 1096 and 1460
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id )
q on con.id=q.id
left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) between 1461 and 1825
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and datediff(fdate,a.pour_time) between 1461 and 1825
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id )
r on con.id=r.id
left join
(select a.id,ifnull(c.累计销售额, 0) + ifnull(d.累计销售额, 0) + ifnull(f.tzje, 0) ljxsje
from b_salesdeliver_sales_contract a
inner join zlfxb_ht zl on a.id=zl.id
left join (
#获取小票数据
SELECT a.contract_code,
sum(ifnull(a.sign_for_amount, 0)) 累计销售量,
sum(ifnull(a.tip_total_price, 0)) 累计销售额
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,ifnull(a.issue_date,0)) > 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) c on a.code = c.contract_code
left join (
#获取结算数据
SELECT a.contract_code,
sum(ifnull(b.amount_subtotal, 0)) 累计销售量,
sum(ifnull(b.total_money, 0)) 累计销售额
FROM b_salesdeliver_concrete_settlement_bill a
inner join zlfxb_ht zl on a.contract_id=zl.id
INNER JOIN b_salesdeliver_concrete_settlement_bill_info b ON a.id = b.pid
AND b.is_deleted = 0
AND date(b.time) BETWEEN '2000-01-01' and fdate
and datediff(fdate,b.time) > 1825
WHERE a.is_deleted = 0
GROUP BY a.contract_code) d on a.code = d.contract_code
left join (
#获取调整数据
select a.contract_id, sum(adjustment_money) tzje, sum(adjust_amount) tzfl
from b_salesdeliver_schedule_money_alteration a
inner join zlfxb_ht zl on a.contract_id=zl.id
where a.is_deleted = 0
and a.pour_time BETWEEN '2000-01-01' and fdate
and datediff(fdate,a.pour_time) > 1825
and a.settlement_id is null
group by a.contract_id) f on a.id = f.contract_id )
s on con.id=s.id
group by con.id;
-- #期初账龄
-- update temp_zlfxb a
-- INNER JOIN (select a.contract_id,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') <= 30 then ifnull(a.overdue_money,0) else 0 end famount1,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') between 31 and 60 then ifnull(a.overdue_money,0) else 0 end famount2,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') between 61 and 90 then ifnull(a.overdue_money,0) else 0 end famount3,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') between 91 and 120 then ifnull(a.overdue_money,0) else 0 end famount4,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') between 121 and 180 then ifnull(a.overdue_money,0) else 0 end famount5,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') between 181 and 365 then ifnull(a.overdue_money,0) else 0 end famount6,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') between 366 and 730 then ifnull(a.overdue_money,0) else 0 end famount7,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') between 731 and 1095 then ifnull(a.overdue_money,0) else 0 end famount8,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') between 1096 and 1460 then ifnull(a.overdue_money,0) else 0 end famount9,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') between 1461 and 1825 then ifnull(a.overdue_money,0) else 0 end famount10,
-- case when ifnull(a.overdue_days,0)+datediff(fdate,'2024-09-28') > 1825 then ifnull(a.overdue_money,0) else 0 end famount11
-- from b_delivery_config a where a.is_deleted=0) b on a.htid=b.contract_id
-- set a.famount1=ifnull(a.famount1,0)+ifnull(b.famount1,0),
-- a.famount2=ifnull(a.famount2,0)+ifnull(b.famount2,0),
-- a.famount3=ifnull(a.famount3,0)+ifnull(b.famount3,0),
-- a.famount4=ifnull(a.famount4,0)+ifnull(b.famount4,0),
-- a.famount5=ifnull(a.famount5,0)+ifnull(b.famount5,0),
-- a.famount6=ifnull(a.famount6,0)+ifnull(b.famount6,0),
-- a.famount7=ifnull(a.famount7,0)+ifnull(b.famount7,0),
-- a.famount8=ifnull(a.famount8,0)+ifnull(b.famount8,0),
-- a.famount9=ifnull(a.famount9,0)+ifnull(b.famount9,0),
-- a.famount10=ifnull(a.famount10,0)+ifnull(b.famount10,0),
-- a.famount11=ifnull(a.famount11,0)+ifnull(b.famount11,0);
update temp_zlfxb1 a left join temp_zlfxb b on a.htid=b.htid
set a.famount1=b.famount1,
a.famount2=b.famount2,
a.famount3=b.famount3,
a.famount4=b.famount4,
a.famount5=b.famount5,
a.famount6=b.famount6,
a.famount7=b.famount7,
a.famount8=b.famount8,
a.famount9=b.famount9,
a.famount10=b.famount10,
a.famount11=b.famount11
where a.htid=b.htid ;
# 取回款信息
update temp_zlfxb1 a inner join (select b.contract_code,max(a.receipt_time) receipt_time,sum(ifnull(receipt_price,0))-sum(ifnull(b.refund_price,0))
from b_salesdeliver_contract_receipt_refund a
inner join b_salesdeliver_contract_receipt_refund_info b on a.id = b.pid
where a.is_deleted=0 and b.is_deleted=0 and a.receipt_time<=fdate and b.contract_code like concat('%',fhth,'%')
and a.status=9333 group by b.contract_code) b on a.FHTH=b.contract_code
set a.fdate_zhhk=b.receipt_time
where a.FHTH=b.contract_code;
#取最后一次浇注日
update temp_zlfxb1 a inner join (select contract_code,max(issue_date) issue_date
from b_salesdeliver_electronic_tip where is_deleted=0 group by contract_code) b on a.FHTH=b.contract_code
set a.fdate_zhjz=b.issue_date
where a.FHTH=b.contract_code;
#更新合同状态信息
update temp_zlfxb1 a inner join (select code,case when is_closed=1 then '是' when is_closed=0 then '否' else '否' end is_closed
from b_salesdeliver_sales_contract where is_deleted=0 and sign_time>='2018-1-1' group by code,is_closed ) b on a.fhth=b.code
set a.fsfgb=b.is_closed where a.FHTH=b.code;
#更新账龄
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0),A.famount99 = ifnull(A.yqwsk,0)- ifnull(A.famount11,0),
a.famount11= CASE WHEN ifnull(A.yqwsk,0)- ifnull(A.famount11,0)>0 THEN 0 ELSE ifnull(A.famount11,0)-ifnull(A.yqwsk,0) END;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0),A.famount99= ifnull(A.famount99,0)- ifnull(A.famount10,0),
a.famount10= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount10,0)>0 THEN 0 ELSE ifnull(A.famount10,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0) ,A.famount99= ifnull(A.famount99,0)- ifnull(A.famount9,0),
a.famount9= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount9,0)>0 THEN 0 ELSE ifnull(A.famount9,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0) , A.famount99= ifnull(A.famount99,0)- ifnull(A.famount8,0),
a.famount8= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount8,0)>0 THEN 0 ELSE ifnull(A.famount8,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0) , A.famount99= ifnull(A.famount99,0)- ifnull(A.famount7,0),
a.famount7= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount7,0)>0 THEN 0 ELSE ifnull(A.famount7,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0),A.famount99= ifnull(A.famount99,0)- ifnull(A.famount6,0),
a.famount6= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount6,0)>0 THEN 0 ELSE ifnull(A.famount6,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0),A.famount99= ifnull(A.famount99,0)- ifnull(A.famount5,0),
a.famount5= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount5,0)>0 THEN 0 ELSE ifnull(A.famount5,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0),A.famount99= ifnull(A.famount99,0)- ifnull(A.famount4,0),
a.famount4= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount4,0)>0 THEN 0 ELSE ifnull(A.famount4,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0),A.famount99= ifnull(A.famount99,0)- ifnull(A.famount3,0),
a.famount3= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount3,0)>0 THEN 0 ELSE ifnull(A.famount3,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0),A.famount99= ifnull(A.famount99,0)- ifnull(A.famount2,0),
a.famount2= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount2,0)>0 THEN 0 ELSE ifnull(A.famount2,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
UPDATE temp_zlfxb1 a SET a.famount88=ifnull(A.famount99,0),A.famount99= ifnull(A.famount99,0)- ifnull(A.famount1,0),
a.famount1= CASE WHEN ifnull(A.famount88,0)- ifnull(A.famount1,0)>0 THEN 0 ELSE ifnull(A.famount1,0)-ifnull(A.famount88,0) END
WHERE ifnull(A.famount99,0)>0;
SELECT A.FHTH 合同号,A.FKH 客户,a.fgcxm 工程项目,A.FJZQJXL 截止期间销量,A.FJZQJJE 截止期间金额,
a.FJZQJSK 截止期间回款, a.fzjqk 总计欠款,
a.famount1 AS '1-30天',
a.famount2 AS '31-60天',
a.famount3 AS '61-90天',
a.famount4 AS '91-120天',
a.famount5 AS '121-180天',
a.famount6 AS '181-365天',
a.famount7 AS '1-2年',
a.famount8 AS '2-3年',
a.famount9 AS '3-4年',
a.famount10 AS '4-5年',
a.famount11 AS '5年以上',
A.fsfgb 是否关闭,a.fdate_zhjz 最后一次浇筑日期,a.fdate_zhhk 最后一次回款日期
FROM temp_zlfxb1 A
where FZJQK>0
#where a.FHTH like concat('%',fhth,'%')
;
end
SQL查询技巧：利用CTE递归实现连续日期表
本文介绍如何通过SQL Server 2005之后引入的CTE（公共表表达式）来生成一个只包含连续日期的单列表。详细解释了CTE递归调用的原理和应用，适用于数据分析师和数据库管理员。
扫一扫
473
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
