表a: id int //用户ID name varchar //用户名 表b: b_id int //费用表 fee_date date //缴费日期;select a.id,a.name,b.feedate, row_number() over (partition by id order by feedate desc) as num from a,b where a.id=b.b_id and num=1
表a: id int //用户ID name varchar //用户名 表b: b_id int //费用表 fee_date date //缴费日期;select id,name,max(date) as lastdate from 表a as m inner join 表b as n on m.id = n.b_id group by id,name
select Id,name,fee_date from a inner join b on a.id = b.id where b.fee_date = (select max(fee_date) from b where b.id = a.id) 这样速度应该会更快一点,呵!
create or replace view view_ab as select "id","name","lastdate" from(select a.id as id,a.name as name,max(b.fee_date) as lastdate from 表a a,表b b where a.id=b.b_id)
我上面的错了,更正如下 select c.id,c.name,c.fee_date from (select a.id,a.name,b.fee_date, row_number() over (partition by id order by fee_date desc) as num from henry_a a,henry_b b where a.id=b.b_id) c where c.num=1
create or replace view v_lastfee as select a.id,a.name,b.lastdate from a, (select b_id,max(fee_date) lastdate from b group by b_id) b where a.id=b.b_id OK :)
id int //用户ID
name varchar //用户名
表b:
b_id int //费用表
fee_date date //缴费日期;select
a.id,a.name,b.feedate,
row_number() over (partition by id order by feedate desc) as num
from a,b where a.id=b.b_id and num=1
id int //用户ID
name varchar //用户名
表b:
b_id int //费用表
fee_date date //缴费日期;select id,name,max(date) as lastdate
from 表a as m
inner join 表b as n
on m.id = n.b_id
group by id,name
inner join b on a.id = b.id
where b.fee_date = (select max(fee_date) from b where b.id = a.id)
这样速度应该会更快一点,呵!
select "id","name","lastdate"
from(select a.id as id,a.name as name,max(b.fee_date) as lastdate from 表a a,表b b where a.id=b.b_id)
select c.id,c.name,c.fee_date from (select
a.id,a.name,b.fee_date,
row_number() over (partition by id order by fee_date desc) as num
from henry_a a,henry_b b where a.id=b.b_id) c where c.num=1
select a.id,a.name,b.lastdate
from a, (select b_id,max(fee_date) lastdate from b group by b_id) b
where a.id=b.b_id
OK :)