sql - 使用 EXISTS 引入子查询

Question

我有一个很长的查询，在我执行时抛出异常。查询:

SELECT HostID,HostName,RackID,HostTypeID,DomainName,RackNumberOfHeightUnits,RackStartHeightUnits 
FROM tHosts, tDomains   
WHERE tHosts.DomainID=tDomains.DomainID AND (RackID IN ( SELECT tRacks.Name,tRacks.RackID,tRacks.SiteID,tRacks.Description,NumberOfHeightUnits   
                                                         FROM tDomains, tSites, tRacks   
                                                         WHERE   tDomains.AccountID= tSites.AccountID    
                                                         AND tSites.SiteID = tRacks.SiteID    
                                                         AND tSites.SiteID = 2  
                                                         AND tDomains.AccountID=1 ) 
AND  SiteID IN (SELECT SiteID FROM tSites WHERE SiteID IN (SELECT SiteID FROM tSites WHERE AccountID=1)))AND AccountID=1

这里是为查询完成的:

SELECT tRacks.Name,tRacks.RackID,tRacks.SiteID,tRacks.Description,NumberOfHeightUnits 
                                                         FROM tDomains, tSites, tRacks  
                                                         WHERE tDomains.AccountID= tSites.AccountID  
                                                         AND tSites.SiteID = tRacks.SiteID  
                                                         AND tSites.SiteID = 2  
                                                         AND tDomains.AccountID=1 

**错误:**当子查询没有用EXISTS引入时，select列表中只能指定一个表达式。
提前致谢。

Answer 1

使用 IN 您必须返回一列，即您要与之比较的列:

改变这个

 ...AND (RackID IN ( SELECT tRacks.Name,tRacks.RackID,tRacks.SiteID,tRacks.Description,NumberOfHeightUnits   
                     FROM tDomains, tSites, tRacks ...

对此:

... AND (RackID IN ( SELECT tRacks.RackID FROM tDomains, tSites, tRacks ...

在这个地方“外面”不会使用其他列

但是 - 老实说 - 整个查询看起来 - 嗯 - 可以改进......