时间戳

首页 > 解决方案 > MySQL LEFT OUTER JOIN 选择最佳匹配而不更改 sql_mode

mysql - MySQL LEFT OUTER JOIN 选择最佳匹配而不更改 sql_mode

问题描述

我正在尝试从 subject_inv 表中获取具有最佳匹配主题的学生列表。我的查询的问题是它需要更改 sql_mode。有没有办法在不更改 sql_mode 参数的情况下修改此查询。

    SELECT `student`.*, `subject_inv`.`subject_name`, `score`.`custom_score`, 
    MIN(
    CASE WHEN (`student`.`subject` = `subject_inv`.`subject_name`) THEN 1 WHEN (`student`.`topic1` = `subject_inv`.`subject_name`) THEN 2 WHEN (`student`.`topic2` = `subject_inv`.`subject_name`) THEN 3 WHEN (`student`.`topic3` = `subject_inv`.`subject_name`) THEN 4 END
    ) AS priority 
    FROM `student` 
LEFT OUTER JOIN `subject_inv` ON `subject_inv`.`subject_name`=`student`.`subject` OR `subject_inv`.`subject_name` = `student`.`topic1` OR `subject_inv`.`subject_name` = `student`.`topic2` OR `subject_inv`.`subject_name` = `student`.`topic3` 
LEFT OUTER JOIN `score` ON `student`.`id`=`score`.`id` GROUP BY `student`.`id`, priority

它给了我以下错误。当我将 sql_mode 从“only_full_group_by”更改为其他时，它可能应该修复。

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.student.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

有没有办法在不改变 sql_mode 的情况下得到这个结果？

student
id | full_name | subject | topic1 | topic2 | topic3
___________________________________________________
1  | tom       | sbj1    | sbj4   |        |
2  | sam       | sbj3    | sbj7   |        |
3  | ron       | sbj6    | sbj2   |        |    

subject_inv
id | subject_name | tutor
__________________________
1  | sbj1         | tut1
2  | sbj7         | tut2
3  | sbj4         | tut3
4  | sbj9         | tut3

score
id | custom_score
__________________
1  | 10         
2  | 6         
3  | 9         
4  | 4

预期成绩：：：

id | full_name | subject | topic1 | topic2 | topic3 | subject_name | custom_score
________________________________________________________________________________
1  | tom       | sbj1    | sbj4   |        |        | sbj1         | 10
2  | sam       | sbj3    | sbj7   |        |        | sbj7         | 6
3  | ron       | sbj6    | sbj2   |        |        | NULL         | 9

标签： mysqlsql-mode

解决方案

您可以将内部联接与 MIN 的子选择一起使用，例如： 

 SELECT distinct  `student`.*, `subject_inv`.`subject_name`, `score`.`custom_score`
 , t.priority 
  FROM `student` 
  INNER JOIN (

    select  `student`.`id`, MIN(
        CASE  WHEN (`student`.`subject` = `subject_inv`.`subject_name`) THEN 1 
              WHEN (`student`.`topic1` = `subject_inv`.`subject_name`) THEN 2 
              WHEN (`student`.`topic2` = `subject_inv`.`subject_name`) THEN 3 
              WHEN (`student`.`topic3` = `subject_inv`.`subject_name`) THEN 4 END
        ) AS priority 
        FROM `student`
        LEFT OUTER JOIN `subject_inv` ON `subject_inv`.`subject_name`=`student`.`subject` 
            OR `subject_inv`.`subject_name` = `student`.`topic1` 
              OR `subject_inv`.`subject_name` = `student`.`topic2` 
                OR `subject_inv`.`subject_name` = `student`.`topic3`
    group by `student`.`id`
  ) t on t.`id` = `student`.`id`
  LEFT OUTER JOIN `subject_inv` ON `subject_inv`.`subject_name`=`student`.`subject` 
      OR `subject_inv`.`subject_name` = `student`.`topic1` 
        OR `subject_inv`.`subject_name` = `student`.`topic2` 
          OR `subject_inv`.`subject_name` = `student`.`topic3` 
  LEFT OUTER JOIN `score` ON `student`.`id`=`score`.`id`

但是对于 sql_mode=only_full_group_by
如果您需要不在组中的 outhers 列，您也应该使用聚合函数

 SELECT  `student`.*
 , min(`subject_inv`.`subject_name`)
 , min(`score`.`custom_score`)
 , t.priority 
  FROM `student` 
  INNER JOIN (

    select  `student`.`id`, MIN(
        CASE  WHEN (`student`.`subject` = `subject_inv`.`subject_name`) THEN 1 
              WHEN (`student`.`topic1` = `subject_inv`.`subject_name`) THEN 2 
              WHEN (`student`.`topic2` = `subject_inv`.`subject_name`) THEN 3 
              WHEN (`student`.`topic3` = `subject_inv`.`subject_name`) THEN 4 END
        ) AS priority 
        FROM `student`
        LEFT OUTER JOIN `subject_inv` ON `subject_inv`.`subject_name`=`student`.`subject` 
            OR `subject_inv`.`subject_name` = `student`.`topic1` 
              OR `subject_inv`.`subject_name` = `student`.`topic2` 
                OR `subject_inv`.`subject_name` = `student`.`topic3`
    group by `student`.`id`
  ) t on t.`id` = `student`.`id`
  LEFT OUTER JOIN `subject_inv` ON `subject_inv`.`subject_name`=`student`.`subject` 
      OR `subject_inv`.`subject_name` = `student`.`topic1` 
        OR `subject_inv`.`subject_name` = `student`.`topic2` 
          OR `subject_inv`.`subject_name` = `student`.`topic3` 
  LEFT OUTER JOIN `score` ON `student`.`id`=`score`.`id` 
  GROUP BY `student`.`id`, t.priority

推荐阅读