小编典典

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

sql

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

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;`

阅读 42

收藏
2021-04-28

共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
2021-04-28