XH WFDD HPHM HPZLMS WFXWMS WFSJ ZP ZPBH5115000000250723 5115000005 川A9J270 小型汽车 不按规定停车 2008-7-10 20:17:11 <BLOB> 1
5115000000250723 5115000005 川A9J270 小型汽车 不按规定停车 2008-7-10 20:17:11 <BLOB> 2
5115000000250723 5115000005 川A9J270 小型汽车 不按规定停车 2008-7-10 20:17:11 <BLOB> 3
5115000000225316 5115000002 川C36058 小型汽车 不按规定停车 2008-5-16 15:32:10 <BLOB> 1
5115000000225316 5115000002 川C36058 小型汽车 不按规定停车 2008-5-16 15:32:10 <BLOB> 2
5115000000225316 5115000002 川C36058 小型汽车 不按规定停车 2008-5-16 15:32:10 <BLOB> 3
5115000000225318 5115000002 川Q00485 小型汽车 不按规定停车 2008-5-16 15:34:10 <BLOB> 1
5115000000225318 5115000002 川Q00485 小型汽车 不按规定停车 2008-5-16 15:34:10 <BLOB> 2以上为一张违法车辆的数据表,可以命名为Peccancy,数据库使用Oracle。可看出,每列数据除了ZP(照片,blob字段)和ZPBH(照片编号)不一样,其余的序号、违法地点、号牌号码等字段都一致,因为每个违法车辆都包含三个图片。我现在想要整理这些数据,将同一辆车的图片(ZP)合并到一条记录中,每辆车只有一条记录,但含3张图片。对于同一辆车的判定,若XH字段一致,就可以认为这是同辆车的违法记录。最后可达到以下效果:XH …… HPZLMS WFXWMS WFSJ ZP1 ZP2 ZP3 XH …… 小型汽车 不按规定停车 2008-7-10 20:17:11 <BLOB> <BLOB> <BLOB> 请赐教!
5115000000250723 5115000005 川A9J270 小型汽车 不按规定停车 2008-7-10 20:17:11 <BLOB> 2
5115000000250723 5115000005 川A9J270 小型汽车 不按规定停车 2008-7-10 20:17:11 <BLOB> 3
5115000000225316 5115000002 川C36058 小型汽车 不按规定停车 2008-5-16 15:32:10 <BLOB> 1
5115000000225316 5115000002 川C36058 小型汽车 不按规定停车 2008-5-16 15:32:10 <BLOB> 2
5115000000225316 5115000002 川C36058 小型汽车 不按规定停车 2008-5-16 15:32:10 <BLOB> 3
5115000000225318 5115000002 川Q00485 小型汽车 不按规定停车 2008-5-16 15:34:10 <BLOB> 1
5115000000225318 5115000002 川Q00485 小型汽车 不按规定停车 2008-5-16 15:34:10 <BLOB> 2以上为一张违法车辆的数据表,可以命名为Peccancy,数据库使用Oracle。可看出,每列数据除了ZP(照片,blob字段)和ZPBH(照片编号)不一样,其余的序号、违法地点、号牌号码等字段都一致,因为每个违法车辆都包含三个图片。我现在想要整理这些数据,将同一辆车的图片(ZP)合并到一条记录中,每辆车只有一条记录,但含3张图片。对于同一辆车的判定,若XH字段一致,就可以认为这是同辆车的违法记录。最后可达到以下效果:XH …… HPZLMS WFXWMS WFSJ ZP1 ZP2 ZP3 XH …… 小型汽车 不按规定停车 2008-7-10 20:17:11 <BLOB> <BLOB> <BLOB> 请赐教!
解决方案 »
- oracle 语句求助
- centos+oracle11g+VM——谢谢
- dbms_job.submit:想要在今天23点执行,然后每一小时执行一次
- oracle10.2 rman备份到异机如何实现
- 求解
- 关于oracle9i在red hat9.0下安装按语遇到的一个问题
- 如何将 oracle 9i中的数据导入到oracle 8i中去???
- 请问Oracle 8i和9i如何卸载???谢谢!
- 如何能免费下载到Norton Antivirus 2002 for win2000 Advanced Server,必定结贴!
- 关于监听器的问题,急在线等待!
- 关于64位机器下调用oci
- 为何找不到qu.id???????
select XH,WFDD,HPHM,HPZLMS,WFXWMS,WFSJ,ZP as ZP_1 from Peccancy where zpbh=1alter table new_table add ZP_2 blob;
alter table new_table add ZP_3 blob;update new_table set ZP_2=(select zp from Peccancy where zpbh=2),ZP_3=(select zp from
Peccancy where zpbh=3)
select distinct D.XH ,D.WFDD ,D.HPHM ,D.HPZLMS ,D.WFXWMS ,D.WFSJ ,
(select A.ZP from tb A where A.ZPBH=1 and A.XH=D.XH and rownum=1) ,
(select B.ZP from tb B where B.ZPBH=2 and B.XH=D.XH and rownum=1) ,
(select C.ZP from tb C where C.ZPBH=3 and C.XH=D.XH and rownum=1)
from tbl D
select xh, wfdd, hphm, hpzlms, wfxwms, wfsj, zp1, zp2, zp3
from (select xh,
wfdd,
hphm,
hpzlms,
wfxwms,
wfsj,
zpbh,
zp1,
zp2,
lead(zp2) over(order by zp2) zp3
from (select a.xh,
a.wfdd,
a.hphm,
a.hpzlms,
a.wfxwms,
a.wfsj,
a.zpbh,
a.zp zp1,
lead(a.zp) over(order by a.zp) zp2
from Peccancy a
order by 1 desc)
order by 1 desc)
where zpbh = 1;
谢谢帮助,我按照这种写法完成了sql语句,报出了ORA-00932错误。下面是我的sql语句select distinct D.xh,
D.wfdd,
D.wfdz,
D.hphm,
D.hpzlms,
D.wfxwms,
D.wfsj,
(select A.ZP as Zjwj1
from PECCANCY_PHOTO A
where A.ZPBH = 1
and A.WFXH = D.XH
and rownum = 1),
(select B.ZP as Zjwj2
from PECCANCY_PHOTO B
where B.ZPBH = 2
and B.WFXH = D.XH
and rownum = 1),
(select C.ZP as Zjwj3
from PECCANCY_PHOTO C
where C.ZPBH = 3
and C.WFXH = D.XH
and rownum = 1)
from (select t.WFXH as XH,
t.WFDD,
'' as WFDZ,
t.HPHM as Hphm,
h.DMSM1 as HPZLMS,
c.WFMS as WFXWMS,
t.WFSJ as Wfsj,
p.ZP as Zjwj1
from PECCANCY t, VIO_CODEWFDM c, VIO_HPZL h, PECCANCY_PHOTO p
where t.WFXH = p.WFXH
and t.HPZL = h.DMZ
and t.WFXW = c.WFXW
and t.WFSJ >=
to_date('2007-6-22 0:00:00', 'yyyy-MM-dd hh24:mi:ss')
and t.WFSJ <=
to_date('2010-6-22 20:32:48', 'yyyy-MM-dd hh24:mi:ss')
and t.HPZL = '02') D
错误:
ORA-00932错误:不一致的数据类型:要求-得到的却是blob错误。
select xh, wfdd, hphm, hpzlms, wfxwms, wfsj, zp1, zp2, zp3
from (select xh,
wfdd,
hphm,
hpzlms,
wfxwms,
wfsj,
zpbh,
zp1,
zp2,
lead(zp2) over(order by zp2) zp3
from (select a.xh,
a.wfdd,
a.hphm,
a.hpzlms,
a.wfxwms,
a.wfsj,
a.zpbh,
a.zp zp1,
lead(a.zp) over(order by a.zp) zp2
from (select t.WFXH as XH,
t.WFDD,
t.HPHM as Hphm,
h.DMSM1 as HPZLMS,
c.WFMS as WFXWMS,
t.WFSJ as Wfsj,
p.ZP,
p.zpbh
from PECCANCY t,
VIO_CODEWFDM c,
VIO_HPZL h,
PECCANCY_PHOTO p
where t.WFXH = p.WFXH
and t.HPZL = h.DMZ
and t.WFXW = c.WFXW
and t.WFSJ >=
to_date('2007-6-22 0:00:00',
'yyyy-MM-dd hh24:mi:ss')
and t.WFSJ <=
to_date('2010-6-22 20:32:48',
'yyyy-MM-dd hh24:mi:ss')
and t.HPZL = '02') a
order by 1 desc)
order by 1 desc)
where zpbh = 1;botbaby,根据你的sql,我也写出了自己的sql语句,但同楼上一样,也会报出同样的错误:
ORA-00932错误:不一致的数据类型:要求-得到的却是blob错误。
这样的错误没遇到过,该怎样解决呢?
是不是你这几张基础表照片字段类型搞错了,还是blob用法太特殊?不了解,等高人出现别忘了给我点分
select XH, WFDD, HPHM, HPZLMS, WFXWMS, WFSJ, max(decode(rn,1,ZP)),max(decode(rn,2,ZP)),max(decode(rn,3,ZP)),max(decode(rn,4,ZP)),max(decode(rn,5,ZP)),max(decode(rn,6,ZP)),ZPBH
(select XH, WFDD, HPHM, HPZLMS, WFXWMS, WFSJ, ZP, ZPBH,row_number() over(partition by XH order by XH) rn
from t ) t
group by XH, WFDD, HPHM, HPZLMS, WFXWMS, WFSJ,ZPBH
10G有一个函数直接就能转换