sql - SQL：WHERE 检查多列（其中只有一列必须不为空）

Question

好的，

所以我有一个报告如下：

WITH   StudentCTEm ([Person Code], [Course Instance Maths], [Course Occurrence Maths], [Course Title], [ULN], [Main Aim])
AS     (SELECT [Person Code],
               [Course Instance],
               [Course Occurrence],
               [Course Title],
               [ULN],
               [Main Aim]
        FROM   EBSWarehouse.dbo.StudentEnrolment cte
        WHERE  cte.[Progress Status] = 'A' AND cte.[Course Instance] LIKE '%REGM%' AND cte.[Course Occurrence] LIKE '1920%'),
StudentCTEe ([Person Code], [Course Instance English], [Course Occurrence English], [Course Title], [ULN], [Main Aim])
AS     (SELECT [Person Code],
               [Course Instance],
               [Course Occurrence],
               [Course Title],
               [ULN],
               [Main Aim]
        FROM   EBSWarehouse.dbo.StudentEnrolment cte
        WHERE  cte.[Progress Status] = 'A' AND cte.[Course Instance] LIKE '%REGE%' AND cte.[Course Occurrence] LIKE '1920%'),
StudentCTEIT ([Person Code], [Course Instance IT], [Course Occurrence IT], [Course Title], [ULN], [Main Aim])
AS     (SELECT [Person Code],
               [Course Instance],
               [Course Occurrence],
               [Course Title],
               [ULN],
               [Main Aim]
        FROM   EBSWarehouse.dbo.StudentEnrolment cte
        WHERE  cte.[Progress Status] = 'A' AND cte.[Course Instance] LIKE '%REGIT%' AND cte.[Course Occurrence] LIKE '1920%'),
pcsCTEfsM ([Learning Aim Ref Maths], [Learning Aim Desc Maths], [PICS ID], [Funding End])
AS     (SELECT [Learning Aim Ref],
               [Learning Aim Desc],
               [PICS ID],
               [Funding End]
        FROM   dbo.ILRComponentAim cte
        WHERE [Learning Aim Desc] like '%Functional Skills qualification in Mathematics%'),
pcsCTEfsE ([Learning Aim Ref English], [Learning Aim Desc English], [PICS ID], [Funding End])
AS     (SELECT [Learning Aim Ref],
               [Learning Aim Desc],
               [PICS ID],
               [Funding End]
        FROM   dbo.ILRComponentAim cte
        WHERE [Learning Aim Desc] like '%Functional Skills qualification in English%'),
pcsCTEfsIT ([Learning Aim Ref IT], [Learning Aim Desc IT], [PICS ID], [Funding End])
AS     (SELECT [Learning Aim Ref],
               [Learning Aim Desc],
               [PICS ID],
               [Funding End]
        FROM   dbo.ILRComponentAim cte
        WHERE [Learning Aim Desc] like '%Functional Skills qualification in Information%')


SELECT DISTINCT
s.[Person Code],
s.ULN,
s.Forenames,
s.Surname,
s.[Episode Start],
s.[Episode Exp End],
s.[Person Code],
icm.[Learning Aim Ref Maths],
ICm.[Learning Aim Desc Maths],
Sem.[Course Instance Maths],
Sem.[Course Occurrence Maths],
SEm.[Course Title],
ice.[Learning Aim Ref English],
ICe.[Learning Aim Desc English],
See.[Course Instance English],
See.[Course Occurrence English],
SEe.[Course Title],
icit.[Learning Aim Ref IT],
icit.[Learning Aim Desc IT],
Seit.[Course Instance IT],
Seit.[Course Occurrence IT],
SEIT.[Course Title]






FROM dbo.Student s
LEFT JOIN           dbo.ILRProgAim AS p ON p.[PICS ID] = S.[PICS ID]
LEFT JOIN       pcsCTEfsM icm ON icm.[PICS ID] = s.[PICS ID]
LEFT JOIN       pcsCTEfsE ice ON ice.[PICS ID] = s.[PICS ID]
LEFT JOIN       pcsCTEfsIT icit ON icit.[PICS ID] = s.[PICS ID]
LEFT JOIN       StudentCTEe see ON see.[ULN] = s.[ULN]
LEFT JOIN       StudentCTEm sem ON sem.[ULN] = s.[ULN]
LEFT JOIN       StudentCTEIT seit ON seit.[ULN] = s.[ULN]
INNER JOIN EBSWarehouse.dbo.StudentEnrolment sen ON sen.[ULN] = s.ULN  

WHERE s.[Episode End] is null AND s.[Status] = 'L' AND [Learning Aim End] is null AND icm.[Funding End] is null AND sen.[Main Aim] <> 1 
ORDER BY s.[Person Code] ASC

一些记录在以下方面全面显示为空：

    SELECT 
    icm.[Learning Aim Ref Maths],
    ICm.[Learning Aim Desc Maths],
    Sem.[Course Instance Maths],
    Sem.[Course Occurrence Maths],
    SEm.[Course Title],
    ice.[Learning Aim Ref English],
    ICe.[Learning Aim Desc English],
    See.[Course Instance English],
    See.[Course Occurrence English],
    SEe.[Course Title],
    icit.[Learning Aim Ref IT],
    icit.[Learning Aim Desc IT],
    Seit.[Course Instance IT],
    Seit.[Course Occurrence IT],
    SEIT.[Course Title]
    ...

这很好。

如何在我的WHERE子句中仅显示上述至少一列不为空的记录

谢谢

我知道这是肮脏的报告写作，但这是一项需要做的快速工作

它告诉我添加更多细节：

如果您想要上下文 ，那么这基本上是跨各种表和另一个数据库进行映射，以准确计算谁在使用他们的 PICS（内部软件）学徒进行功能技能课程，以及我们在内部学生管理系统上拥有哪些信息。

我应该添加预期结果与实际结果相同，对于 GDPR，我无法真正发布结果集）但实际上我需要做的就是忽略报告中所有课程中所有“null”的记录选项（基本上有些人已经退出或完成了他们的 FS 部分但没有完成他们的 WBL 课程，因此作为本报告的一部分出现，我尝试使用完成状态列省略记录，但有些人被认为仍处于活动状态并已离开很久以前 - 所以那是不行的，所以我唯一的选择就是duuuurty我正在整理的这个小视图

标签： sqlsql-serverssms

Answer 1

合并？

WHERE   COALESCE    (
                        icm.[Learning Aim Ref Maths],
                        ICm.[Learning Aim Desc Maths],
                        Sem.[Course Instance Maths],
                        Sem.[Course Occurrence Maths],
                        SEm.[Course Title],
                        ice.[Learning Aim Ref English],
                        ICe.[Learning Aim Desc English],
                        See.[Course Instance English],
                        See.[Course Occurrence English],
                        SEe.[Course Title],
                        icit.[Learning Aim Ref IT],
                        icit.[Learning Aim Desc IT],
                        Seit.[Course Instance IT],
                        Seit.[Course Occurrence IT],
                        SEIT.[Course Title]
                    ) IS NOT NULL