A1095(30)

本题要注意查询有几辆车的时候如果采取遍历的方式容易产生超时的现象,所以我们要采取第二次排序,剩下的就是根据题意来做就可以了,注意字符串数组比较strcmp(a,b)==0说明两者相同,采用map的话,map的访问是map<string,int> :: iterator it,这个it就相当于我们说的int i中的i了。这样就可以访问啦
以下是代码部分

#include<cstdio>
#include<iostream>
#include<algorithm>
#include<string.h>
#include<vector>
#include<map>
using namespace std;
const int maxn=10010;
struct node
{
	char id[10];
	int status;
	int time;
}a[maxn],valid[maxn];
bool cmp(node b,node c)
{
	if (strcmp(b.id,c.id)!=0)return strcmp(b.id,c.id)<0;
	else return b.time<c.time;
}
bool cmp2(node b, node c)
{
	return b.time<c.time;
}
int main()
{
	int n,k,maxtime=0;
	cin>>n>>k;
	for (int i=0;i<n;i++)
	{
		int hh,mm,ss;
		char st[10];
		scanf("%s %d:%d:%d %s",a[i].id,&hh,&mm,&ss,st);
		if (strcmp(st,"in")==0)
		a[i].status=1;
		else a[i].status=0;
		a[i].time=hh*3600+mm*60+ss;
	}
	sort(a,a+n,cmp);
	int num=0;
	map<string,int> totaltime;
	for (int i=1;i<n;i++)
	{
		if(strcmp(a[i].id,a[i-1].id)==0&&a[i].status==0&&a[i-1].status==1)
		{
			totaltime[a[i].id]+=(a[i].time-a[i-1].time);
			valid[num++]=a[i-1];
			valid[num++]=a[i];	
			if(maxtime<totaltime[a[i].id])
			{
				maxtime=totaltime[a[i].id];
			}
		}
	}
	  /* for (int j=1;j<num;j+=2)
		{
			printf("%s %d   ",valid[j].id,valid[j].time-valid[j-1].time);
		}*/
		sort(valid,valid+num,cmp2);
	for (int i=0;i<k;i++)
	{
		int hh,mm,ss,time,k=0;
		scanf("%d:%d:%d",&hh,&mm,&ss);
		time=hh*3600+mm*60+ss;
		for (int j=0;j<num&&valid[j].time<=time;j++)
		{
			if(valid[j].status==1)
			k++;
			else k--;
			
		}
		printf("%d\n",k);
	}
	map<string,int>::iterator it;
     for (it=totaltime.begin();it!=totaltime.end();it++)
     {
     	if (it->second==maxtime)
     	{
     		printf("%s ",it->first.c_str());
		}
	 }
	 printf("%02d:%02d:%02d\n",maxtime/3600,maxtime%3600/60,maxtime%60);
}

中间一行是刚开始我的num忘记自加一,所以出现了错误,之后通过输出方式就找到错误啦,做这种大题一定要细心

帮我优化这个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
最新发布
07-19
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值