MySQL-每个ID的最新输入 [英] Mysql - most recent enter for each id

问题描述

我有一个查询，可从5个表中检索信息.它们之间的关系如附图所示.我已经创建了一个查询来执行此操作，到目前为止效果很好.问题是每个用户在"PROFISSIONAL_PORCENTAGEM"表中可以有1个或多个条目.我的查询正在检索所有这些.我想要基于日期字段"PROFISSIONAL_PORCENTAGEM_DATA"的最后一个.我已经对此问题进行了研究，当我们仅使用一个表时，检索每个ID的最新信息"的解决方案是相同的，请参见此处的示例

I have a query that retrieve information from 5 tables. The relation between them is showed in the picture attached. I have created a query to do this, and so far so good. The issue is each user can have 1 or more entries at "PROFISSIONAL_PORCENTAGEM" table. My query is retrieving all them. I want the last one based on the date field "PROFISSIONAL_PORCENTAGEM_DATA". I´ve performed a research about this issue and the solution of "retrieve the most recent of each id" is the same when we are using only one table see example here MySQL - Selecting the most recent post by each of the 10 most recent authors. But in this case I have 5 tables. I dont know how to do that. Any idea will be appreciated.

SELECT
  PP.PROFISSIONAL_PORCENTAGEM_Pk,
  UST.USER_NAME, 
  ADDR.ADDRESS_NOME, 
  ADDR.ADDRESS_ATIVO,
  PA.PROFESSIONAL_ADDRSS_PK, 
  PP.PROFISSIONAL_PORCENTAGEM_PORCENT,
  PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA,
  PP.PROFISSIONAL_PORCENTAGEM_DATA

FROM 
  profissional_porcentagem AS PP
  RIGHT JOIN professional_addrss AS PA
    ON(PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA=PA.PROFESSIONAL_ADDRSS_PK)

  INNER JOIN address ADDR
    ON(PA.PROFESSIONAL_ADDRSS_FKADDRSS=ADDR.ADDRESS_Pk)

  INNER JOIN professional PF
    ON(PA.PROFESSIONAL_ADDRSS_FKPROFESSIONAL=PF.PROFESSIONAL_Pk)

  INNER JOIN usert AS UST
    ON(PF.PROFESSIONAL_FKUSER=UST.USER_Pk)

WHERE UST.USER_Pk=3
AND ADDR.ADDRESS_ATIVO=1;

对不起，我需要10个声誉才能附加图片！！ 这是图像链接 http://imgur.com/ZP3OdeX

sorry I need 10 reputation to attach image!! This is the image link http://imgur.com/ZP3OdeX

推荐答案

好，终于找到了解决方案. 只需将以下代码添加到sql查询的末尾即可:

Well, finally I found the solution. Just add this code to the end of sql query:

AND PP.PROFISSIONAL_PORCENTAGEM_Pk = (
SELECT t2.PROFISSIONAL_PORCENTAGEM_Pk FROM profissional_porcentagem as t2
    where t2.PROFISSIONAL_PORCENTAGEM_FK_CLINICA = PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA 
    order by t2.PROFISSIONAL_PORCENTAGEM_Pk desc limit 1
)

然后我们将得到整个查询，如下所示:

And then we gonna have the entire query like this:

SELECT
PP.PROFISSIONAL_PORCENTAGEM_Pk,
UST.USER_NAME, 
ADDR.ADDRESS_NOME, 
ADDR.ADDRESS_ATIVO,
PA.PROFESSIONAL_ADDRSS_PK, 
PP.PROFISSIONAL_PORCENTAGEM_PORCENT,
PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA,
PP.PROFISSIONAL_PORCENTAGEM_DATA

FROM 
profissional_porcentagem AS PP
RIGHT JOIN professional_addrss AS PA
    ON(PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA=PA.PROFESSIONAL_ADDRSS_PK)

INNER JOIN address ADDR
    ON(PA.PROFESSIONAL_ADDRSS_FKADDRSS=ADDR.ADDRESS_Pk)

INNER JOIN professional PF
    ON(PA.PROFESSIONAL_ADDRSS_FKPROFESSIONAL=PF.PROFESSIONAL_Pk)

INNER JOIN usert AS UST
    ON(PF.PROFESSIONAL_FKUSER=UST.USER_Pk)

WHERE UST.USER_Pk=3
AND ADDR.ADDRESS_ATIVO=1

AND PP.PROFISSIONAL_PORCENTAGEM_Pk = (
SELECT t2.PROFISSIONAL_PORCENTAGEM_Pk FROM profissional_porcentagem as t2
    where t2.PROFISSIONAL_PORCENTAGEM_FK_CLINICA =   PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA 
    order by t2.PROFISSIONAL_PORCENTAGEM_Pk desc limit 1
);

注意:我受此帖子的启发选择最近的行每个用户的日期

Note: I was inspired by this post Select row with most recent date per user

这篇关于MySQL-每个ID的最新输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！