当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式

[mms89757]

if exists(select * from sysobjects where name='Result' )
 drop table Result

select LineNum as 线路,SIM as 号码 into Result  from UV_MsgInbox t1 
where t1.MsgArrivedTime >= dateadd(minute,-5,getdate())
	 and MsgTitle='外接电源断电，启动内置电源' 
	          and not exists(select * from UV_MsgInbox t2
                      where t2.Sender = t1.SIM
                       and t2.MsgArrivedTime >= dateadd(minute,-5,getdate())
                       and t2.MsgTitle ='恢复正常供电')
  group by  t1.SIM,t1.LineNum 
select 线路  from Result  group by 线路 having count(DISTINCT 号码)>=2

if exists(select 线路  from Result  group by 线路 having count(DISTINCT 号码)>=2)
  begin


	declare @receiver varchar(500)
	set @receiver='13737740287,15878359061,15812405921'
	if exists (select * from UV_MsgInbox t1 where  t1.MsgType=1 and t1.MsgArrivedTime >= dateadd(minute,-5,getdate())  
			and t1.sender in (select 号码  from Result group by 号码 having count(DISTINCT 号码)>=2))--后半括号中子查询不对

子查询想要的是select 线路，号码 from Result group by 线路，号码 having count(DISTINCT 号码)>=2这条sql执行结果中的号码

怎么写才对呢

[claro]

if exists(select 线路  from Result  group by 线路 having count(DISTINCT 号码)>=2)
begin
declare @receiver varchar(500)
set @receiver='13737740287,15878359061,15812405921'
if exists (select * from UV_MsgInbox t1 where  t1.MsgType=1 and t1.MsgArrivedTime >= dateadd(minute,-5,getdate() 
 and t1.sender in (select 号码  from Result group by 号码 having count(DISTINCT 号码)>=2))

真没看懂，不过if判断好像多了个）

[SQLCenter]

这样不就行了吗，你要什么列随你啊，反正在 group by 列表就行：

select 号码 from Result group by 线路，号码 having count(DISTINCT 号码)>=2

[abuying]

if exists (select * from UV_MsgInbox t1 where  t1.MsgType=1 and t1.MsgArrivedTime >= dateadd(minute,-5,getdate())  
            and exists (select 号码  from Result where t.sender=result.号码 group by 号码 having count(DISTINCT 号码)>=2))

[mms89757]

[Quote=引用 2 楼 sqlcenter 的回复:]
这样不就行了吗，你要什么列随你啊，反正在 group by 列表就行：

select 号码 from Result group by 线路，号码 having count(DISTINCT 号码)>=2
[/Quote]
这个好像不行。我数据里面明明存在有符合条件的号码，这样写的话查询的时候查询结果是空的

[SQLCenter]

[Quote=引用 4 楼 mms89757 的回复:]
这个好像不行。我数据里面明明存在有符合条件的号码，这样写的话查询的时候查询结果是空的
[/Quote]
那不是这条语句的问题，看看其他方面的逻辑，这条语句能找到你预期的“号码”

[mms89757]

if exists(select * from sysobjects where name='Result' )
  drop table Result

select LineNum as 线路,SIM as 号码 into Result  from UV_MsgInbox t1 where t1.MsgArrivedTime >= dateadd(minute,-5,getdate()) and MsgTitle='外接电源断电，启动内置电源'  and not exists(select * from UV_MsgInbox t2 where t2.Sender = t1.SIM
 	and t2.MsgArrivedTime >= dateadd(minute,-5,getdate())
     and t2.MsgTitle ='恢复正常供电')
    group by  t1.SIM,t1.LineNum 
select 线路  from Result  group by 线路 having count(DISTINCT 号码)>=2
      if exists(select 线路  from Result  group by 线路 having count(DISTINCT 号码)>=2)
  	begin
	--declare @receiver varchar(500)
	--set @receiver='13737740287,15878359061,15812405921'
	 if exists (select * from UV_MsgInbox t3 where  t3.MsgType=1 and t3.MsgArrivedTime >= dateadd(minute,-5,getdate())  and t3.sender in (select SIM as 号码 from UV_MsgInbox t1 where t1.MsgArrivedTime >= dateadd(minute,-5,getdate())
			 and MsgTitle='外接电源断电，启动内置电源' 
	             and not exists(select * from UV_MsgInbox t2
                      where t2.Sender = t1.SIM
                       and t2.MsgArrivedTime >= dateadd(minute,-5,getdate())
                       and t2.MsgTitle ='恢复正常供电')
  			group by  t1.SIM,t1.LineNum ))--后半括号中子查询不对
	    begin
                 ......下面内容省略

这样写的话就是我要的结果，但是这些语句太长太繁琐，所以看看能不能简化它 让人看起来不那么头疼

[mms89757]

错了，错了。。。
后面还要判断号码个数是否大于等于2
就像这句select 线路 from Result group by 线路 having count(DISTINCT 号码)>=2这样
上面的子查询还没判断，能够在上面的基础上加上判断号码这个条件就是最后要的结果了

[mms89757]

if exists(select * from sysobjects where name='Result' )
  drop table Result

select LineNum as 线路,SIM as 号码 into Result  from UV_MsgInbox t1 where t1.MsgArrivedTime >= dateadd(minute,-5,getdate()) and MsgTitle='外接电源断电，启动内置电源'  and not exists(select * from UV_MsgInbox t2 where t2.Sender = t1.SIM
 	and t2.MsgArrivedTime >= dateadd(minute,-5,getdate())
     and t2.MsgTitle ='恢复正常供电')
    group by  t1.SIM,t1.LineNum 
select 线路  from Result  group by 线路 having count(DISTINCT 号码)>=2
      if exists(select 线路  from Result  group by 线路 having count(DISTINCT 号码)>=2)
  	begin
	--declare @receiver varchar(500)
	--set @receiver='13737740287,15878359061,15812405921'
	 if exists (select * from UV_MsgInbox t3 where  t3.MsgType=1 and t3.MsgArrivedTime >= dateadd(minute,-5,getdate())  and t3.sender in (select 号码 from Result group by 号码))--后半括号中子查询不对
	    begin
               ......后面省略

这样写行不？