MySQL Forums
Forum List  »  Chinese

请问这个查询的1055错误的导致原因
Posted by: Kuandian Gan
Date: October 11, 2024 01:22AM

数据库版本:mysql Ver 8.4.2 for Linux on x86_64 (MySQL Community Server - GPL)
涉及表结构:
t_student
+-----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+----------------+
| sno | int | NO | PRI | NULL | auto_increment |
| sname | varchar(6) | NO | | NULL | |
| ssex | char(1) | NO | | NULL | |
| sbirthday | date | NO | | NULL | |
| class | varchar(5) | NO | | NULL | |
+-----------+------------+------+-----+---------+----------------+

t_score
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| sno | int | NO | PRI | NULL | |
| cno | int | NO | PRI | NULL | |
| degree | float(4,1) | NO | | NULL | |
+--------+------------+------+-----+---------+-------+

t_course
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| cno | int | NO | PRI | NULL | auto_increment |
| cname | varchar(50) | NO | | NULL | |
| tno | int | NO | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+

t_teacher
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| tno | int | NO | PRI | NULL | auto_increment |
| tname | varchar(6) | NO | | NULL | |
| tsex | char(1) | NO | | NULL | |
| tbirthday | date | NO | | NULL | |
| prof | varchar(20) | YES | | NULL | |
| depart | varchar(20) | NO | | NULL | |
+-----------+-------------+------+-----+---------+----------------+

然后创建了一个视图,这是创建时的语法:
create or replace view v_score_info as
select s.*, c.cno, c.cname, t.*, sc.degree from t_student s
join t_score sc on s.sno=sc.sno
join t_course c on sc.cno=c.cno
join t_teacher t on c.tno=t.tno;

系统记录的view创建语法是:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_score_info` AS select `s`.`sno` AS `sno`,`s`.`sname` AS `sname`,`s`.`ssex` AS `ssex`,`s`.`sbirthday` AS `sbirthday`,`s`.`class` AS `class`,`c`.`cno` AS `cno`,`c`.`cname` AS `cname`,`t`.`tno` AS `tno`,`t`.`tname` AS `tname`,`t`.`tsex` AS `tsex`,`t`.`tbirthday` AS `tbirthday`,`t`.`prof` AS `prof`,`t`.`depart` AS `depart`,`sc`.`degree` AS `degree` from (((`t_student` `s` join `t_score` `sc` on((`s`.`sno` = `sc`.`sno`))) join `t_course` `c` on((`sc`.`cno` = `c`.`cno`))) join `t_teacher` `t` on((`c`.`tno` = `t`.`tno`)))

我所执行的语句:
select * from t_student where date_format(sbirthday, '%m-%d') in
(select date_format(sbirthday, '%m-%d') from v_score_info where prof='教授' group by date_format(sbirthday, '%m-%d') having count(*) > 1);

报错信息:
1055 - Expression #1 of HAVING clause is not in GROUP BY clause and contains nonaggregated column 'study.v_score_info.sbirthday' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在我的知识面下完全不认为这样会报错,其子查询的部分我单独执行没有问题;而外部的查询我在in后构建了一个固定数据,也可以执行;然后我整体执行认为报错是系统混淆了内外的字段但是通过取别名也没有解决,故请求帮助。

Options: ReplyQuote


Subject
Views
Written By
Posted
请问这个查询的1055错误的导致原因
168
October 11, 2024 01:22AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.