您好，我正在尝试执行以下查询，但它给我一个我无法理解的错误。请任何人都可以帮助我。提前致谢 [英] Hello, I Am Trying To Execute Below Query But Its Giving Me An Error Which I Unable To Understand. Please Can Anyone Help Me. Thanks In Advance

问题描述

drop table #merchant_list

SELECT distinct a.merchant_number，dev_serial as [terminal serial]

into #merchant_list

FROM [BI] 。[dbo]。[DEVICE_PROFILE_SNPSHT] a

内连接[BI]。[dbo]。[MERCHANT_PROFILE] b

on a.merchant_number = b.merchant_number
其中a.year_month = 201506

- （813652行受影响）



插入[报告] 。[REF＃20570_txn_count]

选择top 1 b.merchant_number，period ='Jun15'，

gross_txn_amt = SUM（TRANSACTION_TYPE IN时的情况（'PU'，'AD '，'RC'）然后isnull（AMOUNT，0）否则0结束），

gross_txn_cnt = SUM（TRANSACTION_TYPE IN（'PU'，'AD'，'RC'）然后1的情况0结束）

来自mdw。[mdw_FIN_EVTS_STMT_Jun15]。[dbo]。[POS_DETAIL_TRANSACTION] A

内连接#merchant_list B

on a。 merchant_number = b.merchant_number

和a.device_char =（从#m中选择不同的[终端序列] erchant_list）

GROUP BY b.merchant_number



ERROR ::子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式

解决方案

<时，不允许这样做blockquote>如前所述，问题出在子查询中，它返回多行。由于您使用的等式比较只能比较一个值，因此这是禁止的。



如果您需要根据#merchant_list表中的终端序列获取所有行，你应该将相等比较改为IN。这样就可以对值列表进行比较，如下所示：

 ... 
 和 a.device_char  IN （选择  distinct  [terminal serial] 来自 #merchant_list）
 ... 

如果要求只返回子查询中的一行，则应添加适当的条件以确保返回正确的行。

更改

 和 a.device_char =（ select   distinct  [terminal serial] 来自 #merchant_list）

到

 和 a.device_char =（选择  t op   1  [terminal serial] 来自 #merchant_list）

drop table #merchant_list
SELECT distinct a.merchant_number, dev_serial as [terminal serial]
into #merchant_list
FROM [BI].[dbo].[DEVICE_PROFILE_SNPSHT] a
inner join [BI].[dbo].[MERCHANT_PROFILE] b
on a.merchant_number = b.merchant_number
where a.year_month = 201506
--(813652 row(s) affected)

insert into [Reports].[REF#20570_txn_count]
select top 1 b.merchant_number, period = 'Jun15',
gross_txn_amt = SUM(case when TRANSACTION_TYPE IN ('PU', 'AD', 'RC') then isnull(AMOUNT,0) else 0 end),
gross_txn_cnt = SUM(case when TRANSACTION_TYPE IN ('PU', 'AD', 'RC') then 1 else 0 end)
from mdw.[mdw_FIN_EVTS_STMT_Jun15].[dbo].[POS_DETAIL_TRANSACTION] A
inner join #merchant_list B
on a.merchant_number = b.merchant_number
and a.device_char = (select distinct [terminal serial] from #merchant_list)
GROUP BY b.merchant_number

ERROR::Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

解决方案

As already mentioned the problem is in the sub query which returns more than one row. Since you use equality comparison which can compare only a single value this is prohibited.

If you need to fetch all rows based on the terminal serial in the #merchant_list table, the you should change the equality comparison to IN. This way the comparison is done for a list of values, Something like the following:

...
and a.device_char IN (select distinct [terminal serial] from #merchant_list)
...

If the requirement is to return only one row from the subquery then you should add proper conditions to ensure that the correct row is returned.

change

and a.device_char = (select distinct [terminal serial] from #merchant_list)

to

and a.device_char = (select top 1 [terminal serial] from #merchant_list)

这篇关于您好，我正在尝试执行以下查询，但它给我一个我无法理解的错误。请任何人都可以帮助我。提前致谢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！