|
0101043509 程序搜索出来7个，实际只有5个， |
|
本帖最后由 李维强-15级 于 2025-3-12 17:27 编辑 修复成品 [SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode
BEGIN
BEGIN TRY
DROP TABLE #TempGWSN;
END TRY
BEGIN CATCH
-- 错误处理逻辑，这里可以不做任何事情，因为表可能不存在
-- 如果需要，可以通过 ERROR_NUMBER() 和 ERROR_MESSAGE() 获取错误信息
END CATCH
CREATE TABLE #TempGWSN (
id INT IDENTITY(1,1) PRIMARY KEY,
gw_sn VARCHAR(255)
);
INSERT INTO #TempGWSN (gw_sn)
VALUES
('4500858055');
-- 动态拼接订单号列表
DECLARE @OrderList NVARCHAR(MAX);
SELECT @OrderList = STUFF(
(SELECT ',' + QUOTENAME(gw_sn, '""')
FROM #TempGWSN
ORDER BY id
FOR XML PATH('')
), 1, 1, ''
);
DECLARE @txt nvarchar(max);
SELECT @txt=
'
BEGIN TRY
DROP TABLE #tempT1;
DROP TABLE #tempT2;
DROP TABLE #SplitIDS;
END TRY
BEGIN CATCH
END CATCH
select * into #tempT1 from (
SELECT
a.tower_ids,
b.gw_sn,
a.gw_order_sn AS "采购订单号",
a.contract_sn AS "合同号",
a.item_id AS "行项目ID",
a.customer as "客户名称",
CASE WHEN a.original_bidder=''1000014692'' THEN ''顺泰'' when a.original_bidder=''1000091718'' THEN ''瑜煌'' END AS ''中标主体'',
a.product_name AS "塔类型",
a.project_name AS "工程名称",
a.short_name AS "工程简称",
a.delivery_date AS "合同交货期",
a.job_sn AS "生产单号",
a.job_sn,
a.release_sn AS "生产工单",
CONVERT(VARCHAR(100), a.release_date, 120) AS "下达时间",
-- case when a.GTXQ_pushed = 1 THEN ''已上传'' when a.GTXQ_pushed = 0 THEN ''未传'' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN ''已上传'' when a.bom_eip_status = 0 THEN ''未传'' END AS "参数规范-技术加工",
case when a.eip_pushed =1 THEN ''已上传'' when a.eip_pushed is NULL THEN NULL ELSE ''未传'' END as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN ''已上传'' when a.bom_eip_status = 0 THEN ''未传'' END AS "原材料数据",
case when a.YCL_WZGL = 0 THEN ''未关联'' when a.YCL_WZGL = 1 THEN ''未完全匹配'' when a.YCL_WZGL = 2 THEN ''完整关联'' END AS "原材料数据关联",
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = ''未生成'' then ''未生成'' when a.BZ_exist is NULL then null ELSE ''已生成'' END as "成品数据-生成",
a.CP_isUpload as "成品数据-上传",
a.plan_start_date AS "工单-计划开始时间",
a.plan_finish_date AS "工单-计划结束时间",
a.actual_start_date AS "工单-实际开始时间",
a.actual_finish_date AS "工单-实际结束时间",
a.loftingPlanStartDate AS "理论计划开始时间",
a.delivery_date AS "理论计划完成时间",
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号"
-- 计划开始时间 若没有，则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有，则取 contract.delivery_date,
-- 实际开始时间 若没有，则取 release.nested_material_date,
-- SELECT release_id,MIN(eip_pushed) AS min_eip,MAX(eip_pushed) AS max_eip FROM eip_tower GROUP BY release_id
FROM OPENQUERY(FD_MES_DB,''
SELECT
a.*,
IF(c.min_push = 1 AND c.max_push = 1, 1, 0) AS YCL_pushed, -- 生产工单原材料上传
d.YCL_WZGL, -- 生产工单原材料关联性
e.SCSYGC_isComplete,
e.SCSYGC_isUpload,
IFNULL(f.dxwd_isUpload,"未生成") as dxwd_isUpload,
IFNULL(g.release_sn,"未生成") as BZ_exist, -- 包装是否存在
g.CP_isUpload -- 包装是否上传
FROM (
SELECT
a.gw_order_sn,
a.contract_sn,
a.item_id,
a.customer,
a.original_bidder,
a.product_name,
a.project_name,
a.short_name,
a.delivery_date,
b.id AS release_sub_id,
b.job_id,
c.job_sn,
c.lofting_id,
c.tower_ids,
b.release_sn,
e.release_date,
b.plan_start_date,
b.plan_finish_date,
b.actual_start_date,
b.actual_finish_date,
c.plan_start_date AS loftingPlanStartDate,
e.nested_material_date,
b.bom_eip_status, -- 参数规范-技术加工(bom)
SUBSTRING(tower_sns, 1, 4000) AS tower_sns ,
d.eip_pushed -- 参数规范-工程原材料
FROM
contract a
INNER JOIN release_sub b
ON a.id = b.contract_id
INNER JOIN lofting_job c
ON b.job_id = c.ID
INNER JOIN release_release_sub_temp s
ON s.release_sub_id = b.id
INNER JOIN `release` e
ON s.release_id = e.id
LEFT JOIN eip_contract_material d
ON b.release_sn = d.release_sn
WHERE
b.type = 1
AND a.is_gw_order = 1
AND a.delete_state = 0
AND a.gw_order_sn IN ('+@OrderList+ ')
) AS a
LEFT JOIN (
SELECT
release_id,
MIN(push_flag) AS min_push,
MAX(push_flag) AS max_push
FROM eip_machine_base_release
GROUP BY release_id
) AS c ON a.release_sub_id = c.release_id
left JOIN (
SELECT
job_id,
CASE
WHEN count(*) = 0 THEN 0 -- 还没生成
WHEN COUNT(eq_test_no)<COUNT(*) THEN 1 -- 没有完全匹配
WHEN COUNT(eq_test_no) = COUNT(*) THEN 2 -- 完全匹配
ELSE -1
END AS YCL_WZGL
FROM
eip_machine_base_release_material
GROUP BY
job_id
) as d on a.job_id=d.job_id
left join (
SELECT
release_id,
CASE
WHEN COUNT(DISTINCT data_type) = 2 THEN "完整"
ELSE "不完整"
END AS SCSYGC_isComplete,
CASE
WHEN SUM(CASE WHEN eip_status != 1 THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS SCSYGC_isUpload
FROM
eip_prod_process
GROUP BY
release_id
) as e on a.release_sub_id=e.release_id
left join (
SELECT
release_id,
CASE
WHEN SUM(CASE WHEN push_status !=1 or push_status is null THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS dxwd_isUpload
FROM
eip_temperature
GROUP BY release_id
) as f on a.release_sub_id=f.release_id
left join (
SELECT release_sn,
case
WHEN SUM(case WHEN eip_status !=1 or eip_status is null THEN 1 else 0 END) >0 then "未上传"
ELSE "已上传"
END AS CP_isUpload
FROM eip_product GROUP BY release_sn
) as g on a.release_sn=g.release_sn
'') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn ) ss;
select * into #tempT2 from (
select * FROM OPENQUERY(FD_MES_DB,''
SELECT a.id,a.tower_id,b.eip_pushed FROM tower_lofting a INNER JOIN eip_tower b on a.tower_id=b.tower_id''
)
) s2
-- select * from #tempT1; -- 得到总表
-- select * from #tempT2; -- 得到杆塔表
CREATE TABLE #SplitIDS (
job_sn varchar(255),
id VARCHAR(255)
);
-- 拆分 tempT1 的 IDS 字段并插入到临时表
INSERT INTO #SplitIDS (job_sn, id)
SELECT
job_sn,
value AS id
FROM
#tempT1
CROSS APPLY
STRING_SPLIT(tower_ids, '','');
-- select * from #SplitIDS
-- 继续后续查询
WITH JoinedData AS (
SELECT
s.job_sn,
s.id,
t.eip_pushed
FROM
#SplitIDS s
LEFT JOIN
#tempT2 t ON s.id = t.id
),
GroupedData AS (
SELECT
job_sn,
CASE
WHEN COUNT(*) = SUM(eip_pushed) THEN ''已上传'' ELSE ''未上传''
END AS GTXQ_up
FROM
JoinedData
GROUP BY
job_sn
)
-- 最终结果
SELECT
t.gw_sn as "国网订单号",
t.采购订单号,
t.合同号,
t.行项目ID,
t.客户名称,
t.中标主体,
t.塔类型,
t.工程名称,
t.工程简称,
t.合同交货期,
t.生产单号,
t.生产工单,
t.下达时间,
g.GTXQ_up as "参数规范-杆塔详情",
t."参数规范-技术加工",
t."参数规范-工程原材料",
t.原材料数据,
t.原材料数据关联,
t."生产实验过程-完整性",
t."生产实验过程-上传",
t.镀锌温度,
t."成品数据-生成",
t."成品数据-上传",
t."工单-计划开始时间",
t."工单-计划结束时间",
t."工单-实际开始时间",
t."工单-实际结束时间",
t.理论计划开始时间,
t.理论计划完成时间,
t.理论实际开始时间,
t.杆塔号
FROM
#tempT1 t
LEFT JOIN
GroupedData g ON t.job_sn = g.job_sn order by ''采购订单号'',''生产工单''
DROP TABLE #tempT1;
DROP TABLE #tempT2;
DROP TABLE #SplitIDS;
';
EXEC(@txt);
DROP TABLE #TempGWSN;
END
|
|
修复 杆塔详情版 [SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode
BEGIN
BEGIN TRY
DROP TABLE #TempGWSN;
END TRY
BEGIN CATCH
-- 错误处理逻辑，这里可以不做任何事情，因为表可能不存在
-- 如果需要，可以通过 ERROR_NUMBER() 和 ERROR_MESSAGE() 获取错误信息
END CATCH
CREATE TABLE #TempGWSN (
id INT IDENTITY(1,1) PRIMARY KEY,
gw_sn VARCHAR(255)
);
INSERT INTO #TempGWSN (gw_sn)
VALUES
('4500856325');
-- 动态拼接订单号列表
DECLARE @OrderList NVARCHAR(MAX);
SELECT @OrderList = STUFF(
(SELECT ',' + QUOTENAME(gw_sn, '""')
FROM #TempGWSN
ORDER BY id
FOR XML PATH('')
), 1, 1, ''
);
DECLARE @txt nvarchar(max);
SELECT @txt=
'
BEGIN TRY
DROP TABLE #tempT1;
DROP TABLE #tempT2;
DROP TABLE #SplitIDS;
END TRY
BEGIN CATCH
END CATCH
select * into #tempT1 from (
SELECT
a.tower_ids,
b.gw_sn,
a.gw_order_sn AS "采购订单号",
a.contract_sn AS "合同号",
a.item_id AS "行项目ID",
a.customer as "客户名称",
CASE WHEN a.original_bidder=''1000014692'' THEN ''顺泰'' when a.original_bidder=''1000091718'' THEN ''瑜煌'' END AS ''中标主体'',
a.product_name AS "塔类型",
a.project_name AS "工程名称",
a.short_name AS "工程简称",
a.delivery_date AS "合同交货期",
a.job_sn AS "生产单号",
a.job_sn,
a.release_sn AS "生产工单",
CONVERT(VARCHAR(100), a.release_date, 120) AS "下达时间",
-- case when a.GTXQ_pushed = 1 THEN ''已上传'' when a.GTXQ_pushed = 0 THEN ''未传'' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN ''已上传'' when a.bom_eip_status = 0 THEN ''未传'' END AS "参数规范-技术加工",
case when a.eip_pushed =1 THEN ''已上传'' when a.eip_pushed is NULL THEN NULL ELSE ''未传'' END as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN ''已上传'' when a.bom_eip_status = 0 THEN ''未传'' END AS "原材料数据",
case when a.YCL_WZGL = 0 THEN ''未关联'' when a.YCL_WZGL = 1 THEN ''未完全匹配'' when a.YCL_WZGL = 2 THEN ''完整关联'' END AS "原材料数据关联",
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = ''未生成'' then ''未生成'' when a.BZ_exist is NULL then null ELSE ''已生成'' END as "成品数据-生成",
a.CP_isUpload as "成品数据-上传",
a.plan_start_date AS "工单-计划开始时间",
a.plan_finish_date AS "工单-计划结束时间",
a.actual_start_date AS "工单-实际开始时间",
a.actual_finish_date AS "工单-实际结束时间",
a.loftingPlanStartDate AS "理论计划开始时间",
a.delivery_date AS "理论计划完成时间",
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号"
-- 计划开始时间 若没有，则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有，则取 contract.delivery_date,
-- 实际开始时间 若没有，则取 release.nested_material_date,
-- SELECT release_id,MIN(eip_pushed) AS min_eip,MAX(eip_pushed) AS max_eip FROM eip_tower GROUP BY release_id
FROM OPENQUERY(FD_MES_DB,''
SELECT
a.*,
IF(c.min_push = 1 AND c.max_push = 1, 1, 0) AS YCL_pushed, -- 生产工单原材料上传
d.YCL_WZGL, -- 生产工单原材料关联性
e.SCSYGC_isComplete,
e.SCSYGC_isUpload,
IFNULL(f.dxwd_isUpload,"未生成") as dxwd_isUpload,
IFNULL(g.release_sub_id,"未生成") as BZ_exist, -- 包装是否存在
g.CP_isUpload -- 包装是否上传
FROM (
SELECT
a.gw_order_sn,
a.contract_sn,
a.item_id,
a.customer,
a.original_bidder,
a.product_name,
a.project_name,
a.short_name,
a.delivery_date,
b.id AS release_sub_id,
b.job_id,
c.job_sn,
c.lofting_id,
c.tower_ids,
b.release_sn,
e.release_date,
b.plan_start_date,
b.plan_finish_date,
b.actual_start_date,
b.actual_finish_date,
c.plan_start_date AS loftingPlanStartDate,
e.nested_material_date,
b.bom_eip_status, -- 参数规范-技术加工(bom)
SUBSTRING(tower_sns, 1, 4000) AS tower_sns ,
d.eip_pushed -- 参数规范-工程原材料
FROM
contract a
INNER JOIN release_sub b
ON a.id = b.contract_id
INNER JOIN lofting_job c
ON b.job_id = c.ID
INNER JOIN release_release_sub_temp s
ON s.release_sub_id = b.id
INNER JOIN `release` e
ON s.release_id = e.id
LEFT JOIN eip_contract_material d
ON b.release_sn = d.release_sn
WHERE
b.type = 1
AND a.is_gw_order = 1
AND a.delete_state = 0
AND a.gw_order_sn IN ('+@OrderList+ ')
) AS a
LEFT JOIN (
SELECT
release_id,
MIN(push_flag) AS min_push,
MAX(push_flag) AS max_push
FROM eip_machine_base_release
GROUP BY release_id
) AS c ON a.release_sub_id = c.release_id
left JOIN (
SELECT
job_id,
CASE
WHEN count(*) = 0 THEN 0 -- 还没生成
WHEN COUNT(eq_test_no)<COUNT(*) THEN 1 -- 没有完全匹配
WHEN COUNT(eq_test_no) = COUNT(*) THEN 2 -- 完全匹配
ELSE -1
END AS YCL_WZGL
FROM
eip_machine_base_release_material
GROUP BY
job_id
) as d on a.job_id=d.job_id
left join (
SELECT
release_id,
CASE
WHEN COUNT(DISTINCT data_type) = 2 THEN "完整"
ELSE "不完整"
END AS SCSYGC_isComplete,
CASE
WHEN SUM(CASE WHEN eip_status != 1 THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS SCSYGC_isUpload
FROM
eip_prod_process
GROUP BY
release_id
) as e on a.release_sub_id=e.release_id
left join (
SELECT
release_id,
CASE
WHEN SUM(CASE WHEN push_status !=1 or push_status is null THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS dxwd_isUpload
FROM
eip_temperature
GROUP BY release_id
) as f on a.release_sub_id=f.release_id
left join (
SELECT a.release_sub_id,
case
when SUM(case WHEN eip_status !=1 or eip_status is null THEN 1 else 0 END) >0 then "未上传"
ELSE "已上传"
END AS CP_isUpload
FROM package_import a left join eip_product b on a.tower_id=b.tower_id
GROUP BY a.release_sub_id
) as g on a.release_sub_id=g.release_sub_id
'') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn ) ss;
select * into #tempT2 from (
select * FROM OPENQUERY(FD_MES_DB,''
SELECT a.id,a.tower_id,b.eip_pushed FROM tower_lofting a INNER JOIN eip_tower b on a.tower_id=b.tower_id''
)
) s2
-- select * from #tempT1; -- 得到总表
-- select * from #tempT2; -- 得到杆塔表
CREATE TABLE #SplitIDS (
job_sn varchar(255),
id VARCHAR(255)
);
-- 拆分 tempT1 的 IDS 字段并插入到临时表
INSERT INTO #SplitIDS (job_sn, id)
SELECT
job_sn,
value AS id
FROM
#tempT1
CROSS APPLY
STRING_SPLIT(tower_ids, '','');
-- select * from #SplitIDS
-- 继续后续查询
WITH JoinedData AS (
SELECT
s.job_sn,
s.id,
t.eip_pushed
FROM
#SplitIDS s
LEFT JOIN
#tempT2 t ON s.id = t.id
),
GroupedData AS (
SELECT
job_sn,
CASE
WHEN COUNT(*) = SUM(eip_pushed) THEN ''已上传'' ELSE ''未上传''
END AS GTXQ_up
FROM
JoinedData
GROUP BY
job_sn
)
-- 最终结果
SELECT
t.gw_sn as "国网订单号",
t.采购订单号,
t.合同号,
t.行项目ID,
t.客户名称,
t.中标主体,
t.塔类型,
t.工程名称,
t.工程简称,
t.合同交货期,
t.生产单号,
t.生产工单,
t.下达时间,
g.GTXQ_up as "参数规范-杆塔详情",
t."参数规范-技术加工",
t."参数规范-工程原材料",
t.原材料数据,
t.原材料数据关联,
t."生产实验过程-完整性",
t."生产实验过程-上传",
t.镀锌温度,
t."成品数据-生成",
t."成品数据-上传",
t."工单-计划开始时间",
t."工单-计划结束时间",
t."工单-实际开始时间",
t."工单-实际结束时间",
t.理论计划开始时间,
t.理论计划完成时间,
t.理论实际开始时间,
t.杆塔号
FROM
#tempT1 t
LEFT JOIN
GroupedData g ON t.job_sn = g.job_sn;
DROP TABLE #tempT1;
DROP TABLE #tempT2;
DROP TABLE #SplitIDS;
';
EXEC(@txt);
DROP TABLE #TempGWSN;
END
|
|
本帖最后由 李维强-15级 于 2025-3-7 15:14 编辑 只输入一次采购订单版本 [SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode
BEGIN
BEGIN TRY
DROP TABLE #TempGWSN;
END TRY
BEGIN CATCH
-- 错误处理逻辑，这里可以不做任何事情，因为表可能不存在
-- 如果需要，可以通过 ERROR_NUMBER() 和 ERROR_MESSAGE() 获取错误信息
END CATCH
CREATE TABLE #TempGWSN (
id INT IDENTITY(1,1) PRIMARY KEY,
gw_sn VARCHAR(255)
);
INSERT INTO #TempGWSN (gw_sn)
VALUES
('4500844755'),
('5000002642'),
('4500838263'),
('4500846064'),
('4500846082'),
('4500848101'),
('4500853530'),
('4500856325'),
('4500858055'),
('4500858867'),
('4500865012'),
('4500865013'),
('4500872020'),
('4500872442'),
('4500873311'),
('4500873312'),
('4500873313'),
('4500873314'),
('4500873315'),
('4500878463'),
('4500878470'),
('4500879452'),
('5000002592'),
('5000002619'),
('5000002996');
-- 动态拼接订单号列表
DECLARE @OrderList NVARCHAR(MAX);
SELECT @OrderList = STUFF(
(SELECT ',' + QUOTENAME(gw_sn, '""')
FROM #TempGWSN
ORDER BY id
FOR XML PATH('')
), 1, 1, ''
);
DECLARE @txt nvarchar(max);
SELECT @txt=
'SELECT
b.gw_sn,
a.gw_order_sn AS "采购订单号",
a.contract_sn AS "合同号",
a.item_id AS "行项目ID",
a.customer as "客户名称",
CASE WHEN a.original_bidder=''1000014692'' THEN ''顺泰'' when a.original_bidder=''1000091718'' THEN ''瑜煌'' END AS ''中标主体'',
a.product_name AS "塔类型",
a.project_name AS "工程名称",
a.short_name AS "工程简称",
a.delivery_date AS "合同交货期",
a.job_sn AS "生产单号",
a.release_sn AS "生产工单",
CONVERT(VARCHAR(100), a.release_date, 120) AS "下达时间",
case when a.GTXQ_pushed = 1 THEN ''已上传'' when a.GTXQ_pushed = 0 THEN ''未传'' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN ''已上传'' when a.bom_eip_status = 0 THEN ''未传'' END AS "参数规范-技术加工",
case when a.eip_pushed =1 THEN ''已上传'' when a.eip_pushed is NULL THEN NULL ELSE ''未传'' END as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN ''已上传'' when a.bom_eip_status = 0 THEN ''未传'' END AS "原材料数据",
case when a.YCL_WZGL = 0 THEN ''未关联'' when a.YCL_WZGL = 1 THEN ''未完全匹配'' when a.YCL_WZGL = 2 THEN ''完整关联'' END AS "原材料数据关联",
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = ''未生成'' then ''未生成'' when a.BZ_exist is NULL then null ELSE ''已生成'' END as "成品数据-生成",
a.CP_isUpload as "成品数据-上传",
a.plan_start_date AS "工单-计划开始时间",
a.plan_finish_date AS "工单-计划结束时间",
a.actual_start_date AS "工单-实际开始时间",
a.actual_finish_date AS "工单-实际结束时间",
a.loftingPlanStartDate AS "理论计划开始时间",
a.delivery_date AS "理论计划完成时间",
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号"
-- 计划开始时间 若没有，则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有，则取 contract.delivery_date,
-- 实际开始时间 若没有，则取 release.nested_material_date,
-- SELECT release_id,MIN(eip_pushed) AS min_eip,MAX(eip_pushed) AS max_eip FROM eip_tower GROUP BY release_id
FROM OPENQUERY(FD_MES_DB,''
SELECT
a.*,
IF(b.min_eip = 1 AND b.max_eip = 1, 1, 0) AS GTXQ_pushed, -- 参数规范-杆塔详情
IF(c.min_push = 1 AND c.max_push = 1, 1, 0) AS YCL_pushed, -- 生产工单原材料上传
d.YCL_WZGL, -- 生产工单原材料关联性
e.SCSYGC_isComplete,
e.SCSYGC_isUpload,
IFNULL(f.dxwd_isUpload,"未生成") as dxwd_isUpload,
IFNULL(g.release_sub_id,"未生成") as BZ_exist, -- 包装是否存在
g.CP_isUpload -- 包装是否上传
FROM (
SELECT
a.gw_order_sn,
a.contract_sn,
a.item_id,
a.customer,
a.original_bidder,
a.product_name,
a.project_name,
a.short_name,
a.delivery_date,
b.id AS release_sub_id,
b.job_id,
c.job_sn,
c.lofting_id,
b.release_sn,
e.release_date,
b.plan_start_date,
b.plan_finish_date,
b.actual_start_date,
b.actual_finish_date,
c.plan_start_date AS loftingPlanStartDate,
e.nested_material_date,
b.bom_eip_status, -- 参数规范-技术加工(bom)
SUBSTRING(tower_sns, 1, 4000) AS tower_sns ,
d.eip_pushed -- 参数规范-工程原材料
FROM
contract a
INNER JOIN release_sub b
ON a.id = b.contract_id
INNER JOIN lofting_job c
ON b.job_id = c.ID
INNER JOIN release_release_sub_temp s
ON s.release_sub_id = b.id
INNER JOIN `release` e
ON s.release_id = e.id
LEFT JOIN eip_contract_material d
ON b.release_sn = d.release_sn
WHERE
b.type = 1
AND a.is_gw_order = 1
AND a.delete_state = 0
AND a.gw_order_sn IN ('+@OrderList+ ')
) AS a
LEFT JOIN (
SELECT release_sn,MIN(eip_pushed) AS min_eip, MAX(eip_pushed) AS max_eip FROM eip_tower GROUP BY release_sn
) AS b ON a.release_sn = b.release_sn
LEFT JOIN (
SELECT
release_id,
MIN(push_flag) AS min_push,
MAX(push_flag) AS max_push
FROM eip_machine_base_release
GROUP BY release_id
) AS c ON a.release_sub_id = c.release_id
left JOIN (
SELECT
job_id,
CASE
WHEN count(*) = 0 THEN 0 -- 还没生成
WHEN COUNT(eq_test_no)<COUNT(*) THEN 1 -- 没有完全匹配
WHEN COUNT(eq_test_no) = COUNT(*) THEN 2 -- 完全匹配
ELSE -1
END AS YCL_WZGL
FROM
eip_machine_base_release_material
GROUP BY
job_id
) as d on a.job_id=d.job_id
left join (
SELECT
release_id,
CASE
WHEN COUNT(DISTINCT data_type) = 2 THEN "完整"
ELSE "不完整"
END AS SCSYGC_isComplete,
CASE
WHEN SUM(CASE WHEN eip_status != 1 THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS SCSYGC_isUpload
FROM
eip_prod_process
GROUP BY
release_id
) as e on a.release_sub_id=e.release_id
left join (
SELECT
release_id,
CASE
WHEN SUM(CASE WHEN push_status !=1 or push_status is null THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS dxwd_isUpload
FROM
eip_temperature
GROUP BY release_id
) as f on a.release_sub_id=f.release_id
left join (
SELECT a.release_sub_id,
case
when SUM(case WHEN eip_status !=1 or eip_status is null THEN 1 else 0 END) >0 then "未上传"
ELSE "已上传"
END AS CP_isUpload
FROM package_import a left join eip_product b on a.tower_id=b.tower_id
GROUP BY a.release_sub_id
) as g on a.release_sub_id=g.release_sub_id
'') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn
ORDER BY gw_sn';
EXEC(@txt);
DROP TABLE #TempGWSN;
END
|
|
本帖最后由 李维强-15级 于 2025-3-6 22:02 编辑 BEGIN TRY DROP TABLE #TempGWSN; END TRY BEGIN CATCH -- 忽略表不存在错误:ml-citation{ref="1" data="citationList"} END CATCH CREATE TABLE #TempGWSN ( id INT IDENTITY(1,1) PRIMARY KEY, gw_sn VARCHAR(255) ); INSERT INTO #TempGWSN (gw_sn) VALUES ('0101043170'), ('0101057386'), ('0101043505'), ('0101043506'), ('0101043509'), ('0101043510'), ('0101043511'); -- 动态拼接订单号列表 DECLARE @OrderList NVARCHAR(MAX); SELECT @OrderList = STUFF( (SELECT ',' + QUOTENAME(gw_sn, '""') FROM #TempGWSN ORDER BY id FOR XML PATH('') ), 1, 1, '' ); -- 将动态生成的订单号嵌入OPENQUERY查询 SELECT b.gw_sn, a.gw_order_sn AS "采购订单号", ... (其他字段保持不变) FROM OPENQUERY(FD_MES_DB, ' SELECT a.*, IF(b.min_eip = 1 AND b.max_eip = 1, 1, 0) AS GTXQ_pushed, ... (其他子查询逻辑保持不变) WHERE b.type = 1 AND a.is_gw_order = 1 AND a.delete_state = 0 AND a.gw_order_sn IN (' + @OrderList + ') -- 替换为动态拼接的订单号列表 ') AS a INNER JOIN #TempGWSN b ON a.gw_order_sn = b.gw_sn; |
|
本帖最后由 李维强-15级 于 2025-3-5 11:09 编辑 [SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode
BEGIN
BEGIN TRY
DROP TABLE #TempGWSN;
END TRY
BEGIN CATCH
-- 错误处理逻辑，这里可以不做任何事情，因为表可能不存在
-- 如果需要，可以通过 ERROR_NUMBER() 和 ERROR_MESSAGE() 获取错误信息
END CATCH
CREATE TABLE #TempGWSN (
id INT IDENTITY(1,1) PRIMARY KEY,
gw_sn VARCHAR(255)
);
INSERT INTO #TempGWSN (gw_sn)
VALUES
('0101043170'),
('0101057386'),
('0101043505'),
('0101043506'),
('0101043509'),
('0101043510'),
('0101043511');
SELECT
b.gw_sn,
a.gw_order_sn AS "采购订单号",
a.contract_sn AS "合同号",
a.item_id AS "行项目ID",
a.customer as "客户名称",
CASE WHEN a.original_bidder='1000014692' THEN '顺泰' when a.original_bidder='1000091718' THEN '瑜煌' END AS '中标主体',
a.product_name AS "塔类型",
a.project_name AS "工程名称",
a.short_name AS "工程简称",
a.delivery_date AS "合同交货期",
a.job_sn AS "生产单号",
a.release_sn AS "生产工单",
CONVERT(VARCHAR(100), a.release_date, 120) AS "下达时间",
case when a.GTXQ_pushed = 1 THEN '已上传' when a.GTXQ_pushed = 0 THEN '未传' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN '已上传' when a.bom_eip_status = 0 THEN '未传' END AS "参数规范-技术加工",
case when a.eip_pushed =1 THEN '已上传' when a.eip_pushed is NULL THEN NULL ELSE '未传' END as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN '已上传' when a.bom_eip_status = 0 THEN '未传' END AS "原材料数据",
case when a.YCL_WZGL = 0 THEN '未关联' when a.YCL_WZGL = 1 THEN '未完全匹配' when a.YCL_WZGL = 2 THEN '完整关联' END AS "原材料数据关联",
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = '未生成' then '未生成' when a.BZ_exist is NULL then null ELSE '已生成' END as "成品数据-生成",
a.CP_isUpload as "成品数据-上传",
a.plan_start_date AS "工单-计划开始时间",
a.plan_finish_date AS "工单-计划结束时间",
a.actual_start_date AS "工单-实际开始时间",
a.actual_finish_date AS "工单-实际结束时间",
a.loftingPlanStartDate AS "理论计划开始时间",
a.delivery_date AS "理论计划完成时间",
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号"
-- 计划开始时间 若没有，则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有，则取 contract.delivery_date,
-- 实际开始时间 若没有，则取 release.nested_material_date,
-- SELECT release_id,MIN(eip_pushed) AS min_eip,MAX(eip_pushed) AS max_eip FROM eip_tower GROUP BY release_id
FROM OPENQUERY(FD_MES_DB,'
SELECT
a.*,
IF(b.min_eip = 1 AND b.max_eip = 1, 1, 0) AS GTXQ_pushed, -- 参数规范-杆塔详情
IF(c.min_push = 1 AND c.max_push = 1, 1, 0) AS YCL_pushed, -- 生产工单原材料上传
d.YCL_WZGL, -- 生产工单原材料关联性
e.SCSYGC_isComplete,
e.SCSYGC_isUpload,
IFNULL(f.dxwd_isUpload,"未生成") as dxwd_isUpload,
IFNULL(g.release_sub_id,"未生成") as BZ_exist, -- 包装是否存在
g.CP_isUpload -- 包装是否上传
FROM (
SELECT
a.gw_order_sn,
a.contract_sn,
a.item_id,
a.customer,
a.original_bidder,
a.product_name,
a.project_name,
a.short_name,
a.delivery_date,
b.id AS release_sub_id,
b.job_id,
c.job_sn,
c.lofting_id,
b.release_sn,
e.release_date,
b.plan_start_date,
b.plan_finish_date,
b.actual_start_date,
b.actual_finish_date,
c.plan_start_date AS loftingPlanStartDate,
e.nested_material_date,
b.bom_eip_status, -- 参数规范-技术加工(bom)
SUBSTRING(tower_sns, 1, 4000) AS tower_sns ,
d.eip_pushed -- 参数规范-工程原材料
FROM
contract a
INNER JOIN release_sub b
ON a.id = b.contract_id
INNER JOIN lofting_job c
ON b.job_id = c.ID
INNER JOIN release_release_sub_temp s
ON s.release_sub_id = b.id
INNER JOIN `release` e
ON s.release_id = e.id
LEFT JOIN eip_contract_material d
ON b.release_sn = d.release_sn
WHERE
b.type = 1
AND a.is_gw_order = 1
AND a.delete_state = 0
AND a.gw_order_sn IN (
"0101043170",
"0101057386",
"0101043505",
"0101043506",
"0101043509",
"0101043510",
"0101043511"
)
) AS a
LEFT JOIN (
SELECT a.lofting_id,MIN(b.eip_pushed) AS min_eip, MAX(b.eip_pushed) AS max_eip FROM tower_lofting a INNER JOIN eip_tower b on a.tower_id=b.tower_id GROUP BY lofting_id
) AS b ON a.lofting_id = b.lofting_id
LEFT JOIN (
SELECT
release_id,
MIN(push_flag) AS min_push,
MAX(push_flag) AS max_push
FROM eip_machine_base_release
GROUP BY release_id
) AS c ON a.release_sub_id = c.release_id
left JOIN (
SELECT
job_id,
CASE
WHEN count(*) = 0 THEN 0 -- 还没生成
WHEN COUNT(eq_test_no)<COUNT(*) THEN 1 -- 没有完全匹配
WHEN COUNT(eq_test_no) = COUNT(*) THEN 2 -- 完全匹配
ELSE -1
END AS YCL_WZGL
FROM
eip_machine_base_release_material
GROUP BY
job_id
) as d on a.job_id=d.job_id
left join (
SELECT
release_id,
CASE
WHEN COUNT(DISTINCT data_type) = 2 THEN "完整"
ELSE "不完整"
END AS SCSYGC_isComplete,
CASE
WHEN SUM(CASE WHEN eip_status != 1 THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS SCSYGC_isUpload
FROM
eip_prod_process
GROUP BY
release_id
) as e on a.release_sub_id=e.release_id
left join (
SELECT
release_id,
CASE
WHEN SUM(CASE WHEN push_status !=1 or push_status is null THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS dxwd_isUpload
FROM
eip_temperature
GROUP BY release_id
) as f on a.release_sub_id=f.release_id
left join (
SELECT a.release_sub_id,
case
when SUM(case WHEN eip_status !=1 or eip_status is null THEN 1 else 0 END) >0 then "未上传"
ELSE "已上传"
END AS CP_isUpload
FROM package_import a left join eip_product b on a.tower_id=b.tower_id
GROUP BY a.release_sub_id
) as g on a.release_sub_id=g.release_sub_id
') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn
ORDER BY gw_sn
DROP TABLE #TempGWSN;
END
|
|
沈工，请教一个问题。 我们在上传“参数规范-技术加工”数据的时候，会包含“生产工单号”、“质量追溯码”这两个数据。 因为生产当中合并加工的原因，他们会在生产某几基塔的公共段时，建一个生产工单。在生产腿段时，又建一个生产工单。最终的结果就是在传"参数规范-技术加工”的数据之时存在“质量追溯码”相同，而“生产工单号”不同的情况。 对于这种情况，国网平台会根据“质量追溯码”的信息，关联到某一基塔的“参数规范-技术加工”数据么？ |
|
本帖最后由 李维强-15级 于 2025-3-3 17:56 编辑 [SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode
BEGIN
BEGIN TRY
DROP TABLE #TempGWSN;
END TRY
BEGIN CATCH
-- 错误处理逻辑，这里可以不做任何事情，因为表可能不存在
-- 如果需要，可以通过 ERROR_NUMBER() 和 ERROR_MESSAGE() 获取错误信息
END CATCH
CREATE TABLE #TempGWSN (
id INT IDENTITY(1,1) PRIMARY KEY,
gw_sn VARCHAR(255)
);
INSERT INTO #TempGWSN (gw_sn)
VALUES
('4500872442'),
('4500873315'),
('4500873314'),
('4500873313'),
('4500872020'),
('4500873312'),
('4500873311'),
('4500865013'),
('4500865012'),
('5000002876'),
('4500858867'),
('4500858055'),
('4500856325'),
('4500877783'),
('4500878470'),
('4500878463'),
('5000002996'),
('4500879452');
SELECT b.gw_sn,a.* FROM OPENQUERY(FD_MES_DB,
'
SELECT gw_order_sn,gw_order_sn as "采购订单号",contract_sn as "内部合同号",project_name as "工程名称",
CASE WHEN bidder="1000014692" THEN "顺泰" when bidder="1000091718" THEN "瑜煌" END AS "中标主体",
item_id as "订单行项目ID",task_sn as "生产订单",release_sn as "生产工单号",release_date as "下达时间",
delivery_date as "合同交货日期",change_delivery_date as "到货期变更日期",
case when eip_tower_status=1 THEN "已上传" else "未上传" END as "参数-基塔信息",
case when bom_eip_status=0 THEN "已上传" else "未传" END as "参数-技术加工",
case when eip_contract_material_status>0 THEN "已上传" else "未传" END as "原材料",
case when eip_machine_base_release_status=11 THEN "部分关联已上传" when eip_machine_base_release_status=13 THEN "部分关联未上传" when eip_machine_base_release_status=0 then "待维护" END as "生产工单原材料",
case when production_inspection_status=5 THEN "已维护" else "等待维护" END as "生产过程检验",
case when prod_process_status=6 THEN "完整且上传" else "等待维护" END as "生产过程信息采集",
case when temperature_status=1 THEN "已上传" else "等待维护" END as "镀锌温度",
case when product_status=1 THEN "已上传" else "等待维护" END as "成品"
FROM eip_internet_things_warning
') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn
ORDER BY gw_sn,a.生产工单号
DROP TABLE #TempGWSN;
END
-- 计划开始时间 若没有，则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有，则取 contract.delivery_date,
-- 实际开始时间 若没有，则取 release.nested_material_date,
SELECT
a.*,
IF(b.min_eip = 1 AND b.max_eip = 1, 1, 0) AS GTXQ_pushed, -- 参数规范-杆塔详情
IF(c.min_push = 1 AND c.max_push = 1, 1, 0) AS YCL_pushed, -- 生产工单原材料上传
d.YCL_WZGL, -- 生产工单原材料关联性
e.SCSYGC_isComplete,
e.SCSYGC_isUpload,
IFNULL(f.dxwd_isUpload,'未生成') as dxwd_isUpload,
IFNULL(g.release_sub_id,'未生成') as BZ_exist,
g.CP_isUpload
FROM (
SELECT
a.gw_order_sn,
a.contract_sn,
a.item_id,
a.original_bidder,
a.product_name,
a.project_name,
a.short_name,
a.delivery_date,
b.id AS release_sub_id,
b.job_id,
c.job_sn,
b.release_sn,
e.release_date,
b.plan_start_date,
b.plan_finish_date,
b.actual_start_date,
b.actual_finish_date,
c.plan_start_date AS loftingPlanStartDate,
e.nested_material_date,
b.bom_eip_status, -- 参数规范-技术加工(bom)
d.tower_sns,
d.eip_pushed -- 参数规范-工程原材料
FROM
contract a
INNER JOIN release_sub b
ON a.id = b.contract_id
INNER JOIN lofting_job c
ON b.job_id = c.ID
INNER JOIN release_release_sub_temp s
ON s.release_sub_id = b.id
INNER JOIN `release` e
ON s.release_id = e.id
LEFT JOIN eip_contract_material d
ON b.id = d.release_id
WHERE
b.type = 1
AND a.is_gw_order = 1
AND a.delete_state = 0
AND a.gw_order_sn IN ('5000849129')
) AS a
LEFT JOIN (
SELECT
release_id,
MIN(eip_pushed) AS min_eip,
MAX(eip_pushed) AS max_eip
FROM eip_tower
GROUP BY release_id
) AS b ON a.release_sub_id = b.release_id
LEFT JOIN (
SELECT
release_id,
MIN(push_flag) AS min_push,
MAX(push_flag) AS max_push
FROM eip_machine_base_release
GROUP BY release_id
) AS c ON a.release_sub_id = c.release_id
left JOIN (
SELECT
job_id,
CASE
WHEN count(*) = 0 THEN 0 -- 还没生成
WHEN COUNT(eq_test_no)<COUNT(*) THEN 1 -- 没有完全匹配
WHEN COUNT(eq_test_no) = COUNT(*) THEN 2 -- 完全匹配
ELSE -1
END AS YCL_WZGL
FROM
eip_machine_base_release_material
GROUP BY
job_id
) as d on a.job_id=d.job_id
left join (
SELECT
release_id,
CASE
WHEN COUNT(DISTINCT data_type) = 2 THEN '完整'
ELSE '不完整'
END AS SCSYGC_isComplete,
CASE
WHEN SUM(CASE WHEN eip_status != 1 THEN 1 ELSE 0 END) > 0 THEN '未上传'
ELSE '已上传'
END AS SCSYGC_isUpload
FROM
eip_prod_process
GROUP BY
release_id
) as e on a.release_sub_id=e.release_id
left join (
SELECT
release_id,
CASE
WHEN SUM(CASE WHEN push_status !=1 or push_status is null THEN 1 ELSE 0 END) > 0 THEN '未上传'
ELSE '已上传'
END AS dxwd_isUpload
FROM
eip_temperature
GROUP BY release_id
) as f on a.release_sub_id=f.release_id
left join (
SELECT a.release_sub_id,
case
when SUM(case WHEN eip_status !=1 or eip_status is null THEN 1 else 0 END) >0 then '未上传'
ELSE '已上传'
END AS CP_isUpload
FROM package_import a left join eip_product b on a.tower_id=b.tower_id
GROUP BY a.release_sub_id
) as g on a.release_sub_id=g.release_sub_id
[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode
BEGIN
BEGIN TRY
DROP TABLE #TempGWSN;
END TRY
BEGIN CATCH
-- 错误处理逻辑，这里可以不做任何事情，因为表可能不存在
-- 如果需要，可以通过 ERROR_NUMBER() 和 ERROR_MESSAGE() 获取错误信息
END CATCH
CREATE TABLE #TempGWSN (
id INT IDENTITY(1,1) PRIMARY KEY,
gw_sn VARCHAR(255)
);
INSERT INTO #TempGWSN (gw_sn)
VALUES
('4100161987'),
('4100161980');
SELECT
b.gw_sn,
a.gw_order_sn AS "采购订单号",
a.contract_sn AS "合同号",
a.item_id AS "行项目ID",
CASE WHEN a.original_bidder='1000014692' THEN '顺泰' when a.original_bidder='1000091718' THEN '瑜煌' END AS '中标主体',
a.product_name AS "塔类型",
a.project_name AS "工程名称",
a.short_name AS "工程简称",
a.delivery_date AS "合同交货期",
a.job_sn AS "生产单号",
a.release_sn AS "生产工单",
a.release_date AS "下达时间",
a.plan_start_date AS "工单-计划开始时间",
a.plan_finish_date AS "工单-计划结束时间",
a.actual_start_date AS "工单-实际开始时间",
a.actual_finish_date AS "工单-实际结束时间",
a.loftingPlanStartDate AS "理论计划开始时间",
a.delivery_date AS "理论计划完成时间",
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号",
case when a.GTXQ_pushed = 1 THEN '已上传' when a.GTXQ_pushed = 0 THEN '未传' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN '已上传' when a.bom_eip_status = 0 THEN '未传' END AS "参数规范-技术加工",
a.eip_pushed as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN '已上传' when a.bom_eip_status = 0 THEN '未传' END AS "原材料数据",
case when a.YCL_WZGL = 0 THEN '未关联' when a.YCL_WZGL = 1 THEN '未完全匹配' when a.YCL_WZGL = 2 THEN '完整关联' END AS "原材料数据关联",
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = '未生成' then '未生成' when a.BZ_exist is NULL then null ELSE '已生成' END as "成品数据-生成",
a.CP_isUpload as "成品数据-上传"
-- 计划开始时间 若没有，则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有，则取 contract.delivery_date,
-- 实际开始时间 若没有，则取 release.nested_material_date,
FROM OPENQUERY(FD_MES_DB,'
SELECT
a.*,
IF(b.min_eip = 1 AND b.max_eip = 1, 1, 0) AS GTXQ_pushed, -- 参数规范-杆塔详情
IF(c.min_push = 1 AND c.max_push = 1, 1, 0) AS YCL_pushed, -- 生产工单原材料上传
d.YCL_WZGL, -- 生产工单原材料关联性
e.SCSYGC_isComplete,
e.SCSYGC_isUpload,
IFNULL(f.dxwd_isUpload,"未生成") as dxwd_isUpload,
IFNULL(g.release_sub_id,"未生成") as BZ_exist, -- 包装是否存在
g.CP_isUpload -- 包装是否上传
FROM (
SELECT
a.gw_order_sn,
a.contract_sn,
a.item_id,
a.original_bidder,
a.product_name,
a.project_name,
a.short_name,
a.delivery_date,
b.id AS release_sub_id,
b.job_id,
c.job_sn,
b.release_sn,
e.release_date,
b.plan_start_date,
b.plan_finish_date,
b.actual_start_date,
b.actual_finish_date,
c.plan_start_date AS loftingPlanStartDate,
e.nested_material_date,
b.bom_eip_status, -- 参数规范-技术加工(bom)
SUBSTRING(tower_sns, 1, 4000) AS tower_sns ,
d.eip_pushed -- 参数规范-工程原材料
FROM
contract a
INNER JOIN release_sub b
ON a.id = b.contract_id
INNER JOIN lofting_job c
ON b.job_id = c.ID
INNER JOIN release_release_sub_temp s
ON s.release_sub_id = b.id
INNER JOIN `release` e
ON s.release_id = e.id
LEFT JOIN eip_contract_material d
ON b.id = d.release_id
WHERE
b.type = 1
AND a.is_gw_order = 1
AND a.delete_state = 0
AND a.gw_order_sn IN (
"4100161987",
"4100161980"
)
) AS a
LEFT JOIN (
SELECT
release_id,
MIN(eip_pushed) AS min_eip,
MAX(eip_pushed) AS max_eip
FROM eip_tower
GROUP BY release_id
) AS b ON a.release_sub_id = b.release_id
LEFT JOIN (
SELECT
release_id,
MIN(push_flag) AS min_push,
MAX(push_flag) AS max_push
FROM eip_machine_base_release
GROUP BY release_id
) AS c ON a.release_sub_id = c.release_id
left JOIN (
SELECT
job_id,
CASE
WHEN count(*) = 0 THEN 0 -- 还没生成
WHEN COUNT(eq_test_no)<COUNT(*) THEN 1 -- 没有完全匹配
WHEN COUNT(eq_test_no) = COUNT(*) THEN 2 -- 完全匹配
ELSE -1
END AS YCL_WZGL
FROM
eip_machine_base_release_material
GROUP BY
job_id
) as d on a.job_id=d.job_id
left join (
SELECT
release_id,
CASE
WHEN COUNT(DISTINCT data_type) = 2 THEN "完整"
ELSE "不完整"
END AS SCSYGC_isComplete,
CASE
WHEN SUM(CASE WHEN eip_status != 1 THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS SCSYGC_isUpload
FROM
eip_prod_process
GROUP BY
release_id
) as e on a.release_sub_id=e.release_id
left join (
SELECT
release_id,
CASE
WHEN SUM(CASE WHEN push_status !=1 or push_status is null THEN 1 ELSE 0 END) > 0 THEN "未上传"
ELSE "已上传"
END AS dxwd_isUpload
FROM
eip_temperature
GROUP BY release_id
) as f on a.release_sub_id=f.release_id
left join (
SELECT a.release_sub_id,
case
when SUM(case WHEN eip_status !=1 or eip_status is null THEN 1 else 0 END) >0 then "未上传"
ELSE "已上传"
END AS CP_isUpload
FROM package_import a left join eip_product b on a.tower_id=b.tower_id
GROUP BY a.release_sub_id
) as g on a.release_sub_id=g.release_sub_id
') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn
ORDER BY gw_sn
DROP TABLE #TempGWSN;
END
|
Archiver|手机版|小黑屋|cqutlab
( 渝ICP备15004556号 ) 
GMT+8, 2025-4-4 21:24 , Processed in 0.164659 second(s), 34 queries .
Powered by Discuz! X3.1
© 2001-2013 Comsenz Inc.
组图打开中，请稍候......
收藏