时间戳

首页 > 解决方案 > 子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的

sql - 子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的

问题描述

我收到此错误消息。我尝试了许多其他方法，但无法解决问题。如果有人能看到这个，我将不胜感激：

;WITH progression
AS
(
SELECT
    w.CustomerID,
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 8 THEN 1 ELSE 0 END)  > 0 THEN 'Yes' ELSE 'No' END AS [PG01],
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 11 THEN 1 ELSE 0 END) > 0 THEN 'Yes' ELSE 'No' END AS [PG03],
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 14 THEN 1 ELSE 0 END) > 0 THEN 'Yes' ELSE 'No' END AS [PG04],
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 17 THEN 1 ELSE 0 END) > 0 THEN 'Yes' ELSE 'No' END AS [PG05],
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 9 THEN 1 ELSE 0 END)  > 0 THEN 'Yes' ELSE 'No' END AS [in_work_review]
FROM
    ABC.dbo.tblWorkflow w
WHERE
    w.Deleted IS NULL
    AND
    w.workflowqueueid = 4
    AND
    w.workflowoutcomeid IN (8, 11, 14, 17, 9)
GROUP BY
    w.CustomerID
),

contact_data
AS
(
SELECT
    CustomerID, [Email], [Mobile], [Home]
FROM
    (
    SELECT 
        CustomerID,  
        CASE
            WHEN ContactTypeID = 1 THEN 'Home'
            WHEN ContactTypeID = 2 THEN 'Mobile'
            WHEN ContactTypeID = 5 THEN 'Email' 
        END AS ContactTypeDescription,
        ContactValue
    FROM ABC.dbo.tblCustomerContact
    WHERE ContactTypeID IN (1,2,5)
    ) base
PIVOT   
    (
    MAX(ContactValue) FOR ContactTypeDescription IN ([Home],[Mobile],[Email])
    ) pvt
),

appointment
AS
(
SELECT 
    n.[Key] AS CustomerID,
    MAX(CASE WHEN n.AppointmentStatusID IN (2,4) THEN n.ActionDate ELSE NULL END) AS [Last_Completed_Attended_Appointment],
    MAX(CASE WHEN n.AppointmentStatusID = 1 THEN n.ActionDate ELSE NULL END) AS [Next Pending Appointment]
FROM ABC.dbo.tblNote n
WHERE n.AppointmentTypeID = 6 AND n.AppointmentStatusID IN (1,2,4)
GROUP BY n.[Key]
)

SELECT
    m.Firstname + ' ' + m.Lastname AS [Manager],
    u.Firstname + ' ' + u.Lastname AS [Adviser],
    c.CustomerID,
    c.GivenName + ' ' + c.FamilyName AS [Customer Name],
    g.Gender,
    e.EthnicityName AS Ethnicity,
    com.CompanyName,
    NULL AS DeliverySite,
    d.[Name] AS District,
    css.ServiceStatus,
    contact_data.Email,
    uad.Line1 AS AddressLine1,
    uad.Line2 AS AddressLine2,
    uad.Line3 AS AddressLine3,
    uad.Postcode,
    uad.AlternativePostcode,
    uad.Town,
    ISNULL(contact_data.mobile, contact_data.home) AS Phone,
    c.StartDate,

    
    (SELECT MIN(wh.MGCReviewerModifiedDate) AS [ST01_Approved_Date]
        FROM ABC.dbo.tblWorkflow w 
        INNER JOIN ABC.dbo.tblWorkflowHistory wh 
        ON w.WorkflowID = wh.WorkflowID 
        WHERE wh.WorkflowQueueID = 4 AND wh.MGCReviewerModifiedDate IS NOT NULL
        AND wh.Deleted IS NULL AND w.WorkflowOutcomeID = 1 AND w.WorkflowQueueID = 4
        AND w.Deleted IS NULL AND w.CustomerID = c.CustomerID
        GROUP BY w.WorkflowID, w.CustomerID) AS ST01_Approved_Date,

    qual.LearningAimTitle,
    qual.LearningAimCompletionStatus,
    qual.LearningAimOutcomeStatus,
    mwp.WorkExperienceTitle ,
    mwp.WorkExperienceStartDate,
    mwp.WorkExperienceEndDate,
    mwp.learningaimcompletionstatus as [Work_Ex_CompletionStatus],
    mwp.learningaimoutcomestatus as [Work_Ex_OutcomeStatus],
    p.PG01 AS [PG01_Start(Job Start Approved/Claimed ?)],
    p.in_work_review AS [PG01_In_work_review(Job Outcome Approved/Claimed ?)],
    p.PG03 AS [PG03 – Education Approved / Claimed?],
    p.PG04 AS [PG04 – Apprenticeship Approved / Claimed?],
    p.PG05 AS [PG05– Apprenticeship Approved / Claimed?],
    a.Last_Completed_Attended_Appointment,
    a.[Next Pending Appointment],
    FLOOR(DATEDIFF(day, c.DateOfBirth, GETDATE()) / 365.25) as CustomerAge
