子查询返回了多个值。当子查询跟随=，！=，<，<=，>，>=时，不允许这样做，或者当子查询用作表达式时也不允许。

Question

我有一个存储过程，其中包含select * from book table的语句，使用子查询后我的查询语句是：

我有一个存储过程，其中包含select * from book table的语句，使用子查询后我的查询语句是：

USE [library]
GO

/****** Object:  StoredProcedure [dbo].[report_r_and_l]    Script Date: 04/17/2013 12:42:39 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[report_r_and_l]
@fdate date,
@tdate date,
@key varchar(1)
as

if(@key='r')

    select * 
    from dbo.books 
    where isbn =(select isbn from dbo.lending where (act between @fdate and @tdate) and (stat ='close'))

else if(@key='l')

    select * 
    from dbo.books 
    where isbn =(select isbn from dbo.lending where lended_date between @fdate and @tdate)

我知道子查询会将多个查询返回给主查询，但我不知道如何避免这个错误，有人能帮助我吗？

- Roshan

---

显然select isbn返回了多个值。您可以使用where isbn IN (select isbn ...。 - Hans Kesting

使用 select top 1 isbn ... 或 select max(isbn) ...。即使您知道只会返回一行，但 SQL Server 不知道，因为错误消息是基于命令的统计分析而不是实际数据。 - Olivier Jacot-Descombes

Answer 1

问题在于这两个查询都返回了多行：

select isbn from dbo.lending where (act between @fdate and @tdate) and (stat ='close')
select isbn from dbo.lending where lended_date between @fdate and @tdate

根据您期望的结果，您有两个选择。您可以将上述查询替换为保证只返回一个行的内容（例如使用SELECT TOP 1），或者您可以切换=为IN并返回多个行，如下：

select * from dbo.books where isbn IN (select isbn from dbo.lending where (act between @fdate and @tdate) and (stat ='close'))

- Dan Puzey

---

我明白了，谢谢。我想问一件事情，只有一个在书籍表中引用所选的ISBN，我想从借阅表中选择ISBN，从借阅表中选择lend_no，从学生表中选择sudent_name，引用借阅表中的索引号，如何从多个表中检索多行？ - Roshan

不，原问题的模式中有“lended_date”。实际定义的模式始终比良好的语法更重要，无论你想多么追求语言准确性。 - Dan Puzey

这确实是我见过的最简洁易懂的IN描述，谢谢！ - MDGREEE

Answer 2

使用In代替=

 select * from dbo.books
 where isbn in (select isbn from dbo.lending 
                where act between @fdate and @tdate
                and stat ='close'
               )

或者您可以使用 Exists

SELECT t1.*,t2.*
FROM  books   t1 
WHERE  EXISTS ( SELECT * FROM dbo.lending t2 WHERE t1.isbn = t2.isbn and
                t2.act between @fdate and @tdate and t2.stat ='close' )

- praveen

---

SET atRate1 = (SELECT t1.,t2. FROM RateCode t1 WHERE EXISTS (SELECT * FROM RateAmount t2 WHERE t1.RateCode = t2.RateCode AND t1.CountryCode = @CCode AND t1.ModuleCode = @MCode)) 我在 t2.* 上出现了错误。 - Ashish-BeJovial

Answer 3

您可以如下使用IN运算符。

select * from dbo.books where isbn IN
(select isbn from dbo.lending where lended_date between @fdate and @tdate)

- bvr

Answer 4

Using operator 'IN' helps

USE [library]
GO

/****** Object:  StoredProcedure [dbo].[report_r_and_l]    Script Date: 04/17/2013 12:42:39 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[report_r_and_l]
@fdate date,
@tdate date,
@key varchar(1)
as

if(@key='r')

    select * 
    from dbo.books 
    where isbn IN (select isbn from dbo.lending where (act between @fdate and @tdate) and (stat ='close'))

else if(@key='l')

    select * 
    from dbo.books 
    where isbn IN (select isbn from dbo.lending where lended_date between @fdate and @tdate)

- Samantha