mysql left outer join选择最佳匹配而不更改sql\u模式

pu3pd22g 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(402)

我正在试着从subject\u inv表中得到一个学生名单，上面有他们最匹配的科目。我的查询的问题是需要更改sql\u模式。有没有一种方法可以在不更改sql\u 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模式从“only\u full\u group\u 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\u模式的情况下得到这个结果？

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

mysql sql-mode

来源：https://stackoverflow.com/questions/51914917/mysql-left-outer-join-select-best-match-without-changing-sql-mode

2条答案

按热度按时间

des4xlb01#

我想知道这个查询是如何使用coalesce进行优先级排序的（以及它是否产生了预期的结果）。注意，我已经重命名了这些表。

drop table if exists st,su,sc;
create table st(id int, full_name varchar(20), subject varchar(20), topic1 varchar(20), topic2 varchar(20), topic3 varchar(20));
insert into st values
(1  , 'tom'       , 'sbj1'    , 'sbj4'   , null,null),
(2  , 'sam'       , 'sbj3'    , 'sbj7'   , null,null),       
(3  , 'ron'       , 'sbj6'    , 'sbj2'   , null,null);

create table su(id int, subject_name varchar(20), tutor varchar(20));
insert into su values
(1  , 'sbj1'         , 'tut1'),
(2  , 'sbj7'         , 'tut2'),
(3  , 'sbj4'         , 'tut3'),
(4  , 'sbj9'         , 'tut3');

create table sc(id int, custom_score int);
insert into sc values
(1  , 10),         
(2  , 6  ),       
(3  , 9  ),       
(4  , 4);

select  st.id,st.full_name,st.subject,st.topic1,st.topic2,st.topic3,
        coalesce((select su.subject_name from su where su.subject_name = st.subject) ,
                (select su.subject_name from su where su.subject_name = st.topic1) ,
                (select su.subject_name from su where su.subject_name = st.topic2) ,
                (select su.subject_name from su where su.subject_name = st.topic3)
                )  subjectname,
         custom_score
from st
left join sc on sc.id = st.id;

+------+-----------+---------+--------+--------+--------+-------------+--------------+
| id   | full_name | subject | topic1 | topic2 | topic3 | subjectname | custom_score |
+------+-----------+---------+--------+--------+--------+-------------+--------------+
|    1 | tom       | sbj1    | sbj4   | NULL   | NULL   | sbj1        |           10 |
|    2 | sam       | sbj3    | sbj7   | NULL   | NULL   | sbj7        |            6 |
|    3 | ron       | sbj6    | sbj2   | NULL   | NULL   | NULL        |            9 |
+------+-----------+---------+--------+--------+--------+-------------+--------------+
3 rows in set (0.02 sec)

赞(0）回复(0）举报 2021-06-20

k97glaaz2#

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

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模式=仅\u full\u group by
如果你需要一个不在分组中的列，你也应该使用aggregation函数

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

赞(0）回复(0）举报 2021-06-20

我来回答

mysql left outer join选择最佳匹配而不更改sql\u模式

2条答案

相关问题

热门标签

最新问答