子查询返回超过 1 个值.当子查询跟在 =,!=,<,<=,>,>= 之后或当子查询用作表达式时，这是不允许的 [英] Subquery returned more than 1 value.This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression

问题描述

我有一个存储过程 select * from book table ，使用子查询我的查询是

I have a stored procedure that select * from book table , using sub query my query is

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)

我知道子查询是向主查询返回多个查询，但我不知道如何避免这个错误，谁能帮助我?

I know sub query is return more than one query to main query , But i don't know how to avoid this error, can any one help me ?

推荐答案

问题是这两个查询都返回了不止一行:

The problem is that these two queries are each returning more than one row:

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

您有两种选择，具体取决于您想要的结果.您可以将上述查询替换为保证返回 single 行的内容(例如，通过使用 SELECT TOP 1)，或者您可以切换 = 到 IN 并返回多行，如下所示:

You have two choices, depending on your desired outcome. You can either replace the above queries with something that's guaranteed to return a single row (for example, by using SELECT TOP 1), OR you can switch your = to IN and return multiple rows, like this:

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

这篇关于子查询返回超过 1 个值.当子查询跟在 =,!=,<,<=,>,>= 之后或当子查询用作表达式时，这是不允许的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！