如题,比如一个这样的字符串('10103,10104,10201,10105'),我要折成('10103,10104','10104,10201','10201,10105'),下面是我要查询的语句
select (c.stationname || '->' || b.stationname) as road,decode(vehclass, 1, '收费', '免费') as vehclass,d.vehtypename,sum(vehcount) as vehcount
from ac_odstat a, pm_station b, pm_station c,
(select vehtypeno,(vehtypeno || '.' || vehtypename) as vehtypename from pm_vehtype) d
where a.outstationno = b.stationno
and a.instationno = c.stationno
and a.vehtype = d.vehtypeno
and (a.instationno || ',' || a.outstationno) in (折分后的字符)
and SquadDate>=trunc(to_date('2008-2-1','yyyy-mm-dd'))
and SquadDate<=trunc(to_date('2008-2-1','yyyy-mm-dd') )
group by c.stationname,b.stationname,a.instationno,a.vehclass,d.vehtypeno,d.vehtypename
order by c.stationname,a.vehclass,d.vehtypeno
还能在in的时候,匹配时没有记录的情况,能返回一个空值,比如(a.instationno || ',' || a.outstationno) in ('10103,10104','10104,10201','10201,10105'),数据库里没有('10103,10104')匹配的,这时查询结果中为空.请问这个怎么解决?
select (c.stationname || '->' || b.stationname) as road,decode(vehclass, 1, '收费', '免费') as vehclass,d.vehtypename,sum(vehcount) as vehcount
from ac_odstat a, pm_station b, pm_station c,
(select vehtypeno,(vehtypeno || '.' || vehtypename) as vehtypename from pm_vehtype) d
where a.outstationno = b.stationno
and a.instationno = c.stationno
and a.vehtype = d.vehtypeno
and (a.instationno || ',' || a.outstationno) in (折分后的字符)
and SquadDate>=trunc(to_date('2008-2-1','yyyy-mm-dd'))
and SquadDate<=trunc(to_date('2008-2-1','yyyy-mm-dd') )
group by c.stationname,b.stationname,a.instationno,a.vehclass,d.vehtypeno,d.vehtypename
order by c.stationname,a.vehclass,d.vehtypeno
还能在in的时候,匹配时没有记录的情况,能返回一个空值,比如(a.instationno || ',' || a.outstationno) in ('10103,10104','10104,10201','10201,10105'),数据库里没有('10103,10104')匹配的,这时查询结果中为空.请问这个怎么解决?
另外建立一个函数或者存储过程来处理字符串的拆分.并且把拆分后的数据存储到一个特定的表或者视图中,然后的事情就好办了,你在SQL里改一下就可以了.
至于字符串的拆分的方法有很多.....
好一点的方法暂时没有想出来,等高手吧.呵呵...