FROM ABC.dbo.tblCustomer c
LEFT JOIN ABC.dbo.tblUser u ON c.OwnerID = u.UserID
LEFT JOIN ABC.dbo.tblUser m ON u.ManagerID = m.UserID
LEFT JOIN ABC.dbo.tlkpGender g ON c.GenderID = g.GenderID
LEFT JOIN ABC.dbo.tlkpEthnicity e ON c.EthnicityID = e.EthnicityID
LEFT JOIN ABC.dbo.tblCompany com ON c.CompanyId = com.[CompanyID]
LEFT JOIN ABC.dbo.tblCustomerAddress cad ON c.CustomerID = cad.CustomerID AND cad.EffectiveTo IS NULL
LEFT JOIN ABC.dbo.tblUKAddress uad ON uad.UKaddressID = cad.UKaddressID
LEFT JOIN PostcodeESyNCS.dbo.tblPostcode po ON uad.Postcode = po.Postcode
LEFT JOIN PostcodeESyNCS.dbo.tlkpDistrict d ON po.DistrictId = d.DistrictId
LEFT JOIN ABC.dbo.tlkpCustomerServiceStatus css ON c.CustomerServiceStatusID = css.CustomerServiceStatusID
LEFT JOIN progression p ON c.CustomerID = p.CustomerID
LEFT JOIN contact_data ON c.CustomerID = contact_data.CustomerID

OUTER APPLY
    (
    SELECT TOP 1 
        l.learningaimid,
        aim.LearningAimTitle,
        aim.isfullqualification,
        st.LearningAimCompletionStatus,
        aos.LearningAimOutcomeStatus 
    FROM ABC.dbo.tbllearning l 
    LEFT JOIN ABC.dbo.tlkplearningaim aim on aim.learningaimid = l.learningaimid 
    LEFT JOIN ABC.dbo.tlkplearningaimcompletionstatus st on st.learningaimcompletionstatusid = l.learningaimcompletionstatusid
    LEFT JOIN ABC.dbo.tlkpLearningAimOutcomeStatus aos on aos.learningaimoutcomestatusid = l.learningaimoutcomestatusid
    WHERE aim.learningaimtypeid = 1 AND l.customerid = c.customerid 
    ORDER BY l.StartDate DESC
    ) qual

OUTER APPLY 
    ( 
    SELECT TOP 1 
        l.learningaimid,
        REPLACE(aim.LearningAimTitle,',',' ') as WorkExperienceTitle,
        aim.isfullqualification,
        st.LearningAimCompletionStatus,
        outcome.LearningAimOutcomeStatus,
        l.StartDate as WorkExperienceStartDate,
        l.ActualEndDate as WorkExperienceEndDate
        FROM ABC.dbo.tbllearning l 
        LEFT JOIN ABC.dbo.tlkplearningaim aim ON aim.learningaimid=l.learningaimid 
        LEFT JOIN ABC.dbo.tlkplearningaimcompletionstatus st ON st.learningaimcompletionstatusid=l.learningaimcompletionstatusid
        LEFT JOIN ABC.dbo.tlkpLearningAimOutcomeStatus outcome ON outcome.learningaimoutcomestatusid= l.learningaimoutcomestatusid
        WHERE aim.LearningAimID IN (SELECT la.LearningAimID
                                            FROM ABC.dbo.tlkpLearningAim la 
                                            WHERE la.LearningAimTypeID = 2 
                                            and la.LearningAimTitle like '%Work%')              
              AND l.customerid = c.customerid 
            ORDER BY StartDate DESC      
        ) AS mwp
LEFT JOIN appointment a ON c.CustomerID = a.CustomerID
WHERE
    c.Deleted IS NULL

我收到以下错误：消息 512，级别 16，状态 1，第 2 行子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。警告：空值被聚合或其他 SET 操作消除。

标签： sqltsqlsubquerycommon-table-expression

解决方案

我看到这可能发生的唯一地方是ST01_Approved_Date：

(SELECT MIN(wh.MGCReviewerModifiedDate) AS [ST01_Approved_Date]
 FROM ABC.dbo.tblWorkflow w JOIN
      ABC.dbo.tblWorkflowHistory wh 
      ON w.WorkflowID = wh.WorkflowID 
 WHERE wh.WorkflowQueueID = 4 AND wh.MGCReviewerModifiedDate IS NOT NULL AND
       wh.Deleted IS NULL AND w.WorkflowOutcomeID = 1 AND w.WorkflowQueueID = 4 AND
      w.Deleted IS NULL AND w.CustomerID = c.CustomerID
  GROUP BY w.WorkflowID, w.CustomerID
 ) AS ST01_Approved_Date,

GROUP By由于这个原因，相关子查询不应该包含。我不知道逻辑是什么，但是w.WorkFlowId外部查询可能应该有一个条件，例如：

(SELECT MIN(wh.MGCReviewerModifiedDate) AS [ST01_Approved_Date]
 FROM ABC.dbo.tblWorkflow w JOIN
      ABC.dbo.tblWorkflowHistory wh 
      ON w.WorkflowID = wh.WorkflowID 
 WHERE wh.WorkflowQueueID = 4 AND wh.MGCReviewerModifiedDate IS NOT NULL AND
       wh.Deleted IS NULL AND w.WorkflowOutcomeID = 1 AND w.WorkflowQueueID = 4 AND
       w.Deleted IS NULL AND w.CustomerID = c.CustomerID AND
       w.workflowID = <outer query reference>.WorkflowId
 ) AS ST01_Approved_Date,

推荐阅读