使用Group_Concat进行多表联接，其中某些记录并非在所有表中都存在 [英] Multiple Table Joins with Group_Concat where some records don't exist in all tables

问题描述

我正在尝试进行一个非常复杂的查询(对我而言)，该查询将从主表中获取一个Description字段，然后向其附加来自相关查找表的标题和值.并非所有记录在查找表中都有记录.在接下来的问题中，我将提出其他问题，但首先要提出的问题是，仅显示所有表中具有值的记录.

I am trying to do a fairly complex (for me) query that will grab a Description field from a Main Table and then append it with titles and values from related Look-Up-Tables. Not all records have records in the Look-up tables. I'll pose further questions as subsequent questions as I go along, but to start my issue is that only those records with values in all the tables show up.

http://sqlfiddle.com/#!9/09047/13

• (空)
• 这是记录2文本
  
  颜色:
  红色
  
  水果:
  Apple
• (空)

如果我使用Concat_WS，我会得到所有记录，但是concat中的标签"会消失:

If I use Concat_WS I get all records but my 'label' in the concat disappears:

http://sqlfiddle.com/#!9/09047/16

• 这是记录1文本
  
  蓝色
  
• 这是记录2文本
  
  红色
  Apple
• 这是记录3文字
  
  
  葡萄

所以我的第一步是获取所有记录描述，无论它们存在多少个查找表，并获取显示的名称/标签.

So my first step is to get all the record descriptions regardless of how many Look-up-Tables they exist in and to get the Names/Labels displaying.

推荐答案

您似乎需要COALESCE:

Select J.id, 
  Concat(J.Description,
    COALESCE(Concat('<b>Color</b>:<br>',
         group_concat(F.Name SEPARATOR '<br>')),''),
    '<br>',
    COALESCE(Concat('<b>Fruit</b>:<br>',
         group_concat(F2.Name SEPARATOR '<br>')),'')

  ) AS output
from Main J
Left Join LUT_1 L          On J.ID=L.MainID
Left Join LUT_Names_1 F    On F.ID=L.LUT_NAME_ID
Left Join LUT_2 L2         On J.ID=L2.MainID
Left Join LUT_Names_2 F2   On F2.ID=L2.LUT_NAME_ID
Group by J.ID;

SQLFiddle演示

SQLFiddle Demo

与MySQL一样，查询本身也基于MySQL扩展.如果将其设置为 ONLY_FULL_GROUP_BY (MySQL 5.7.5及更高版本的默认设置):

As always for MySQL, the query itself is basing on MySQL extension. If you set it to ONLY_FULL_GROUP_BY (default for MySQL 5.7.5 and above):

SET sql_mode=ONLY_FULL_GROUP_BY;
-- query will return error

J.Description"不在GROUP BY中

J.Description' isn't in GROUP BY

要更正此问题，您将需要在该列上使用聚合函数，例如:MAX:

To correct this you will need to use aggregation function on that column like: MAX:

SET sql_mode=ONLY_FULL_GROUP_BY;

Select J.id, 
Concat(MAX(J.Description),
  COALESCE(Concat('<b>Color</b>:<br>',
         group_concat(F.Name SEPARATOR '<br>')),''),
  '<br>',
  COALESCE(Concat('<b>Fruit</b>:<br>',
         group_concat(F2.Name SEPARATOR '<br>')),'')

)
from Main J
Left Join LUT_1 L          On J.ID=L.MainID
Left Join LUT_Names_1 F    On F.ID=L.LUT_NAME_ID
Left Join LUT_2 L2         On J.ID=L2.MainID
Left Join LUT_Names_2 F2   On F2.ID=L2.LUT_NAME_ID
Group by J.ID;

这篇关于使用Group_Concat进行多表联接，其中某些记录并非在所有表中都存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！