组连接查询问题

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 

Answer 2

如果您只是尝试向日期添加新行，那么 GROUP_CONCAT 并不是您真正需要的。它是一个聚合函数，与 GROUP BY 一起工作，用于连接分组块中不同行的字符串。

如果我们有一个表 week:

week weekday
1    Monday
1    Tuesday
1    Friday
2    Monday
2    Sunday

查询

SELECT week, GROUP_CONCAT( weekday SEPARATOR '<br />' ) as days
FROM weeks
GROUP BY week

将为您提供：

week days
1    Monday<br />Tuesday<br />Friday
2    Monday<br />Sunday

我相信您正在寻找的是一个简单的 CONCAT:

SELECT week, CONCAT( weekday,'<br />' ) as day
FROM weeks

将为您提供：

week day
1    Monday<br />
1    Tuesday<br />
1    Friday<br />
2    Monday<br />
2    Sunday<br />