|
IF OBJECT_ID('tempdb..#LotZZTime') IS NOT NULL
DROP TABLE #LotZZTime;
IF OBJECT_ID('tempdb..#OpJZTime') IS NOT NULL
DROP TABLE #OpJZTime;
IF OBJECT_ID('tempdb..#LotZhcTime') IS NOT NULL
DROP TABLE #LotZhcTime;
IF OBJECT_ID('tempdb..#OpZhcTime') IS NOT NULL
DROP TABLE #OpZhcTime;
-- 创建临时表来存储进站时间
CREATE TABLE #LotZZTime (
LotNo NVARCHAR(50), -- 假设LotNo的数据类型是NVARCHAR(50)，根据实际情况调整
lot_zz_time DATETIME
);
INSERT INTO #LotZZTime (LotNo, lot_zz_time)
SELECT LotNo, Min(EventTime) AS lot_zz_time
FROM TBLWIPCONT_PARTIALIN WITH (NOLOCK)
-- WHERE areano = 'DNKE-JJG' -- 如果需要，取消注释这行来添加过滤条件
GROUP BY LotNo;
-- 创建临时表来存储每个批维度当前工序的进站时间
CREATE TABLE #OpJZTime (
LotNo NVARCHAR(100),
opno NVARCHAR(50), -- opno
opno_jz_time DATETIME
);
INSERT INTO #OpJZTime (LotNo, opno, opno_jz_time)
SELECT LotNo, opno, Min(EventTime) AS opno_jz_time
FROM TBLWIPCONT_PARTIALIN WITH (NOLOCK)
-- WHERE areano = 'DNKE-JJG' -- 如果需要，取消注释这行来添加过滤条件
GROUP BY LotNo, opno;
-- 创建临时表来存储每个批维度的最后出站时间
CREATE TABLE #LotZhcTime (
LotNo NVARCHAR(50),
lot_zhc_time DATETIME
);
INSERT INTO #LotZhcTime (LotNo, lot_zhc_time)
SELECT LotNo, Max(EventTime) AS lot_zhc_time
FROM TBLWIPCONT_PARTIALOUT WITH (NOLOCK)
-- WHERE areano = 'DNKE-JJG' -- 如果需要，取消注释这行来添加过滤条件
GROUP BY LotNo;
-- 创建临时表来存储每个批维度当前工序的最后出站时间
CREATE TABLE #OpZhcTime (
LotNo NVARCHAR(100),
opno NVARCHAR(50),
op_zhc_time DATETIME
);
INSERT INTO #OpZhcTime (LotNo, opno, op_zhc_time)
SELECT LotNo, opno, Max(EventTime) AS op_zhc_time
FROM TBLWIPCONT_PARTIALOUT WITH (NOLOCK)
-- WHERE areano = 'DNKE-JJG' -- 如果需要，取消注释这行来添加过滤条件
GROUP BY LotNo, opno;
-- 主查询，使用临时表来引用中间结果
SELECT a7.LOTNO,
t8.lot_zz_time,
b1.opno_jz_time,
b2.op_zhc_time,
b4.lot_zhc_time
FROM TBLWIPLOTSTATE a7
LEFT JOIN #LotZZTime t8 ON a7.LOTNO = t8.LotNo
LEFT JOIN #OpJZTime b1 ON a7.LOTNO = b1.LotNo AND a7.OPNO = b1.opno
LEFT JOIN #LotZhcTime b4 ON a7.LOTNO = b4.LotNo
LEFT JOIN #OpZhcTime b2 ON a7.LOTNO = b2.LotNo AND a7.OPNO = b2.opno
这段程序在我本地sqlsever数据库执行没有问题，放到帆软数据集去执行就会报错 | 
