--好多字查询啊! 先帮整理,各位看看
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY 生产号 ASC ) AS 序号 ,
*
FROM ( (SELECT tr.packageserialnum 生产号 ,
tr.packagecode 包编号 ,
tr.packagecode 包名称 ,
tr.boilersequencecode 锅次 ,
tr.tryforoffice 发放科室 ,
tr.sterilizetime 灭菌日期 ,
( SELECT pa.patientname
FROM patient AS pa
WHERE tr.forpatient = pa.patientcode
) 使用患者 ,
tr.operatetime 使用时间 ,
( SELECT pe.username
FROM personnel AS pe ,
custompackage AS cp
WHERE cp.cleanoutperson = pe.personcode
AND cp.packagelist LIKE '%'
+ tr.packageserialnum + '%'
) 清洗人 ,
( SELECT pe.username
FROM personnel AS pe ,
custompackage AS cp
WHERE cp.checkoutperson = pe.personcode
AND cp.packagelist LIKE '%'
+ tr.packageserialnum + '%'
) 核对人 ,
( SELECT pe.username
FROM personnel AS pe ,
custompackage AS cp
WHERE cp.packperson = pe.personcode
AND cp.packagelist LIKE '%'
+ tr.packageserialnum + '%'
) 打包人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.sterilizeperson = pe.personcode
) 灭菌人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.qualityperson = pe.personcode
) 质检人 ,
CASE WHEN ( SELECT pe.username
FROM personnel AS pe
WHERE tr.provideperson = pe.personcode
) IS NULL
THEN LTRIM(tr.provideperson)
ELSE ( SELECT pe.username
FROM personnel AS pe
WHERE tr.provideperson = pe.personcode
)
END AS 发放人 ,
tr.invalidatetime 失效日期
FROM packagetrackrecord AS tr
WHERE tr.sterilizeperson IS NOT NULL
AND packagecode NOT IN ( SELECT
packagecode
FROM packageinfo ))
UNION ALL
( SELECT tr.packageserialnum 生产号 ,
tr.packagecode 包编号 ,
( SELECT pt.typename
FROM packtype AS pt
JOIN packageinfo AS pi ON ( pi.typecode = pt.typecode )
WHERE pi.packagecode = tr.packagecode
) AS 包名称 ,
tr.boilersequencecode 锅次 ,
tr.tryforoffice 发放科室 ,
tr.sterilizetime 灭菌日期 ,
( SELECT pa.patientname
FROM patient AS pa
WHERE tr.forpatient = pa.patientcode
) 使用患者 ,
tr.operatetime 使用时间 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.cleanoutperson = pe.personcode
) 清洗人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.checkoutperson = pe.personcode
) 核对人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.packperson = pe.personcode
) 打包人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.sterilizeperson = pe.personcode
) 灭菌人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.qualityperson = pe.personcode
) 质检人 ,
CASE WHEN ( SELECT pe.username
FROM personnel AS pe
WHERE tr.provideperson = pe.personcode
) IS NULL
THEN LTRIM(tr.provideperson)
ELSE ( SELECT pe.username
FROM personnel AS pe
WHERE tr.provideperson = pe.personcode
)
END AS 发放人 ,
tr.invalidatetime 失效日期
FROM packagetrackrecord AS tr
WHERE tr.sterilizeperson IS NOT NULL
AND packagecode IN ( SELECT packagecode
FROM packageinfo )
)
) AS DATA
) AS data
WHERE 序号 BETWEEN 1 AND 25
ORDER BY 序号
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY 生产号 ASC ) AS 序号 ,
*
FROM ( (SELECT tr.packageserialnum 生产号 ,
tr.packagecode 包编号 ,
tr.packagecode 包名称 ,
tr.boilersequencecode 锅次 ,
tr.tryforoffice 发放科室 ,
tr.sterilizetime 灭菌日期 ,
( SELECT pa.patientname
FROM patient AS pa
WHERE tr.forpatient = pa.patientcode
) 使用患者 ,
tr.operatetime 使用时间 ,
( SELECT pe.username
FROM personnel AS pe ,
custompackage AS cp
WHERE cp.cleanoutperson = pe.personcode
AND cp.packagelist LIKE '%'
+ tr.packageserialnum + '%'
) 清洗人 ,
( SELECT pe.username
FROM personnel AS pe ,
custompackage AS cp
WHERE cp.checkoutperson = pe.personcode
AND cp.packagelist LIKE '%'
+ tr.packageserialnum + '%'
) 核对人 ,
( SELECT pe.username
FROM personnel AS pe ,
custompackage AS cp
WHERE cp.packperson = pe.personcode
AND cp.packagelist LIKE '%'
+ tr.packageserialnum + '%'
) 打包人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.sterilizeperson = pe.personcode
) 灭菌人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.qualityperson = pe.personcode
) 质检人 ,
CASE WHEN ( SELECT pe.username
FROM personnel AS pe
WHERE tr.provideperson = pe.personcode
) IS NULL
THEN LTRIM(tr.provideperson)
ELSE ( SELECT pe.username
FROM personnel AS pe
WHERE tr.provideperson = pe.personcode
)
END AS 发放人 ,
tr.invalidatetime 失效日期
FROM packagetrackrecord AS tr
WHERE tr.sterilizeperson IS NOT NULL
AND packagecode NOT IN ( SELECT
packagecode
FROM packageinfo ))
UNION ALL
( SELECT tr.packageserialnum 生产号 ,
tr.packagecode 包编号 ,
( SELECT pt.typename
FROM packtype AS pt
JOIN packageinfo AS pi ON ( pi.typecode = pt.typecode )
WHERE pi.packagecode = tr.packagecode
) AS 包名称 ,
tr.boilersequencecode 锅次 ,
tr.tryforoffice 发放科室 ,
tr.sterilizetime 灭菌日期 ,
( SELECT pa.patientname
FROM patient AS pa
WHERE tr.forpatient = pa.patientcode
) 使用患者 ,
tr.operatetime 使用时间 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.cleanoutperson = pe.personcode
) 清洗人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.checkoutperson = pe.personcode
) 核对人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.packperson = pe.personcode
) 打包人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.sterilizeperson = pe.personcode
) 灭菌人 ,
( SELECT pe.username
FROM personnel AS pe
WHERE tr.qualityperson = pe.personcode
) 质检人 ,
CASE WHEN ( SELECT pe.username
FROM personnel AS pe
WHERE tr.provideperson = pe.personcode
) IS NULL
THEN LTRIM(tr.provideperson)
ELSE ( SELECT pe.username
FROM personnel AS pe
WHERE tr.provideperson = pe.personcode
)
END AS 发放人 ,
tr.invalidatetime 失效日期
FROM packagetrackrecord AS tr
WHERE tr.sterilizeperson IS NOT NULL
AND packagecode IN ( SELECT packagecode
FROM packageinfo )
)
) AS DATA
) AS data
WHERE 序号 BETWEEN 1 AND 25
ORDER BY 序号
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货