当没有用 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判断好像多了个)
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-21 02:14
社区公告
暂无公告