select distinct c_cityname,sum(case when moormt=1 and submitstatus='0' then 1 else 0 end)
Over(Partition By c_cityname),sum(case when moormt=1 then 1 else 0 end)
Over(Partition By c_cityname),count(distinct destmobile) Over(Partition By c_cityname),
Count(distinct destmobile)
Over(Partition By c_cityname) -
(select Count(distinct destmobile) Over(Partition By c_cityname)
From history.msghistory a
where a.savetime >= to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss') and
Not Exists
(Select * From history.msghistory b Where
a.destmobile = b.destmobile and a.savetime <
to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss')
))
from (select * from history.msghistory a left outer join
(select distinct
mobile,cityname as c_cityname from sms.mobilenumber)
b on
SUBSTR(a.DESTMOBILE,1,7)=b.mobile(+) where
a.savetime >= to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss'))
大家好,请问在上面的SQL中,为什么我在 Not Exists
(Select * From history.msghistory b Where
a.destmobile = b.destmobile and a.savetime <
to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss')
)) 中加上a.savetime <
to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss')
就报:ORA-01427单行子查询返回多个行错误?请问这是什么原因,怎样才可以避免?
Over(Partition By c_cityname),sum(case when moormt=1 then 1 else 0 end)
Over(Partition By c_cityname),count(distinct destmobile) Over(Partition By c_cityname),
Count(distinct destmobile)
Over(Partition By c_cityname) -
(select Count(distinct destmobile) Over(Partition By c_cityname)
From history.msghistory a
where a.savetime >= to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss') and
Not Exists
(Select * From history.msghistory b Where
a.destmobile = b.destmobile and a.savetime <
to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss')
))
from (select * from history.msghistory a left outer join
(select distinct
mobile,cityname as c_cityname from sms.mobilenumber)
b on
SUBSTR(a.DESTMOBILE,1,7)=b.mobile(+) where
a.savetime >= to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss'))
大家好,请问在上面的SQL中,为什么我在 Not Exists
(Select * From history.msghistory b Where
a.destmobile = b.destmobile and a.savetime <
to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss')
)) 中加上a.savetime <
to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss')
就报:ORA-01427单行子查询返回多个行错误?请问这是什么原因,怎样才可以避免?
a.destmobile = b.destmobile and a.savetime <
to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss')
)) 中加上a.savetime <
to_date('2010-07-8 0:00:00','yyyy-mm-dd hh24:mi:ss')
这个里面的
a.destmobile = b.destmobile
出现 一个 a.destmobile 有多个 b.destmobile