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

mms89757 2010-10-21 02:14:21



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执行结果中的号码

怎么写才对呢

...全文

1401 点赞收藏 8

写回复

8 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

mms89757 2010-10-21



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

               ......后面省略

这样写行不？

回复赞

mms89757 2010-10-21

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

回复赞

mms89757 2010-10-21



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

                 ......下面内容省略

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

回复赞

SQLCenter 2010-10-21

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

回复赞

mms89757 2010-10-21

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

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

回复赞

abuying 2010-10-21



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))

回复赞

SQLCenter 2010-10-21

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

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

回复赞

claro 2010-10-21

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判断好像多了个）

回复赞

壹佰智慧门店V2_1.1.41支持全终端的万能门店小程序壹佰智慧门店V2_1.1.41支持全终端的万能门店小程序

festival-speechtools-devel-1.2.96-28.el7.i686.rpm 离线安装包，亲测可用

systemd-libs-219-62.el7.x86_64.rpm 离线安装包，亲测可用

零用现金总结表.pdf 零用现金总结表.pdf

compat-libpackagekit-glib2-16-0.8.9-1.el7.x86_64.rpm 离线安装包，亲测可用

零售店如何搞好宣传赢得商机.pdf 零售店如何搞好宣传赢得商机.pdf

autocorr-sl-5.3.6.1-19.el7.noarch.rpm 离线安装包，亲测可用

gnome-online-accounts-devel-3.28.0-1.el7.x86_64.rpm 离线安装包，亲测可用

google-noto-sans-ui-fonts-20141117-5.el7.noarch.rpm 离线安装包，亲测可用

利用MRGT监控DNS服务器的负载.pdf 利用MRGT监控DNS服务器的负载.pdf