当我使用子查询时，当子查询跟随=，!=，<，<=，>，>=时，这是不允许的

Question

当我运行以下查询时：

SELECT 
    CASE 
       WHEN AT_EMPSCHEDULE.START1 <> '01/01/1900' 
            AND AT_EMPSCHEDULE.START2 <> '01/01/1900' 
            AND AT_EMPSCHEDULE.END2 <> '01/01/1900'
            AND AT_EMPSCHEDULE.LEAVECODE ='' 
            AND NOT EXISTS(SELECT PDATE 
                           FROM HR_PUBHOLIDAY 
                           WHERE PDATE=AT_EMPSCHEDULE.TRANDATE) 
            AND DATEPART(WEEKDAY,AT_EMPSCHEDULE.TRANDATE) <> 1 
          THEN 
             CAST((SELECT HIS_GENSALARYD.BASESALARY/HIS_GENSALARYD.WORKDAY 
                   FROM HIS_GENSALARYD 
                   WHERE (AT_EMPSCHEDULE.TRANDATE BETWEEN HIS_GENSALARYD.PAYFROM 
                                                  AND HIS_GENSALARYD.PAYTO 
                     AND HIS_GENSALARYD.EMPCODE = AT_EMPSCHEDULE.EMPCODE)) AS DECIMAL(10, 2)) 
          ELSE 0 
    END AS DAILYSALARY
FROM 
    HIS_GENSALARY 
INNER JOIN 
    AT_EMPSCHEDULE ON HIS_GENSALARY.EMPCODE = AT_EMPSCHEDULE.EMPCODE 
                   AND MONTH(TRANDATE) = HIS_GENSALARY.INMONTH 
                   AND YEAR(TRANDATE) = HIS_GENSALARY.INYEAR 
WHERE 
    HIS_GENSALARY.EMPCODE = HIS_GENSALARY.EMPCODE

我收到这样的信息：

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

Answer 1

问题在于：

(SELECT HIS_GENSALARYD.BASESALARY/HIS_GENSALARYD.WORKDAY 
 FROM HIS_GENSALARYD 
 WHERE AT_EMPSCHEDULE.TRANDATE BETWEEN HIS_GENSALARYD.PAYFROM 
     AND HIS_GENSALARYD.PAYTO 
 AND HIS_GENSALARYD.EMPCODE = AT_EMPSCHEDULE.EMPCODE)

你用它来表达。根据错误消息，它只能在您使用它的地方产生一个结果；即它不能返回超过一行，但是至少有一个调用返回了多个行。

若要强制它只返回一行，请向其应用聚合函数；一个明显的选择是max()

(SELECT MAX(HIS_GENSALARYD.BASESALARY/HIS_GENSALARYD.WORKDAY)
 FROM HIS_GENSALARYD 
 WHERE AT_EMPSCHEDULE.TRANDATE BETWEEN HIS_GENSALARYD.PAYFROM 
     AND HIS_GENSALARYD.PAYTO 
 AND HIS_GENSALARYD.EMPCODE = AT_EMPSCHEDULE.EMPCODE)