时间戳

首页 > 解决方案 > this Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

sql - this Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

问题描述

i wrote a query to get total count of devices for user. but when i run the query for single user it working fine. when i keep mutiple emailids its not working getting message subquery should not contain multiple. I unable to change below query to accept multiple values. Can anyone help on this.

select distinct UserDisplayName,[UserName/Mail],SerialNumber,LastSeen as Lastcheckin,Model, EnrollmentStatus,(select count(*) from Intunedevices where [UserName/Mail] in 
('aaa-xyz.onsite-caller@xyz.com','gbidi.i.habab@gsk.com') group by UserDisplayName) as EMSCOUNT 
 from Intunedevices where [UserName/Mail] in ('aaa-xyz.onsite-caller@xyz.com',
'gbidi.i.habab@xyz.com')

标签： sqlsql-serversql-server-2008rdbms

解决方案

问题是group by在您的子查询中返回多行。它可能应该是相关的 - 我不完全确定，因为in标准略有不同，这是您的样本数据中的错字吗？

尝试以下操作：

select distinct 
    UserDisplayName, 
    [UserName/Mail], 
    SerialNumber, 
    LastSeen as Lastcheckin,Model, 
    EnrollmentStatus,
    (select count(*) from Intunedevices d where d.[UserName/Mail] = id.[UserName/Mail] and d.UserDisplayName = id.UserDisplayName) as EMSCOUNT 
 from Intunedevices id
 where id.[UserName/Mail] in ('aaa-xyz.onsite-caller@xyz.com','gbidi.i.habab@xyz.com')

推荐阅读