此子查询最多可以返回一条记录。（错误3354）

Question

您好我的查询得到此错误帮助我恢复它

SELECT CompanyId, CompanyName, RegistrationNumber,
  (select CompanyAddress from RPT_Company_Address where 
   RPT_Company_Address.CompanyId=Company.CompanyId) AS CompanyAddress, 
  MobileNumber, FaxNumber, CompanyEmail, CompanyWebsite, VatTinNumber
FROM Company;`

Answer 1

您的RPT_Company_Address表似乎具有给定公司的多个地址。如果这不可能，则应尝试更正数据并修改架构以防止发生这种情况。

另一方面，如果可以有多个地址，则必须确定查询应如何处理它们：

1）您是否要多次列出同一公司行-每个地址一次？如果是这样，请使用INNER JOIN将它们全部返回：

SELECT Company.CompanyId, CompanyName, RegistrationNumber, CompanyAddress, ...
FROM Company
INNER JOIN RPT_Company_Address RCA ON RCA.CompanyId = Company.CompanyId

2）如果只需要第一个匹配地址，请在与每个公司相对应的第一个匹配地址上进行子查询：

SELECT Company.CompanyId, CompanyName, RegistrationNumber, CompanyAddress, ...
FROM Company
INNER JOIN
(
    SELECT CompanyId, ROW_NUMBER() OVER (ORDER BY 1 PARTITION BY CompanyId) AS Num
    FROM RPT_Company_Address
) Addresses
    ON Addresses.ComapnyId = Company.CompanyId  
WHERE Num = 1

3）如果您有其他方法来标识所需的“主要”地址，请添加WHERE具有该条件的子句：

SELECT Company.CompanyId, CompanyName, RegistrationNumber, CompanyAddress, ...
FROM Company
INNER JOIN RPT_Company_Address RCA ON RCA.CompanyId = Company.CompanyId
WHERE RCA.PrimaryAddress = 1