SQL拆分InformationText列去重去NULL解决Msg 512报错

AI 小北

2026-8-27

问题说明

需要从vCourseInformationDetail表的InformationText字段拆分两类独立数据列：

字段CourseInformationTypeID值为'1'时，InformationText存储WebContent（网站内容）
字段CourseInformationTypeID值为'8'时，InformationText存储EntryRequirements（入学要求）

原有写法存在两个核心问题：

写的两个提取内容的标量子查询未关联外层课程ID，子查询返回多行时触发Msg 512错误
直接内联关联vCourseInformationDetail原表，单门课程对应该表两行不同类型的记录，导致查询结果出现重复行：一行仅入学要求字段有值、网站内容为NULL，另一行仅网站内容有值、入学要求为NULL。
目标是实现单条课程记录同时返回两类非空内容，无重复行、无NULL值。

原有SQL代码如下：

DECLARE 
    @DAY VARCHAR (10) = ' Day(s)',
    @Week VARCHAR (10) = ' Week(s)',
    @Year VARCHAR (10) = ' Year(s)'

SELECT DISTINCT
    COALESCE (O.QualID,'') QualID,
    CASE WHEN LEN(O.Code) <= 9 THEN  LEFT (O.Code,5)
    ELSE LEFT (O.Code,8)
    END AS CourseCode,
    CASE WHEN LEN(O.Code) <= 9 THEN SUBSTRING(O.Code,7,LEN(O.Code)-6)
    ELSE SUBSTRING(O.Code,9,LEN(O.Code)-6) 
    END AS OccurenceCode,
    CASE 
        WHEN (O.Name LIKE '%ACCESS%') THEN 'Adult - Access to Higher Education'
        WHEN (O.Name LIKE '%ESOL%') THEN 'ESOL'
        WHEN (O.Code LIKE '%-F2%') THEN 'Community Learning'
        WHEN (CL.Name LIKE '%AAT%') OR (CL.Name LIKE '%BUSINESS%') THEN  'Business & Accounting'
        WHEN (CL.Name LIKE '%Beauty%') OR (CL.Name LIKE '%Hair%') THEN 'Hair & Beauty'
        WHEN (CL.Name LIKE '%Functional Skills%') OR (CL.Name LIKE '%GCSE%') THEN 'Maths & English'
        WHEN (CL.Name LIKE '%Photography%') OR (CL.Name LIKE '%Graphics%') OR (CL.Name LIKE '%Media%') THEN 'Digital Media'
        WHEN (CL.Name LIKE '%Sports%') THEN 'Sports'
        WHEN (CL.Name LIKE '%Public Services%') THEN 'Public Services'
        WHEN (CL.Name LIKE '%ICT%') THEN 'ICT Technology & Computing'
        WHEN (CL.Name LIKE '%Animal Care%') THEN 'Animal Studies'
        WHEN (CL.Name LIKE '%ICT%') THEN 'ICT Technology & Computing'
        WHEN (CL.Name LIKE '%Childcare%' OR CL.Name LIKE '%Teaching%') THEN 'Teacher Education'
        WHEN (CL.Name LIKE '%Hospitality & Catering%') OR (CL.Name LIKE '%Bus%')THEN 'Business & Accounting'  
        WHEN (CL.Name LIKE '%Engineering%') OR (CL.Name LIKE '%Enviromental%') OR (CL.Name LIKE '%Sustainability%') OR (CL.Name LIKE '%LB Engineering Centre%') THEN 'Engineering' 
        WHEN (CL.Name LIKE '%Construction%') THEN 'Construction' 
        WHEN (CL.Name LIKE '%Motor Vehicle%') THEN 'Motor Vehicle' 
        WHEN (CL.Name LIKE '%Science%') THEN 'Science'
        WHEN (CL.Name LIKE '%Pathways%') THEN 'Pathways' 
        WHEN (S.Description LIKE '%Distance Learning%') THEN 'Distance & Online Learning'
        WHEN (S.Description LIKE '%Community%') OR (S.Description LIKE '%School%') OR (S.Description LIKE '%Farm%') OR (S.Description LIKE '%Stopsley%') THEN 'Community Learning'
        WHEN (O.Name LIKE '%PCE%') OR (O.Name LIKE '%PGCE%') THEN 'Teacher Education'
        WHEN (O.Name LIKE '%Child%') THEN 'ChildCare'
        WHEN (O.Name LIKE '%Health%' OR O.Name LIKE 'Disability') THEN 'Health & Social Care'
        WHEN (O.Name LIKE '%Leadership%' OR O.Name LIKE '%Management%') THEN 'Leadership & Management'
        WHEN (O.Name LIKE '%SSS%') THEN 'SSS Birmingham & Manchester Based'
        WHEN (O.Name LIKE '%Business%') THEN 'Business & Accounting'
        WHEN (O.Name LIKE '%Computing%') THEN 'Technology & Computing'
    END AS SubjectTab,   
    vCLI.Level1Code LearningArea,
    COALESCE (LA.NOTIONAL_NVQ_LEVEL_CODE, '') OfferingLevel, 
    O.Name AS OfferingName,
    (SELECT vCID.InformationText WHERE vCID.CourseInformationTypeID = '1') AS WebContent,
    (SELECT vCID.InformationText WHERE vCID.CourseInformationTypeID = '8') AS EntryRequirements ,
    vCID.CourseInformationTypeID,
    AB.Name AwardingBody,
    FORMAT(O.StartDate,'dd/MM/yyyy') AS StartDate ,
    FORMAT(O.EndDate,'dd/MM/yyyy') AS EndDate,
    CASE 
        WHEN (O.NumberOfWeeks BETWEEN '35' AND '69') THEN CONCAT(1,@Year)
        WHEN (O.NumberOfWeeks BETWEEN '70' AND '155') THEN CONCAT(2,@Year)
        WHEN (O.NumberOfWeeks >= '156') THEN CONCAT(3,@Year)
        WHEN (O.NumberOfWeeks BETWEEN '0' AND '34') THEN CONCAT(o.NumberOfWeeks,@Week)
        WHEN DATEDIFF(DAY, O.StartDate, O.EndDate) BETWEEN '0' AND '7' THEN CONCAT(DATEDIFF(DAY, O.StartDate, O.EndDate),@DAY)
    END AS Duration, 
    S.Description AS Venue,
    COALESCE (FMAF.DLF_Fee_LR_1618,0) AS Fee1618,FMAF.DLF_Fee_LR_Adult AS 'Fee19+','£' + COALESCE (CAST (vOS.Fee1 AS char(136)),'0.00') FullCost,
    CASE 
        WHEN LEN (O.Code) <=9 THEN '16-18'
        WHEN (LA.NOTIONAL_NVQ_LEVEL_CODE >= '4') AND (vCLI.Level1Code = 'L06') THEN 'HE'
        ELSE 'Adult'
    END AS Category, 
    O.Code AS OfferingCode,
    O.UserDefined1 AS Link
