老师，根据公司要求，需要用脚本来提取待出货明细，例如最近半年内的待出货明细，脚本如下：
int test()
{
return 1;
};
int main()
{
int count,i,counts,j;
string sql,v_djlsh,uid,uname,m_dqrq,m_dqsk,m_bz,m_ksrq,m_jsrq,m_ksrqs,m_jsrqs,m_khbh,m_khbhs,m_ksrq,m_khbhss;
uid = get_user_id()
uname = get_user_name()
m_dqrq = fmt_date(get_serv_date(),'yyyy-mm-dd')
m_dqsk = fmt_time(get_serv_time(),'hhmi')
m_bz = "c:\"+m_dqrq+'-'+m_dqsks+"所有待出货明细.xlsx"
m_ksrq = date_add(get_serv_date(),-180)
db_run("select eba_id from eba where service_id in(select val from mup_user_bo where bo_id='eba_service' and user_id='"+uid+"')") //提取操作员绑定的营销区域的所有客户编号
count = db_row_count()
m_khbhs="";
m_khbhss="";
for(i=0;i<count;i++);
{
db_go_row(i)
m_khbh = db_res(0)
if(m_khbhs=="")
{
m_khbhs="'"+m_khbh+"'";
}else{
m_khbhs=m_khbhs+",'"+m_khbh+"'";
}
if(m_khbhss=="")
{
m_khbhss=m_khbh;
}else{
m_khbhss=m_khbhss+","+m_khbh;
}
}
if(m_khbhs == '') //没有绑定营销区域的不做客户编号限定,有绑定的按提取的客户编号组合来限定查询
{
sql = "select d.voucher_no AS 订单单据编号,d.voucher_date AS 单据日期,e.eba_id AS 客户编号,a.vr_item_ext_2 AS 内部订号,T.djlx AS 订单类型,T.gdh AS 工单号,"
sql+= "T.yggdh AS 预告工单号,a.vr_item_ext_1 AS 下单日期,a.item_date_lmt AS 交付日期,a.vr_item_ext_3 AS 客户采购单号,a.vr_item_ext_4 AS 型体,a.vr_item_ext_5 AS 项次,"
sql+= "T.khzl AS 客户指令,T.qhdh AS 企划单号,T.qydh AS 企业单号,T.khwldm AS 客户物料代码,a.res_id AS 内部料号,a.vr_item_ext_6 AS 品名描述,a.sub_attr_val_1 AS 规格,"
sql+= "T.color AS 通用色号,T.nbsh AS 内部色号,T.sfdqry AS 是否打确认样,T.sffhx AS 是否防虹吸,T.dgjp AS 订购胶片,T.zdys AS 织带印刷,a.inp_num AS 订单数量,"
sql+= "ISNULL(Ship.total_ship_num, 0) AS 已出货数量,a.inp_unit_type_id AS 订单单位,(a.inp_num - ISNULL(Ship.total_ship_num, 0)) AS 待出货数量,a.note_info AS 备注,T.khddbz AS 客户订单备注"
sql+= " FROM ebs_vr_item a LEFT JOIN (SELECT b.ref_voucher_id,b.ref_item_id,SUM(b.inp_num) AS total_ship_num FROM ebs_vr_item b INNER JOIN ebs_v c ON c.voucher_id = b.voucher_id"
sql+= " WHERE c.voucher_type = 'BB' AND b.is_main = 'N'AND b.inp_num > 0 GROUP BY b.ref_voucher_id, b.ref_item_id) AS Ship ON a.voucher_id = Ship.ref_voucher_id AND a.item_id = Ship.ref_item_id"
sql+= " LEFT JOIN (SELECT attr_id, CONVERT(NVARCHAR(500), attr_val) AS attr_val, item_id, voucher_id FROM ebs_vr_item_attr) AS S PIVOT (MAX(s.attr_val) FOR S.attr_id IN ([khwldm],[color],[nbsh],"
sql+= " [sfdqry],[dgjp],[zdys],[sffhx],[pinpai],[djlx],[changdu],[yggdh],[ddbz],[khddbz],[gdh],[jijie],[qhdh],[qydh],[khzl])) AS T ON a.voucher_id = T.voucher_id AND a.item_id = T.item_id"
sql+= " LEFT JOIN ebs_v d ON d.voucher_id = a.voucher_id LEFT JOIN ebs_vr e ON e.voucher_id = a.voucher_id WHERE a.inp_num > 0 AND d.voucher_type = 'BA' and d.voucher_date > "+m_ksrq+""
sql+= " AND a.inp_num > ISNULL(Ship.total_ship_num, 0);"
}else{
sql = "select d.voucher_no AS 订单单据编号,d.voucher_date AS 单据日期,e.eba_id AS 客户编号,a.vr_item_ext_2 AS 内部订号,T.djlx AS 订单类型,T.gdh AS 工单号,"
sql+= "T.yggdh AS 预告工单号,a.vr_item_ext_1 AS 下单日期,a.item_date_lmt AS 交付日期,a.vr_item_ext_3 AS 客户采购单号,a.vr_item_ext_4 AS 型体,a.vr_item_ext_5 AS 项次,"
sql+= "T.khzl AS 客户指令,T.qhdh AS 企划单号,T.qydh AS 企业单号,T.khwldm AS 客户物料代码,a.res_id AS 内部料号,a.vr_item_ext_6 AS 品名描述,a.sub_attr_val_1 AS 规格,"
sql+= "T.color AS 通用色号,T.nbsh AS 内部色号,T.sfdqry AS 是否打确认样,T.sffhx AS 是否防虹吸,T.dgjp AS 订购胶片,T.zdys AS 织带印刷,a.inp_num AS 订单数量,"
sql+= "ISNULL(Ship.total_ship_num, 0) AS 已出货数量,a.inp_unit_type_id AS 订单单位,(a.inp_num - ISNULL(Ship.total_ship_num, 0)) AS 待出货数量,a.note_info AS 备注,T.khddbz AS 客户订单备注"
sql+= " FROM ebs_vr_item a LEFT JOIN (SELECT b.ref_voucher_id,b.ref_item_id,SUM(b.inp_num) AS total_ship_num FROM ebs_vr_item b INNER JOIN ebs_v c ON c.voucher_id = b.voucher_id"
sql+= " WHERE c.voucher_type = 'BB' AND b.is_main = 'N'AND b.inp_num > 0 GROUP BY b.ref_voucher_id, b.ref_item_id) AS Ship ON a.voucher_id = Ship.ref_voucher_id AND a.item_id = Ship.ref_item_id"
sql+= " LEFT JOIN (SELECT attr_id, CONVERT(NVARCHAR(500), attr_val) AS attr_val, item_id, voucher_id FROM ebs_vr_item_attr) AS S PIVOT (MAX(s.attr_val) FOR S.attr_id IN ([khwldm],[color],[nbsh],"
sql+= " [sfdqry],[dgjp],[zdys],[sffhx],[pinpai],[djlx],[changdu],[yggdh],[ddbz],[khddbz],[gdh],[jijie],[qhdh],[qydh],[khzl])) AS T ON a.voucher_id = T.voucher_id AND a.item_id = T.item_id"
sql+= " LEFT JOIN ebs_v d ON d.voucher_id = a.voucher_id LEFT JOIN ebs_vr e ON e.voucher_id = a.voucher_id WHERE a.inp_num > 0 AND d.voucher_type = 'BA' and d.voucher_date > "+m_ksrq+""
sql+= " AND a.inp_num > ISNULL(Ship.total_ship_num, 0) and e.eba_id in("+m_khbhs+");"
}
db_run(sql)
// dbg(sql)
counts = db_row_count()
for(j=0;j<counts;j++);
{
db_go_row(j)
db_add_field('单据日期')
db_set_value_ext('单据日期', fmt_date(db_res(1),"yyyy-mm-dd"))
db_add_field('交付日期')
db_set_value_ext('交付日期', fmt_date(db_res(8),"yyyy-mm-dd"))
db_map("res_unit_type","订单单位","订单单位")
}
if(m_khbhss == '')
{
db_show_m('操作员【'+uid+' '+uname+'】于【'+m_dqrq+''+m_dqsk+'】查询所有客户最近半年待出货的订单明细')
}else{
db_show_m('操作员【'+uid+' '+uname+'】于【'+m_dqrq+' '+m_dqsk+'】查询客户编号【'+m_khbhss+'】最近半年待出货的订单明细')
}
return 1;
};
目前测试在脚本文件里面提取最近半年的数据有8922条耗时90秒，在SQL工具里面查询耗时13秒，根据谷歌AI给出的建议，如果能创建下列索引，提取速度会提升10倍左右
sql-- 1. 加快订单主表（按日期和类型）的检索与过滤速度
CREATE NONCLUSTERED INDEX IX_ebs_v_type_date
ON ebs_v (voucher_type, voucher_date)
INCLUDE (voucher_no);
-- 2. 暴击提升“已出货数量”在子查询内部的聚合累加速度（彻底消除对出货表的全表扫描）
CREATE NONCLUSTERED INDEX IX_ebs_vr_item_ref
ON ebs_vr_item (ref_voucher_id, ref_item_id)
INCLUDE (inp_num, is_main, voucher_id);
-- 3. 根治报表中最慢的死角：让自定义属性表（PIVOT数据源）的匹配速度达到内存级
CREATE NONCLUSTERED INDEX IX_ebs_vr_item_attr_fast
ON ebs_vr_item_attr (voucher_id, item_id, attr_id)
INCLUDE (attr_val); |
