select * from a
--车保险信息表
create table b(
bxdh varchar(16) primary key,--保险单号
cphm varchar(16),--车牌号码
bxzl varchar(16),--保险种类(只有两种,交强险,商业险)
dqrq Date--到期日期
);insert into b(bxdh,cphm,bxzl,dqrq) values('1','aa','交强险',TO_DATE('2010-01-01 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into b(bxdh,cphm,bxzl,dqrq) values('2','bb','交强险',TO_DATE('2010-01-02 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into b(bxdh,cphm,bxzl,dqrq) values('3','cc','交强险',TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into b(bxdh,cphm,bxzl,dqrq) values('4','aa','交强险',TO_DATE('2010-01-04 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into b(bxdh,cphm,bxzl,dqrq) values('5','aa','交强险',TO_DATE('2010-01-05 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into b(bxdh,cphm,bxzl,dqrq) values('6','aa','商业险',TO_DATE('2010-01-06 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into b(bxdh,cphm,bxzl,dqrq) values('7','aa','商业险',TO_DATE('2010-01-07 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into b(bxdh,cphm,bxzl,dqrq) values('8','bb','商业险',TO_DATE('2010-01-08 12:08:26','YYYY-MM-DD HH:MI:SS'));
select * from b;--事故表
create table c(
sgid int primary key,--事故id
cphm varchar(16)--车牌号码
)insert into c values (1,'aa');
insert into c values (2,'aa');
insert into c values (3,'aa');
insert into c values (4,'bb');
insert into c values (5,'bb');
insert into c values (6,'bb');
insert into c values (7,'bb');select * from c
希望得到查询结果:
车牌号码 车辆名称 交强险到期日期 商业险到期日期 事故
aa 红旗1 2010-01-05 2010-01-07 3
bb 宝马2 2010-01-02 2010-01-08 4
cc 奔驰3 2010-01-03 0查处车辆信息中所有车辆,对应的保险信息(如果有多个,则显示最晚到期的那条;没有则不显示),车辆事故的次数谢谢大家!
create table a(
cphm varchar(16) primary key,--车牌号码
clmc varchar(16)--车辆名称
);
insert into a(cphm ,clmc) values('aa','红旗1');
insert into a(cphm ,clmc) values('bb','宝马2');
insert into a(cphm ,clmc) values('cc','奔驰3');怎么贴少了这个表,晕
where rn = 1伪代码
with tem as
(
select c.cphm,a.clmc from
a_1 a,
c
where c.cphm = a.cphm
)
select * from
(select t.*,row_number() over(partition by cphm,bxzl order by dqrq desc) rn from b t) table_1 ,
tem t_2
where table_1.cphm = t_2.cphm
and table_1.rn = '1'
(
select c.cphm,a.clmc from
a,
c
where c.cphm = a.cphm
)
select * from
(select t.*,row_number() over(partition by cphm order by dqrq desc) rn from b t) table_1 ,
tem t_2
where table_1.cphm = t_2.cphm
and table_1.rn = '1'
这个
to_char(max(case when bxzl='交强险' then dqrq end),'yyyy-mm-dd') AA,
to_char(max(case when bxzl='商业险' then dqrq end),'yyyy-mm-dd') BB,
nvl(c.sgid,0)
from a ,b,(select count(sgid) sgid,cphm from c group by cphm) c
where a.cphm=b.cphm(+) and a.cphm=c.cphm(+)
group by a.cphm,a.clmc,c.sgid
aa 红旗1 2010-01-05 2010-01-07 3
bb 宝马2 2010-01-02 2010-01-08 4
cc 奔驰3 2010-01-03 0
2 max(decode(bxzl,'商业险',dqrq)) 商业险到期日期,nvl(sgzs,0) 事故次数
3 from a left join b on a.cphm=b.cphm
4 left join(select cphm,count(1) sgzs from c group by cphm) t
5 on a.cphm=t.cphm
6 group by a.cphm,a.clmc,t.sgzs
7 order by a.cphm
8 /车牌号码 车牌名称 交强险到期 商业险到期 事故次数
---------------- ---------------- ---------- ---------- ----------
aa 红旗1 2010-01-05 2010-01-07 3
bb 宝马2 2010-01-02 2010-01-08 4
cc 奔驰3 2010-01-03 0
into #jqxmm
from #a a ,#b b where a.cphm=b.cphm--#mm
and b.bxzl='交强险'
group by a.cphm,a.clmc
order by a.cphm,a.clmcselect * from #syxmm
select a.cphm,a.clmc,max(b.dqrq) as syx
into #syxmm
from #a a ,#b b where a.cphm=b.cphm--#mm
and b.bxzl='商业险'
group by a.cphm,a.clmc
order by a.cphm,a.clmcselect cphm,count(sgid) as sumsg into #sg from #c
group by cphmselect a.cphm,a.clmc,a.jqx,b.syx,isnull(c.sumsg,0) from #jqxmm a
left join #syxmm b on a.cphm=b.cphm
left join #sg c on a.cphm=c.cphm
into #jqxmm
from #a a ,#b b where a.cphm=b.cphm--#mm
and b.bxzl='交强险'
group by a.cphm,a.clmc
order by a.cphm,a.clmcselect * from #syxmm
select a.cphm,a.clmc,max(b.dqrq) as syx
into #syxmm
from #a a ,#b b where a.cphm=b.cphm--#mm
and b.bxzl='商业险'
group by a.cphm,a.clmc
order by a.cphm,a.clmcselect cphm,count(sgid) as sumsg into #sg from #c
group by cphmselect a.cphm,a.clmc,a.jqx,b.syx,isnull(c.sumsg,0) from #jqxmm a
left join #syxmm b on a.cphm=b.cphm
left join #sg c on a.cphm=c.cphm