FROM Offering O 
LEFT JOIN AwardingBody AB ON O.AwardingBodyID = AB.AwardingBodyID
LEFT JOIN vOfferingStats vOS ON O.OfferingID = vOS.OfferingID
LEFT JOIN vPR_FundMethodlogy_AssumedFee FMAF ON O.OfferingID = FMAF.OfferingID
LEFT JOIN Site S ON O.SiteID = S.SiteID 
INNER JOIN vCourseInformationDetail vCID ON O.OfferingID = vCID.OfferingID
LEFT JOIN CollegeLevel CL ON O.SID = CL.SID 
LEFT JOIN vCollegeLevel_Info vCLI ON O.SID = vCLI.SID 
LEFT JOIN Learning_Aim LA ON O.QualID = LA.LEARNING_AIM_REF 
WHERE 
    WebSiteAvailabilityID = '2' 
    AND GETDATE() <= DATEADD(DAY,42, O.StartDate) 
    AND O.Name NOT LIKE '%Cancelled%' 
    AND O.Name NOT LIKE '%School Link%' 
    AND O.Name NOT LIKE '%Amazon%' 
    AND O.Name NOT LIKE '%Apprentice%'

修复方法

核心修改逻辑：先对vCourseInformationDetail表按OfferingID做条件聚合，把每个课程的两类内容提前合并为单行，再和主表关联，从根源避免行拆分、子查询多行报错问题。

