alter table dbo.HJGL_WeldJoint add WeldJointPoint nvarchar(300) go alter view dbo.View_HJGL_WeldJoint as SELECT jot.[ProjectId] --项目Id , pipe.[UnitWorkId] --单位工程Id , uw.UnitWorkName --单位工程 , pipe.[UnitId] --单位Id , unit.UnitName --单位 , jot.[PipelineCode] --管线号 , pipe.[SingleNumber] --单线图号 , pipe.[SingleName] --图纸名称 , pipe.[MediumId] --介质Id , medium.MediumCode , medium.MediumName --介质名称 , pipe.[PipingClassId] --管道等级Id , pipingClass.PipingClassCode --管道等级 , pipe.[DetectionRateId] --探伤比例Id , dr.DetectionRateCode --探伤比例（例如5，10，20） , pipe.DetectionType --探伤类型Id(可能多个，中间用|分隔) , STUFF((SELECT ',' + DetectionTypeCode FROM Base_DetectionType WHERE CHARINDEX('|' + LTRIM(DetectionTypeId) + '|', '|' + pipe.DetectionType + '|') > 0 FOR XML PATH('')), 1, 1, '') AS DetectionTypeStr --探伤类型（可能多个，中间用,分隔） , pipe.DesignTemperature --设计温度℃ , pipe.DesignPress --设计压力Mpa(g) , pipe.TestMedium --压力试验介质Id , tm.MediumName AS TestMediumName --压力试验介质 , pipe.TestPressure --压力试验压力Mpa(g) , pipe.PressurePipingClassId --压力管道级别Id , ppc.PressurePipingClassCode --压力管道级别 , pipe.PipeLenth --管线长度(m) , pipe.LeakMedium --泄露性试验介质Id , tm2.MediumName AS LeakMediumName --泄露性试验介质 , pipe.LeakPressure --泄露性试验压力Mpa(g) , pipe.PCMedium --吹洗要求Id , pm.PurgeMethodCode --吹洗要求 , pipe.VacuumPressure --真空试验压力Kpa(a) , pipe.MaterialId --材质Id , mat.MaterialCode AS PipeMaterialCode --材质 , pipe.Remark AS PipeRemark --备注 , pipe.FlowingSection as FlowingSection --流水段 , jot.[WeldJointId] --焊口Id , jot.[WeldJointCode] --焊口号 , jot.[Material1Id] --材质1Id , mat1.MaterialCode AS Material1Code --材质1 , jot.[Material2Id] --材质2Id , mat2.MaterialCode AS Material2Code --材质2 , jot.[Dia] --外径 , jot.DNDia --DN公称直径 , jot.[Size] --达因 , jot.[Thickness] --壁厚 , jot.[Specification] --规格 , jot.[WeldTypeId] --焊缝类型Id , WeldType.WeldTypeCode --焊缝类型 , jot.[DetectionTypeId] --检测类型Id , ndt.DetectionTypeCode --检测类型 , jot.[Components1Id] --组件1号Id , com1.ComponentsCode AS ComponentsCode1 --组件1号 , jot.[Components2Id] --组件2号Id , com2.ComponentsCode AS ComponentsCode2 --组件2号 , jot.DesignIsHotProess --是否热处理 , CASE jot.DesignIsHotProess WHEN 1 THEN '是' ELSE '否' END AS DesignIsHotProessStr --是否热处理 , jot.[WeldingMethodId] --焊接方法Id , WeldingMethod.WeldingMethodCode --焊接方法 , jot.WPQId --WPS的Id , wps.WPQCode --WPS编号 , jot.MatchableWPQ --可匹配WPQ , jot.[GrooveTypeId] --坡口类型Id , GrooveType.GrooveTypeCode --坡口类型 , jot.[WeldingRod] --焊条Id , rod.ConsumablesCode AS WeldingRodCode --焊条 , jot.[WeldingWire] --焊丝Id , wire.ConsumablesCode AS WeldingWireCode --焊丝 , jot.PreTemperature --预热温度 , jot.[JointAttribute] --焊口属性 , jot.TwoJointType --二次焊口新增类型 , pipe.PipeArea --工厂预制/现场安装 , daily.WeldingDate as WeldingDateD , CONVERT(VARCHAR(100), daily.WeldingDate, 23) AS WeldingDate --焊接日期 , BackingWelder.WelderCode AS BackingWelderCode --打底焊工 , CoverWelder.WelderCode AS CoverWelderCode --盖面焊工 , CoverWelderTeamGroup.TeamGroupName as CoverWelderTeamGroupName ----盖面焊工班组 , BackingWelderTeamGroup.TeamGroupName as BackingWelderTeamGroupName --打底焊工班组 , (CASE (SELECT COUNT(*) FROM [dbo].[HJGL_HotProess_TrustItem] hpti WHERE hpti.WeldJointId = jot.WeldJointId) WHEN 0 THEN '' ELSE (SELECT TOP 1 ISNULL(ReportNo, '') FROM [dbo].[HJGL_HotProess_Trust] hpt LEFT JOIN [dbo].[HJGL_HotProess_TrustItem] hpti ON hpti.HotProessTrustId = hpt.HotProessTrustId WHERE hpti.WeldJointId = jot.WeldJointId) END) AS HotProessReportNo --热处理报告编号 , (CASE (SELECT TOP 1 IsCompleted FROM [dbo].[HJGL_HotProess_TrustItem] hpti WHERE hpti.WeldJointId = jot.WeldJointId) WHEN 1 THEN '完成' WHEN 0 THEN '未完成' ELSE '未热处理' END) AS HotProessResult --热处理检测结果 , (CASE (SELECT COUNT(*) FROM [dbo].HJGL_Hard_TrustItem hti WHERE hti.WeldJointId = jot.WeldJointId) WHEN 0 THEN '' ELSE (SELECT TOP 1 ISNULL(InspectionNum, '') FROM [dbo].HJGL_Hard_Trust ht LEFT JOIN [dbo].HJGL_Hard_TrustItem hti ON hti.HardTrustID = ht.HardTrustID WHERE hti.WeldJointId = jot.WeldJointId) END) AS HardReportNo --硬度报告编号 , (CASE (SELECT TOP 1 IsPass FROM [dbo].HJGL_Hard_TrustItem hti WHERE hti.WeldJointId = jot.WeldJointId) WHEN 1 THEN '合格' WHEN 0 THEN '不合格' ELSE '待检测' END) AS HardResult --硬度检测结果 , (CASE (SELECT COUNT(*) FROM [dbo].HJGL_Batch_BatchTrustItem bti WHERE bti.WeldJointId = jot.WeldJointId) WHEN 0 THEN '' ELSE (SELECT TOP 1 TrustBatchCode FROM [dbo].HJGL_Batch_BatchTrust bt LEFT JOIN [dbo].HJGL_Batch_BatchTrustItem bti ON bti.TrustBatchId = bt.TrustBatchId WHERE bti.WeldJointId = jot.WeldJointId) END) AS TrustBatchCode --委托单编号 , (CASE (SELECT COUNT(*) FROM [dbo].HJGL_Batch_NDEItem ndei LEFT JOIN [dbo].HJGL_Batch_BatchTrustItem bti ON bti.TrustBatchItemId = ndei.TrustBatchItemId WHERE bti.WeldJointId = jot.WeldJointId) WHEN 0 THEN '' ELSE (SELECT TOP 1 ISNULL(NDECode, '') FROM [dbo].HJGL_Batch_NDE nde LEFT JOIN [dbo].HJGL_Batch_NDEItem ndei ON ndei.NDEID = nde.NDEID LEFT JOIN [dbo].HJGL_Batch_BatchTrustItem bti ON bti.TrustBatchItemId = ndei.TrustBatchItemId WHERE bti.WeldJointId = jot.WeldJointId) END) AS NDECode --检测单编号 , (CASE (SELECT TOP 1 CheckResult FROM [dbo].HJGL_Batch_NDEItem ndei LEFT JOIN [dbo].HJGL_Batch_BatchTrustItem bti ON bti.TrustBatchItemId = ndei.TrustBatchItemId WHERE bti.WeldJointId = jot.WeldJointId) WHEN '1' THEN '合格' WHEN '2' THEN '不合格' ELSE '' END) AS CheckResult --检测结果 , jot.PipelineId , jot.[JointArea] , jot.[WeldingLocationId] , jot.[HeartNo1] , jot.[HeartNo2] , jot.[IsHotProess] , jot.[WeldingDailyId] , jot.[BackingWelderId] , jot.[CoverWelderId] , jot.WeldingMode , jot.Remark , jot.IsTwoJoint , jot.SubmitMan , jot.AuditMan , jot.AuditMan2 , jot.AuditDate , jot.AuditDate2 , jot.WeldJointPoint , WeldingLocation.WeldingLocationCode , CASE WHEN jot.WeldingDailyId IS NULL THEN '否' ELSE '是' END AS IsWelding , CASE WHEN jot.WeldingDailyId IS NULL THEN '未完成' ELSE '已完成' END AS IsWeldOK , CASE WHEN jot.IsHotProess = 1 THEN '是' ELSE '否' END AS IsHotProessStr , BackingWelder.PersonName AS BackingWelderName , CoverWelder.PersonName AS CoverWelderName , (CASE WHEN CoverWelder.WelderCode IS NOT NULL AND BackingWelder.WelderCode IS NOT NULL THEN CoverWelder.WelderCode + '/' + BackingWelder.WelderCode ELSE (ISNULL(CoverWelder.WelderCode, '') + ISNULL(BackingWelder.WelderCode, '')) END) AS WelderCode , --焊工 (CASE WHEN mat1.MaterialCode IS NOT NULL AND mat2.MaterialCode IS NOT NULL THEN mat1.MaterialCode + '/' + mat2.MaterialCode ELSE (ISNULL(mat1.MaterialCode, '') + ISNULL(mat2.MaterialCode, '')) END) AS MaterialCode , --材质 daily.WeldingDailyCode , pointItem.PointBatchId , point.PointBatchCode , (CASE WHEN pointItem.PointState = '1' THEN '已点口' WHEN pointItem.PointState = '2' THEN '已扩透' ELSE '' END) AS IsPoint FROM [dbo].[HJGL_WeldJoint] jot LEFT JOIN dbo.HJGL_Pipeline pipe ON pipe.PipelineId = jot.PipelineId LEFT JOIN dbo.WBS_UnitWork uw ON uw.UnitWorkId = pipe.UnitWorkId LEFT JOIN dbo.HJGL_WeldingDaily AS daily ON daily.WeldingDailyId = jot.WeldingDailyId LEFT JOIN dbo.Base_Unit unit ON unit.UnitId = daily.UnitId LEFT JOIN Base_WeldType AS WeldType ON WeldType.WeldTypeId = jot.WeldTypeId LEFT JOIN dbo.Base_DetectionRate dr ON dr.DetectionRateId = pipe.DetectionRateId LEFT JOIN Base_TestMedium tm ON tm.TestMediumId = pipe.TestMedium LEFT JOIN Base_TestMedium tm2 ON tm2.TestMediumId = pipe.LeakMedium LEFT JOIN Base_PurgeMethod pm ON pm.PurgeMethodId = pipe.PCMedium LEFT JOIN Base_PressurePipingClass ppc ON ppc.PressurePipingClassId = pipe.PressurePipingClassId LEFT JOIN Base_Material AS mat ON mat.MaterialId = pipe.MaterialId LEFT JOIN Base_Material AS mat1 ON mat1.MaterialId = jot.Material1Id LEFT JOIN Base_Material AS mat2 ON mat2.MaterialId = jot.Material2Id LEFT JOIN Base_WeldingMethod AS WeldingMethod ON WeldingMethod.WeldingMethodId = jot.WeldingMethodId LEFT JOIN Base_WeldingLocation AS WeldingLocation ON WeldingLocation.WeldingLocationId = jot.WeldingLocationId LEFT JOIN Base_Consumables AS wire ON wire.ConsumablesId = jot.WeldingWire LEFT JOIN Base_Consumables AS rod ON rod.ConsumablesId = jot.WeldingRod LEFT JOIN Base_GrooveType AS GrooveType ON GrooveType.GrooveTypeId = jot.GrooveTypeId LEFT JOIN Base_Components AS com1 ON com1.ComponentsId = jot.Components1Id LEFT JOIN Base_Components AS com2 ON com2.ComponentsId = jot.Components2Id LEFT JOIN SitePerson_Person AS BackingWelder ON BackingWelder.PersonId = jot.BackingWelderId and BackingWelder.ProjectId = jot.ProjectId LEFT JOIN SitePerson_Person AS CoverWelder ON CoverWelder.PersonId = jot.CoverWelderId and CoverWelder.ProjectId = jot.ProjectId LEFT JOIN Base_PipingClass AS pipingClass ON pipingClass.PipingClassId = pipe.PipingClassId LEFT JOIN dbo.Base_Medium medium ON medium.MediumId = pipe.MediumId LEFT JOIN dbo.Base_DetectionType ndt ON ndt.DetectionTypeId = jot.DetectionTypeId LEFT JOIN dbo.WPQ_WPQList wps ON wps.WPQId = jot.WPQId LEFT JOIN dbo.HJGL_Batch_PointBatchItem pointItem ON pointItem.WeldJointId = jot.WeldJointId LEFT JOIN dbo.HJGL_Batch_PointBatch point ON point.PointBatchId = pointItem.PointBatchId LEFT JOIN dbo.ProjectData_TeamGroup AS CoverWelderTeamGroup on CoverWelderTeamGroup.TeamGroupId = jot.CoverWelderTeamGroupId and CoverWelderTeamGroup.ProjectId = jot.ProjectId LEFT JOIN dbo.ProjectData_TeamGroup AS BackingWelderTeamGroup on BackingWelderTeamGroup.TeamGroupId = jot.BackingWelderTeamGroupId and BackingWelderTeamGroup.ProjectId = jot.ProjectId go