获取一对多关系表中的最新数据

Question

我想从表a、b、c中提取数据，但要按表响应的最新数据排序

工作台的结构是这样的(简化)：

casework_id | problem   | user_id          
------------+-----------+-------
1           | Problem1  | 1  
2           | Problem2  | 2
3           | Problem3  | 1
4           | Problem4  | 3

表user具有以下结构(简化)：

user_id | name         
--------+-----------------
1       | peter  
2       | Sam  
3       | Tom  
4       | Steve

表响应结构如下(简化)：

response_id | response   | casework_id | created           
------------+-----------+--------------+-------
1           | responce1  |  1      | 2012-10-14 11:28:31
2           | responce2  |  1      | 2012-9-10 11:28:31 
3           | responce3  |  1      | 2012-9-2 11:28:31
4           | responce4  |  3      | 2012-8-3 11:28:31
4           | responce5  |  3      | 2012-8-2 11:28:31

我正在寻找按最新响应和按casework_id分组来获取数据的查询

I. e. required out put is 

casework_id | problem   | name  | responce  | created        
------------+-----------+-------+-----------+---------
1           | Problem1  | peter | responce1 | 2012-10-14 11:28:31
2           | Problem2  | Sam   | Null      | Null
3           | Problem3  | peter | responce4 | 2012-8-3 11:28:31 
4           | Problem4  | Tom   | Null      | Null

如果你们这些好心人中有人能给我指个方向，我将不胜感激。

Answer 1

您可以使用以下内容：

select c.casework_id,
  c.problem,
  u.name,
  r2.response,
  r1.created
from casework c
left join user u
  on c.user_id = u.user_id
left join
(
  select max(created) created, casework_id
  from response r
  group by casework_id
) r1
  on c.casework_id = r1.casework_id
left join response r2
  on r1.created = r2.created
  and r1.casework_id = r2.casework_id

请参阅SQL Fiddle with Demo

如果您希望既包括创建案例工作的用户，又包括做出响应的用户，则需要在user表上联接两次：

select c.casework_id,
  c.problem,
  u1.name CreatedByName,
  r2.response,
  r1.created,
  u2.name ReponseName
from casework c
left join user u1
  on c.user_id = u1.user_id
left join
(
  select max(created) created, casework_id
  from response r
  group by casework_id
) r1
  on c.casework_id = r1.casework_id
left join response r2
  on r1.created = r2.created
  and r1.casework_id = r2.casework_id
left join user u2
  on r2.user_id = u2.user_id

请参阅SQL Fiddle with demo

Answer 2

我还没有测试过，但它可能会给你一个概念

select c.casework_id, c.problem, 
(select name from user u where u.user_id = c.user_id ), 
(select r.reponse from response r where r.casework_id = c.casework_id ORDER BY r.created DESC LIMIT 1),
(select r.created from response r where r.casework_id = c.casework_id ORDER BY r.created DESC LIMIT 1),
from casework c

Answer 3

SELECT responce.casework_id, problem, name, responce, created 
FROM responce
JOIN
   (SELECT casework_id, problem, name 
    FROM casework JOIN user 
    ON casework.userid=user.userid) AS A
ON responce.casework_id=A.casework_id
ORDER BY responce, responce.casework_id