删除SELECT列表中两个错误的标量子查询、多余的vCID.CourseInformationTypeID字段
将原来直接关联vCourseInformationDetail原表的写法，替换为聚合后的子查询，通过MAX(CASE...)分别提取两类内容，保证每个OfferingID仅返回一行
去掉不必要的DISTINCT，聚合后已经不存在重复行，不需要额外去重

修复后完整SQL：

DECLARE 
    @DAY VARCHAR (10) = ' Day(s)',
    @Week VARCHAR (10) = ' Week(s)',
    @Year VARCHAR (10) = ' Year(s)'

SELECT
    COALESCE (O.QualID,'') QualID,
    CASE WHEN LEN(O.Code) <= 9 THEN  LEFT (O.Code,5)
    ELSE LEFT (O.Code,8)
    END AS CourseCode,
    CASE WHEN LEN(O.Code) <= 9 THEN SUBSTRING(O.Code,7,LEN(O.Code)-6)
    ELSE SUBSTRING(O.Code,9,LEN(O.Code)-6) 
    END AS OccurenceCode,
    CASE 
        WHEN (O.Name LIKE '%ACCESS%') THEN 'Adult - Access to Higher Education'
        WHEN (O.Name LIKE '%ESOL%') THEN 'ESOL'
        WHEN (O.Code LIKE '%-F2%') THEN 'Community Learning'
        WHEN (CL.Name LIKE '%AAT%') OR (CL.Name LIKE '%BUSINESS%') THEN  'Business & Accounting'
        WHEN (CL.Name LIKE '%Beauty%') OR (CL.Name LIKE '%Hair%') THEN 'Hair & Beauty'
        WHEN (CL.Name LIKE '%Functional Skills%') OR (CL.Name LIKE '%GCSE%') THEN 'Maths & English'
        WHEN (CL.Name LIKE '%Photography%') OR (CL.Name LIKE '%Graphics%') OR (CL.Name LIKE '%Media%') THEN 'Digital Media'
        WHEN (CL.Name LIKE '%Sports%') THEN 'Sports'
        WHEN (CL.Name LIKE '%Public Services%') THEN 'Public Services'
        WHEN (CL.Name LIKE '%ICT%') THEN 'ICT Technology & Computing'
        WHEN (CL.Name LIKE '%Animal Care%') THEN 'Animal Studies'
        WHEN (CL.Name LIKE '%ICT%') THEN 'ICT Technology & Computing'
        WHEN (CL.Name LIKE '%Childcare%' OR CL.Name LIKE '%Teaching%') THEN 'Teacher Education'
        WHEN (CL.Name LIKE '%Hospitality & Catering%') OR (CL.Name LIKE '%Bus%')THEN 'Business & Accounting'  
        WHEN (CL.Name LIKE '%Engineering%') OR (CL.Name LIKE '%Enviromental%') OR (CL.Name LIKE '%Sustainability%') OR (CL.Name LIKE '%LB Engineering Centre%') THEN 'Engineering' 
        WHEN (CL.Name LIKE '%Construction%') THEN 'Construction' 
        WHEN (CL.Name LIKE '%Motor Vehicle%') THEN 'Motor Vehicle' 
        WHEN (CL.Name LIKE '%Science%') THEN 'Science'
        WHEN (CL.Name LIKE '%Pathways%') THEN 'Pathways' 
        WHEN (S.Description LIKE '%Distance Learning%') THEN 'Distance & Online Learning'
        WHEN (S.Description LIKE '%Community%') OR (S.Description LIKE '%School%') OR (S.Description LIKE '%Farm%') OR (S.Description LIKE '%Stopsley%') THEN 'Community Learning'
        WHEN (O.Name LIKE '%PCE%') OR (O.Name LIKE '%PGCE%') THEN 'Teacher Education'
        WHEN (O.Name LIKE '%Child%') THEN 'ChildCare'
        WHEN (O.Name LIKE '%Health%' OR O.Name LIKE 'Disability') THEN 'Health & Social Care'
        WHEN (O.Name LIKE '%Leadership%' OR O.Name LIKE '%Management%') THEN 'Leadership & Management'
        WHEN (O.Name LIKE '%SSS%') THEN 'SSS Birmingham & Manchester Based'
        WHEN (O.Name LIKE '%Business%') THEN 'Business & Accounting'
        WHEN (O.Name LIKE '%Computing%') THEN 'Technology & Computing'
    END AS SubjectTab,   
    vCLI.Level1Code LearningArea,
    COALESCE (LA.NOTIONAL_NVQ_LEVEL_CODE, '') OfferingLevel, 
    O.Name AS OfferingName,
    vCID.WebContent,
    vCID.EntryRequirements,
    AB.Name AwardingBody,
    FORMAT(O.StartDate,'dd/MM/yyyy') AS StartDate ,
    FORMAT(O.EndDate,'dd/MM/yyyy') AS EndDate,
    CASE 
        WHEN (O.NumberOfWeeks BETWEEN '35' AND '69') THEN CONCAT(1,@Year)
        WHEN (O.NumberOfWeeks BETWEEN '70' AND '155') THEN CONCAT(2,@Year)
        WHEN (O.NumberOfWeeks >= '156') THEN CONCAT(3,@Year)
        WHEN (O.NumberOfWeeks BETWEEN '0' AND '34') THEN CONCAT(o.NumberOfWeeks,@Week)
        WHEN DATEDIFF(DAY, O.StartDate, O.EndDate) BETWEEN '0' AND '7' THEN CONCAT(DATEDIFF(DAY, O.StartDate, O.EndDate),@DAY)
    END AS Duration, 
    S.Description AS Venue,
    COALESCE (FMAF.DLF_Fee_LR_1618,0) AS Fee1618,
    FMAF.DLF_Fee_LR_Adult AS 'Fee19+',
    '£' + COALESCE (CAST (vOS.Fee1 AS char(136)),'0.00') FullCost,
    CASE 
        WHEN LEN (O.Code) <=9 THEN '16-18'
        WHEN (LA.NOTIONAL_NVQ_LEVEL_CODE >= '4') AND (vCLI.Level1Code = 'L06') THEN 'HE'
        ELSE 'Adult'
    END AS Category, 
    O.Code AS OfferingCode,
    O.UserDefined1 AS Link
