当前位置: 代码迷 >> Sql Server >> 筛选数据解决思路
  详细解决方案

筛选数据解决思路

热度:39   发布时间:2016-04-27 18:42:50.0
筛选数据
select Called_Station_Id,F_User_point,h323_connect_Time from TB_CDR where Calling_Station_Id='0429990001' 
and Called_Station_Id like '0[1-9]%' 
and len(substring(Called_Station_Id,charindex('99',Called_Station_Id)+2,len(Called_Station_Id)))>4
and convert (varchar(10),convert(dateTime, h323_connect_Time),112) like '200803%' 
这条语句能够把大多数的"区号+990001"这样的号码找到,但是遇到像"029或0429"等等这样的区号里有9的就不能够筛选出去了 ,请教下怎么把这样的数据筛选掉呢?谢谢了

Called_Station_Id这个字段下的有些数据不需要,需要筛选出来.具体的数据是: 
Called_Station_Id F_User_point h323_connect_Time 

0411990002 0.04 2008/03/21 10:15:57 
042913842922224 0.54 2008/03/20 09:46:55 
042913898935335 0.09 2008/03/20 09:27:22 
04293991150 0.09 2008/03/20 08:25:21 
0411990001 0.04 2008/03/17 17:14:52 
0429990002 0.04 2008/03/17 14:14:54 
029990004 0.15 2008/03/15 12:15:56 
我们是要把像0411990001,0429990002,029990004这样的数据筛选掉,保留其他的数据.应该怎么写?

------解决方案--------------------
上次发了的吧?
这个东西很难完全匹配。。
试试

SQL code
declare @t table(Called_Station_Id varchar(20),F_User_point numeric(8,2),h323_connect_Time datetime)  insert into @t select '0411990002','0.04','2008/03/21 10:15:57' insert into @t select '042913842922224','0.54','2008/03/20 09:46:55'insert into @t select '042913898935335','0.09','2008/03/20 09:27:22'insert into @t select '04293991150','0.09','2008/03/20 08:25:21'insert into @t select '0411990001','0.04','2008/03/17 17:14:52'insert into @t select '0429990002','0.04','2008/03/17 14:14:54'insert into @t select '029990004','0.15','2008/03/15 12:15:56'select * from @t where patindex('[0-9][0-9][0-9]99[0-9][0-9][0-9][0-9]',Called_Station_Id)>0orpatindex('[0-9][0-9][0-9][0-9]99[0-9][0-9][0-9][0-9]',Called_Station_Id)>0
------解决方案--------------------
SQL code
--try:select Called_Station_Id,F_User_point,h323_connect_Time from TB_CDR where Calling_Station_Id='0429990001'   and Called_Station_Id like '0[1-9]%'   and patindex('[0-9][0-9][0-9]99[0-9][0-9][0-9][0-9]',Called_Station_Id)!>0and patindex('[0-9][0-9][0-9][0-9]99[0-9][0-9][0-9][0-9]',Called_Station_Id)!>0and convert (varchar(10),convert(dateTime, h323_connect_Time),112) like '200803%'
  相关解决方案