mysql - 嵌套查询将获取分组结果中的最新行

Question

我有以下查询:

 SELECT * from `provider_info` where provnum not in 
 (select pi_provnum from prov_index where length(pi_provnum)=6)
 and length(provnum)=6 group by provnum

prov_index 每个 provnum/pi_provnum 只有一行。但provider_info 每个provnum 可以有多行。

我基本上是从一个表中提取第二个表中没有提供者条目 (provnum/pi_provnum) 的行。内部查询提供了provider_info 表中存在但prov_index 表中不存在的provnum 列的列表。 (length() 条件是我在查询中需要的额外内容)

由于provider_info中可能有多个条目具有相同的provnum，因此我使用“group by provnum”子句只给我一个实例。但我最终需要与具有最新“sourcedate”值的行相对应的所有列。

1. 有没有办法使用 DISTINCT 代替 GROUP 子句？

2. 最终，我需要从provider_info返回一个特定行(所有列)，即“soucedate”列中具有最新日期的行

我已经尝试过这个，但它不起作用:

 SELECT * from `provider_info` where provnum not in 
 (select pi_provnum from prov_index where length(pi_provnum)=6) 
 and length(provnum)=6 order by provnum,sourcedate desc group by provnum 
 having sourcedate=max(sourcedate)

基本上我想要的是provider_info中的一行的列表，该行具有最新的源日期列，其中provnum不存在于第二个表prov_index中。关键是我想要最后一行中的所有列，而不仅仅是 max(sourcedate)

这可以在单个复杂查询中完成吗？

Answer 1

您可以使用 NOT EXISTS 子句。如果在provider_info 中有相同provnum 和相同源日期(即最新日期)的两条记录，则可能会返回重复行。如果这在您的场景中是不可能的，或者如果您在这种情况下想要两行，那么这可能有效:

SELECT p1.* 
from `provider_info` p1 where p1.provnum not in 
(select pi_provnum from prov_index where length(pi_provnum)=6) 
and length(p1.provnum)=6
AND NOT EXISTS (
    SELECT * FROM provider_info p2 where p1.provnum = p2.provnum AND p1.sourcedate > p2.sourcedate 
)

旁注，我还将“*”替换为您希望查询返回的实际字段列表。