请帮助此子查询返回的值超过1.错误

Question

我的查询是

SELECT ACCOUNTNUM,
       NAME,
       ADDRESS,
       PHONE,
       TELEFAX,
       INVOICEACCOUNT,
       CUSTGROUP,
       PAYMTERMID,
       CURRENCY,
       DIMENSION,
       CELLULARPHONE,
       STATISTICSGROUP,
       PAYMMODE,
       NAMEALIAS,
       CONTACTPERSONID,
       STREET,
       PARTYID,
       SEGMENTID,
       TAXGROUP,
       DATAAREAID,
       ISNULL (
                 (SELECT PERCENT1 AS DiscCount
                  FROM PRICEDISCTABLE
                  WHERE (DATAAREAID = CUSTTABLE.DATAAREAID)
                    AND (ACCOUNTRELATION = CUSTTABLE.ACCOUNTNUM)), 0) / 100 AS DiscCount,
  (SELECT NAME
   FROM CONTACTPERSON
   WHERE (DATAAREAID = CUSTTABLE.DATAAREAID)
     AND (CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM)
     AND (CONTACTPERSONID = CUSTTABLE.CONTACTPERSONID)) AS ContactName,
  (SELECT PHONE
   FROM CONTACTPERSON AS CONTACTPERSON_1
   WHERE (DATAAREAID = CUSTTABLE.DATAAREAID)
     AND (CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM)
     AND (CONTACTPERSONID = CUSTTABLE.CONTACTPERSONID)) AS ContactPhone,
              ISNULL (
                        (SELECT STATGROUPNAME
                         FROM CUSTSTATISTICSGROUP
                         WHERE (DATAAREAID = CUSTTABLE.DATAAREAID)
                           AND (CUSTSTATISTICSGROUP = CUSTTABLE.STATISTICSGROUP)), 0) AS StatisticsName
FROM CUSTTABLE
WHERE (CUSTGROUP = N'DOM')
  AND (DATAAREAID = N'OTN')
  AND (STATISTICSGROUP = N'S010');

我获得错误"子查询返回的值超过1…"。

请帮帮我。

Answer 1

该错误是由于您的一个子查询为ISNULL函数返回了多行。ISNULL函数用于处理一个值，而不是一组值（如Max）。

运行每个子查询以检出数据，并对其进行更改以只返回一个值。如果您获得多个具有相同值的记录，请使用DISTINCT；如果您只想从一个集合中获得一个随机值，请使用Top1，如果您希望获得集合的最大值（或最小值），请使用MAX。您可能会发现，您可能需要向子查询添加更多条件。

SELECT PERCENT1 AS DiscCount
                  FROM PRICEDISCTABLE
                  WHERE (DATAAREAID = CUSTTABLE.DATAAREAID)
                    AND (ACCOUNTRELATION = CUSTTABLE.ACCOUNTNUM)

https://msdn.microsoft.com/en-us/library/ms184325（v=sql.105）.aspx