mysql - 组连接查询问题

Question

我知道这可能无法回答，但我正在尝试找出查询返回 0 个结果的原因。

SELECT SQL_CALC_FOUND_ROWS 
  records.instance_id
  ,instances.patient_id
  ,form_1_data.field_1 AS field_0
  ,form_1_data.field_2 AS field_1
  ,records.field_240 AS field_2
  ,**GROUP_CONCAT(events.start_time SEPARATOR '<br />')**  AS field_3 
FROM form_4_data AS records 
LEFT JOIN instances ON instances.id= records.instance_id 
LEFT JOIN events ON records.instance_id = events.instance_id  
LEFT JOIN form_3_data ON records.field_111= form_3_data.instance_id  
LEFT JOIN form_1_data ON form_3_data.field_94= form_1_data.instance_id 
WHERE instances.active=1  
ORDER BY records.instance_id DESC  
LIMIT 25,25

返回 0 个结果。

但是，

SELECT SQL_CALC_FOUND_ROWS 
  records.instance_id
  ,instances.patient_id
  ,form_1_data.field_1 AS field_0
  ,form_1_data.field_2 AS field_1
  ,records.field_240 AS field_2
  ,**events.start_time AS field_3** 
FROM form_4_data AS records 
LEFT JOIN instances  ON instances.id= records.instance_id 
LEFT JOIN events ON records.instance_id = events.instance_id  
LEFT JOIN form_3_data ON records.field_111= form_3_data.instance_id  
LEFT JOIN form_1_data ON form_3_data.field_94= form_1_data.instance_id 
WHERE instances.active = 1  
ORDER BY records.instance_id DESC  
LIMIT 25,25

返回:

instance_id patient_id  field_0  field_1  field_2   field_3
83          40          Jack     Nichols  NULL      2011-04-11 20:00:00
60          8           Olive    Miller   Split     NULL
58          32          Mark     Green    NPSG      2011-03-05 20:00:00
58          32          Mark     Green    NPSG      2011-04-09 20:00:00
58          32          Mark     Green    NPSG      2011-04-02 20:00:00
58          32          Mark     Green    NPSG      2011-04-09 20:00:00
57          32          Mark     Green    MSLT      NULL
56          43          Jeremy   Stevens  NPSG      NULL
31          32          Mark     Green    MWT       2011-04-14 20:00:00
28          29          Jerry    Jones    NULL      2011-04-01 20:00:00
28          29          Jerry    Jones    NULL      2011-04-02 20:00:00
28          29          Jerry    Jones    NULL      2011-03-30 20:00:00
28          29          Jerry    Jones    NULL      2011-04-01 20:00:00
28          29          Jerry    Jones    NULL      2011-04-14 20:00:00
11           8          Olive    Miller   Split     2011-03-11 20:00:00
11           8          Olive    Miller   Split     2011-03-30 20:00:00
11           8          Olive    Miller   Split     2011-03-22 20:00:00
11           8          Olive    Miller   Split     2011-04-06 20:00:00
11           8          Olive    Miller   Split     2011-04-16 20:00:00
11           8          Olive    Miller   Split     2011-03-31 20:00:00
11           8          Olive    Miller   Split     2011-04-02 20:00:00
11           8          Olive    Miller   Split     2011-04-03 20:00:00
9            8          Olive    Miller   NPSG      2011-04-22 20:00:00
9            8          Olive    Miller   NPSG      2011-03-29 20:00:00
9            8          Olive    Miller   NPSG      2011-03-29 20:00:00

Answer 1

group_concat 不起作用，因为您的查询中没有 group by 子句。
添加一个 group by 就可以了。

SELECT SQL_CALC_FOUND_ROWS 
  records.instance_id
  ,instances.patient_id
  ,form_1_data.field_1 AS field_0
  ,form_1_data.field_2 AS field_1
  ,records.field_240 AS field_2
GROUP_CONCAT(events.start_time SEPARATOR '<br />')  AS field_3
FROM form_4_data AS records 
LEFT JOIN instances ON instances.id= records.instance_id 
LEFT JOIN events ON records.instance_id = events.instance_id  
LEFT JOIN form_3_data ON records.field_111= form_3_data.instance_id  
LEFT JOIN form_1_data ON form_3_data.field_94= form_1_data.instance_id 
WHERE instances.active=1  
GROUP BY records.instance_id /*<<-- addition */
ORDER BY records.instance_id DESC  
LIMIT 25,25