甲骨文双重选择问题
所以我对甲骨文这两个表:甲骨文双重选择问题
CLIENT
cl_id cl_name
1 John
2 Maria
付款
pa_id pa_date pa_status cl_id
1 2017-01-01 1 1
2 2017-01-01 1 2
3 2017-02-01 1 1
4 2017-02-01 1 2
5 2017-03-01 0 1
6 2017-03-01 1 2
我需要一个选择众所周知声明,让我的客户ID,姓名和他的最后付款的状态。所以,我选择的最终结果应该是:
cl_id cl_name pa_status
1 John 0
2 Maria 1
这是客户选择的作品:
select cl_id, cl_name from CLIENT;
就是付款选择工作的最后状态:
select * from ( select pa_status from PAYMENT ORDER BY PA_DATE DESC) where rownum = 1;
所以现在,我需要让他们一起工作。我试过2种方法没有奏效:
select cl_id, cl_name, (select * from ( select pa_status from PAYMENT ORDER BY PA_DATE DESC) where rownum = 1 and PAYMENT.cl_id = CLIENT.CL_ID) as last_status from CLIENT;
错误:无效的标识符
这:
select cl_id, cl_name, (select * from ( select pa_status from PAYMENT ORDER BY PA_DATE DESC) where rownum = 1) as last_status from CLIENT;
不给我任何错误,但只显示约翰的最后一个状态,即最后一条记录:
cl_id cl_name last_status
1 John 0
2 Maria 0
任何人都可以给我一个提示吗?
感谢
您需要使用分析功能。 这种功能可以让你将数据分割成一些组,并按照你的意愿对每个组的数据进行排名。
你的情况:
Select * from (
Select id, name, status, row_number() over (partition by p.cl_id order by p.pa_date desc) as rw
From client c join payments p on p.cl_id = c.cl_id)
Inn where inn.rw = 1;
这得到了客户 最大日期,然后获得最高支付编号与日期。
with max_date as (
select max(date) as max_date, cl_id from payments group by cl_id
)
select c.cl_id, c.cl_name, p.pa_sttus from client c
join payments p
on c.cl_id = p.cl_id
where p.pa_id = (select max(p2.pa_id) from payments p2
join max_date md
on p2.cl_id = md.cl_id
where p.cl_id = p2.cl_id
and p2.pa_date = md.max_date
)
这给了我最后一行无效的标识符:“md.pa_date” – Sambarilove
我使用了错误的列名。应该现在工作。 – gorrilla10101
先取日期的最大距离为每个客户端ID。
Select cl_id, max(pa_date) as pa_date from PAYMENTS group by cl_id
现在你把乌尔客户端表,并与上述子查询加入
select c.cl_id, c.cl_name,
(select pa_status from PAYMENT t where t.pa_date=p.pa_date and t.cl_id=p.cl_id)
from CLIENT c join (Select cl_id, max(pa_date) as pa_date from PAYMENTS group by cl_id) p on p.cl_id=c.cl_id
您可以使用Oracle的KEEP LAST
这里:
select cl_id, c.cl_name, last_payment.status
from client
join
(
select
cl_id,
max(pa_status) keep (dense_rank last order by pa_date) as status
from payments
group by cl_id
) last_payment using (cl_id);
(如果要包括但不付款的客户,变化加入到LEFT OUTER JOIN
。)
这工作很好的家伙。谢谢! – Sambarilove
太好了。还有更好的语法 - 寻找FIRST分析函数。祝你好运! – user2671057