从与第一个表匹配的第二个表中选择最后一条记录

jchrr9hc 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(319)

我有两个表，第一个表与表单相关，第二个表与advisor相关。每个advisor可以为表单添加注解。我试着从两个表中选择一列。我的结果中有第二个表（顾问表）的最后一个主题，但我需要第二个表（顾问表）的第一个主题。


# DQL

SELECT
    f.name,
    f.title,
    f.conditionResultFinal,
    f.conditionResult,
    f.formCode,
    f.dateInsert,
    f.id,
    (a.idFormRequestProject),
    a.subject as subjectAdvisor
FROM AdminBundle:FormRequestProject f
JOIN AdminBundle:Advisor a
WHERE a.idFormRequestProject = f.id
    AND (f.conditionResultFinal = 0 OR f.conditionResult = 0)
    AND f.displayStatus = 1
GROUP BY f.id
ORDER by a.id,f.id DESC

mysql doctrine doctrine-orm

来源：https://stackoverflow.com/questions/53395146/doctrine-select-last-record-from-second-table-matching-with-first-table

1条答案

按热度按时间

bpzcxfmw1#

最后我解决了这个问题。我删除了groupby。并添加了新的where。。。

SELECT f.name,f.title,f.conditionResultFinal,
f.conditionResult ,
f.formCode,f.dateInsert ,
f.id,(a.idFormRequestProject),
a.subject as subjectAdvisor 
FROM AdminBundle:FormRequestProject f  
Left JOIN AdminBundle:Advisor a WHERE  f.id = a.idFormRequestProject AND a.id = 
(SELECT  Max (aa.id) FROM AdminBundle:Advisor aa WHERE a.idFormRequestProject = 
aa.idFormRequestProject ORDER by aa.id ASC )  AND 
(f.conditionResultFinal = 0 OR f.conditionResult = 0 ) AND  f.displayStatus =1   
ORDER by f.id DESC

赞(0）回复(0）举报 2021-06-19

我来回答

从与第一个表匹配的第二个表中选择最后一条记录

1条答案

相关问题

热门标签

最新问答