sql-server - 子查询返回超过 1 个值。当子查询后面有 =、!=、<、<=、>、>= 或子查询用作表达式时，不允许这样做

Question

我有一个存储过程，从图书表中选择*，使用子查询我的查询是

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)

我知道子查询会向主查询返回多个查询，但我不知道如何避免此错误，有人可以帮助我吗？

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'))