子查询和加入

Question

我有3个关于抱怨的表。第一个表由投诉信息本身组成，第二个表是带有status_id的clock_review，第三个是status_id表，组成了状态信息。我正在尝试从cluckain_review（按日期desc进行排序）中从抱怨和最新status_id中选择complain_desc，并将其与clive_status信息一起。

这是我尝试的（到目前为止还没有成功）：

SELECT c1.complain_desc, c2.status_id, c2.name as statusDesc from complain c1 
left join
(SELECT c3.status_id, c4.name, c3.complain_id FROM complain_review c3
inner join complain_status c4 on c4.id=c3.status_id ORDER by c3.date DESC) c2 
on c2.complain_id=c1.id

这是@maheshiv

提供的更新示例

..我已经浏览了网站，但我不确定要搜索有关此问题的关键字：（

编辑：我已经在http://sqlfiddle.com/#！9/d86a7a/2上构建了一个架构

编辑：也许这将是我能得到的最接近的..

SELECT c.complain_desc, cr1.status_id, cs.name
  FROM complain c
  INNER JOIN complain_review cr1 ON c.id=cr1.complain_id
  INNER JOIN complain_status cs ON cs.id=cr1.status_id
  WHERE cr1.date = (SELECT MAX(cr2.date) FROM complain_review cr2

cr1.complain_id = cr2.complain_id）

Answer 1

我正在尝试从cluckain_review（按date desc进行排序）从抱怨和最新status_id中选择cluck_desc，并将其与complain_status信息相结合。

这是堆栈溢出的一个非常普遍的问题。您可以按照最大的每组最大的方式找到许多解决方案。

这是使用您的示例的解决方案：

SELECT c.complain_desc, latest_cr.status_id, cs.name AS status_desc
FROM complain AS c
INNER JOIN (
    SELECT complain_id, status_id 
    FROM (
        SELECT cr.complain_id, cr.status_id, 
          IF(@cgroup=cr.complain_id, @rownum:=@rownum+1, 1) AS rownum,
          (@cgroup:=cr.complain_id)
        FROM (SELECT @cgroup:=0, @rownum:=1) AS _init
        CROSS JOIN complain_review AS cr
        ORDER BY cr.complain_id DESC, cr.date DESC
    ) AS n
    WHERE n.rownum = 1 
) AS latest_cr 
  ON c.id=latest_cr.complain_id
INNER JOIN complain_status AS cs
  ON cs.id = latest_cr.status_id;

这是一个使用无子查询的不同解决方案：

SELECT c.complain_desc, cr1.status_id, cs.name AS status_desc
FROM complain AS c
INNER JOIN complain_review AS cr1
  ON cr1.complain_id = c.id
LEFT OUTER JOIN complain_review AS cr2
  ON cr2.complain_id = c.id AND (cr2.date > cr1.date OR cr2.date = cr1.date AND cr2.id > cr1.id)
INNER JOIN complain_status AS cs
  ON cs.id = cr1.status_id
WHERE cr2.id IS NULL;

Answer 2

我认为您可能需要此查询，我相信Max Status_ID是投诉的最新状态。根据http://sqlfiddle.com/#！9/d86a7a/15

select c1.complain_desc, c2.status_id, c3.name from complain c1 inner join (select complain_id, max(status_id) from complain_review group by complain_id) c2 on c1.id=c2.complain_id inner join complain_status c3 on c3.id=c2.status_id;