MySQL关联两表实现行转列 按tid判断输出Yes/No的查询方法

涉及表结构 #

• table_1：主信息表，字段如下
  • tid：主体唯一ID
  • name：主体名称
  • pic：主体图片标识
    样例数据：tid=1对应name=name1、pic=XXX；tid=2对应name=name2、pic=ZZZ；tid=3对应name=name3、pic=YYY
• table_2：关联文件表，字段如下
  • fid：文件唯一ID
  • fname：文件名
  • tid：关联主表的主体ID
  • desc：文件类型标识
    样例数据：fid=1对应fname=abcd1、tid=1、desc=File1；fid=2对应fname=abcd2、tid=1、desc=File2；fid=3对应fname=xyze3、tid=2、desc=File1

核心需求 #

以tid为关联键关联两张表，将table_2中desc字段的固定枚举值File1、File2转为独立列，对应tid下存在匹配desc的记录时列值返回Yes，不存在则返回No，最终返回字段包含tid、name、pic、File1、File2。

可直接运行的查询语句 #

SELECT
  t1.tid,
  t1.name,
  t1.pic,
  IF(MAX(t2.`desc` = 'File1') = 1, 'Yes', 'No') AS `File1`,
  IF(MAX(t2.`desc` = 'File2') = 1, 'Yes', 'No') AS `File2`
FROM table_1 t1
LEFT JOIN table_2 t2
  ON t1.tid = t2.tid
GROUP BY t1.tid, t1.name, t1.pic;

语句说明 #

1. 采用LEFT JOIN保证table_1中没有关联文件记录的tid（比如样例中的tid=3）不会被过滤掉
2. desc是MySQL保留关键字，书写时必须用反引号包裹，否则会触发语法错误
3. 分组后用MAX()判断是否存在匹配记录：分组内只要有一条记录满足desc值匹配，对应表达式结果就为1，否则为0，刚好匹配存在即返回Yes的逻辑
4. 用IF函数替代CASE WHEN写法更简洁，两种写法执行效果完全一致

预期返回结果 #

内容的提问来源于stack exchange，提问作者wawan