FROM Offering O 
LEFT JOIN AwardingBody AB ON O.AwardingBodyID = AB.AwardingBodyID
LEFT JOIN vOfferingStats vOS ON O.OfferingID = vOS.OfferingID
LEFT JOIN vPR_FundMethodlogy_AssumedFee FMAF ON O.OfferingID = FMAF.OfferingID
LEFT JOIN Site S ON O.SiteID = S.SiteID 
-- 替换原直接关联vCID的写法，提前聚合为单行
INNER JOIN (
    SELECT 
        OfferingID,
        MAX(CASE WHEN CourseInformationTypeID = '1' THEN InformationText END) AS WebContent,
        MAX(CASE WHEN CourseInformationTypeID = '8' THEN InformationText END) AS EntryRequirements
    FROM vCourseInformationDetail
    WHERE CourseInformationTypeID IN ('1','8')
    GROUP BY OfferingID
) vCID ON O.OfferingID = vCID.OfferingID
LEFT JOIN CollegeLevel CL ON O.SID = CL.SID 
LEFT JOIN vCollegeLevel_Info vCLI ON O.SID = vCLI.SID 
LEFT JOIN Learning_Aim LA ON O.QualID = LA.LEARNING_AIM_REF 
WHERE 
    WebSiteAvailabilityID = '2' 
    AND GETDATE() <= DATEADD(DAY,42, O.StartDate) 
    AND O.Name NOT LIKE '%Cancelled%' 
    AND O.Name NOT LIKE '%School Link%' 
    AND O.Name NOT LIKE '%Amazon%' 
    AND O.Name NOT LIKE '%Apprentice%'

如果存在部分课程缺失某类内容的情况，可以在两个内容字段外层套COALESCE函数设置默认值，避免返回NULL。

内容的提问来源于stack exchange，提问作者Anthony Kamau

SQL拆分InformationText列 去重去NULL解决Msg 512报错

SQL拆分InformationText列去重去NULL解决Msg 512报错