我有两个关于用户的表，我想将两个表连接起来并显示每个用户的最新评论
波纹管是桌子
我想查询每个用户的最新评论（每个用户1个评论）
我编写的波纹管查询，但是如果注释的日期和时间相同，则它将为每个用户返回多个注释
SELECT a.cust_id,b.remarks,b.contact_date,b.contact_time FROM customers a,(select * from customer_review
where (cust_id,contact_time,contact_date) in (select cust_id,max(contact_time),max(contact_date) from customer_review group by cust_id)
) b WHERE a.cust_id=b.cust_id;
请提供任何解决方案，以每位用户查询1个备注 （注意：抱歉，我无法更改列的详细信息） 实时链接Oracle Live
您可以加入并使用相关的子查询进行过滤：
select
c.cust_id,c.cust_name,r.remarks,r.contact_time
from customers c
inner join customer_review r
on r.cust_id = c.cust_id
and r.contact_time = (
select max(contact_time)
from customer_review r1
where r1.cust_id = r.cust_id
)
使用customer_review(cust_id,contact_time)上的索引，这应该是一个非常有效的选择。
如果您不需要来自customers表的信息（例如客户名称），则无需join：
select r.*
from customer_review r
where contact_time = (
select max(contact_time)
from customer_review r1
where r1.cust_id = r.cust_id
)
一种方法使用row_number()：
select cr.*
from (select cr.*,row_number() over (partition by cust_id order by contact_date desc,contact_time desc) as seqnum
from customer_review cr
) cr
where seqnum = 1;
对于示例结果，您不需要customer表，除非您希望没有评论的客户。
您可以尝试以下查询：
Select * from
(SELECT a.cust_id,b.remarks,b.contact_date,b.contact_time FROM customers a,(select * from customer_review
where (cust_id,contact_time,contact_date) in
(select cust_id,max(contact_time),max(contact_date) from customer_review group by cust_id order by max(contact_time) desc,max(contact_date) desc)
) b WHERE a.cust_id=b.cust_id) WHERE ROWNUM = 1;
我通过这种方式解决了问题；
select a.policyid,a.startdate,a.enddate,a.createdate,b.reasondescpsr reasondesc
from policysuspend a,policysuspendreason b
where a.reasonid = b.reasonid
and (a.policyid,a.createdate) in (select policyid,max(createdate)
from policysuspend
group by policyid)