子查询返回的值超过1。这是不允许的 [英] Subquery returned more than 1 value. This is not permitted

问题描述

select  Row_Number() OVER(order by orderNumber ) as SN, IIF( orderStatus = 'Completed','Unused', orderStatus) as VoucherStatus,cardAmount as Amount,card_pin as VoucherPin, orderNumber as BatchNo,cardpin_sn as SerialNumber,   
   (select dname from users where username=(select depositor from Bankdeposit where cardpin=orderdetails.card_pin)) 
  +' ('+  (select saveaseid from users where username=(select depositor from Bankdeposit where cardpin=orderdetails.card_pin))+')'  as UsedBy,
  (select transactionDate from Bankdeposit where cardpin=orderdetails.card_pin) as UsedDate
   from  orderdetails where orderby=@in_uname
	  and orderStatus='completed' order by orderNumber

我尝试过：



seached google但没有响应

What I have tried:

seached google but no response

推荐答案

多个子查询是错误的方法。

Multiple subqueries are the wrong way to do this.

username=(select depositor from Bankdeposit where cardpin=orderdetails.card_pin)

错误意味着您获得了多个结果。如果你得到的行数相同，那么使用TOP 1或DISTINCT。



我从不使用子查询，我会使用CTE加入。我有一篇关于CT有关CTE的文章

The error means you're getting more than one result. If you're getting many rows with the same result, use TOP 1 or DISTINCT.

I'd never use subqueries, I'd use a CTE to join to. I have an article on CP about CTEs

Quote：

子查询返回的值超过1。这是不允许的

Subquery returned more than 1 value. This is not permitted

相当简单，你的选择在其内部嵌入一些选择。

每个内部选择多返回1只有1行只有1个字段，因此1个值。

您的子查询中的1个返回0或超过1行。

检查这些行中的行数：

Rather simple, your select embeds some select inside itself.
Each inner select much return 1 and only 1 row with only 1 field, thus 1 value.
1 of your subqueries return 0 or more than 1 row.
Check number of rows in those:

select depositor from Bankdeposit where cardpin=orderdetails.card_pin

select dname from users where username=(select depositor from Bankdeposit where cardpin=orderdetails.card_pin)

select saveaseid from users where username=(select depositor from Bankdeposit where cardpin=orderdetails.card_pin)

这是工作

选择Row_Number（）OVER（orderNumber排序）为SN，IIF（orderStatus ='Completed'，'Unused' ，orderStatus）作为VoucherStatus，cardAmount作为金额，card_pin作为VoucherPin，orderNumber作为BatchNo，cardpin_sn作为SerialNumber，

（从用户中选择dname，其中username =（从Bankdeposit中选择存款人，其中cardpin = orderdetails.card_pin） ）

+'（'+（从用户名=用户选择saveaseid（从Bankdeposit选择存款人，其中cardpin = orderdetails.card_pin））+'）'作为UsedBy，

（从Bankdeposit中选择transactionDate，其中cardpin = orderdetails.card_pin）作为UsedDate

来自orderdetails其中orderby = @ in_uname

和orderStatus ='used'order by orderNumber

this is working
select Row_Number() OVER(order by orderNumber ) as SN, IIF( orderStatus = 'Completed','Unused', orderStatus) as VoucherStatus,cardAmount as Amount,card_pin as VoucherPin, orderNumber as BatchNo,cardpin_sn as SerialNumber,
(select dname from users where username=(select depositor from Bankdeposit where cardpin=orderdetails.card_pin))
+' ('+ (select saveaseid from users where username=(select depositor from Bankdeposit where cardpin=orderdetails.card_pin))+')' as UsedBy,
(select transactionDate from Bankdeposit where cardpin=orderdetails.card_pin) as UsedDate
from orderdetails where orderby=@in_uname
and orderStatus='used' order by orderNumber

这篇关于子查询返回的值超过1。这是不允许的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！