没有看明白!是不是这个意思?
select calling_nbr,called_nbr from nbr_tab where
called_nbr like '013%' or called_nbr like '13%' --手机号码
group by calling_nbr,called_nbr,start_time....
select calling_nbr,called_nbr from nbr_tab where
called_nbr like '013%' or called_nbr like '13%' --手机号码
group by calling_nbr,called_nbr,start_time....
from nbr_tab t1
where charindex('-l',t1.called_nbr)<>0
and exist (select * from nbr_tab t2
where t2.calling_nbr=t1.calling_nbr
and t2.start_time =t1.start_time
and t2.called_nbr<>t1.called_nbr
)
select t1.calling_nbr,t1.called_nbr
from nbr_tab t1
where charindex('-',t1.called_nbr)<>0
and exist (select * from nbr_tab t2
where t2.calling_nbr=t1.calling_nbr
and t2.start_time =t1.start_time
and t2.called_nbr<>t1.called_nbr
)
charindex 是什么意思呀?我在ORACLE试的时候提示 MISSING EXPRESSION 错误
to argin
在你的sql 语句中group by 是不起作用的,因为一个主叫号码,和一个通话开始时间唯一确定一条记录,不需要在分组了
"主叫号码相同,被叫号码不同,起始时间相同",
"因为一个主叫号码,和一个通话开始时间唯一确定一条记录,不需要在分组了"
你自相矛盾了!
我写的具体一点吧
calling_nbr called_nbr start_time
3576809 05172341732 2001/09/21 10:30:30
3576809 02154084505 2001/08/21 6:44:30
3576809 013905266336 2001/08/21 6:44:30
3757655 05168828386 2001/09/01 8:44:30
3576555 013005266336 2001/08/21 6:55:30
3576555 03358065491 2001/08/21 6:55:30
当然表中的数据很多,有几百万条,通过查询我想得到的结果是
3576809 013905266336 2001/08/21 6:44:30
3576555 013005266336 2001/08/21 6:55:30
请各位帮忙!
table_a.called_nbr,
table_a.start_time,
table_a.duration,
table_a.charge
from table_a,
(select calling_nbr,start_time
from table_a
group by calling_nbr,start_time
having count(*)>1) as temp_a
where table_a.calling_nbr=temp_a.calling_nbr and
table_a.start_time=temp_a.start_time and
table_a.called_nbr like '手机号码';