问题描述
我有三个具有外键关系的数据表，但是当我执行查询时，其给出的错误类似于 Syntax error or access conflict: 1055 Expression #22 of SELECT list is not in GROUP BY 子句并且包含非聚合列invoices.imported'
I have three tables of data with foreign key relationship but when i do a query its giving error like as Syntax error or access violation: 1055 Expression #22 of SELECT list is not in GROUP BY clause and contains nonaggregated column invoices.imported'
下面是我的表格结构
jobs
id invoice_no customer_id
1 H045 1
2 A041 2
job_detials
id job_id est_start_date est_off_date
1 1 04-01-2017 01-01-2017
2 1 05-02-2017 03-06-2017
3 1 01-05-2017 17-04-2017
4 2 14-04-2017 15-02-2017
5 2 12-04-2017 03-04-2017
invoices
id job_id imported
1 1 1
2 2 0
以及我试图执行结果的查询.
And the query which i m trying to execute the results.
select `jobs`.`id` as `job_id`, `invoices`.`imported`,
MIN(job_details.est_start_date) as est_start_date, MAX(job_details.est_off_date) as est_off_date, `jobs`.`invoice_no` from `jobs`
left join `job_details` on `job_details`.`job_id`= `jobs`.`id` left join `invoices` on `invoices`.`job_id` = `jobs`.`id`
group by `jobs`.`id`
有人能帮我解决这个错误吗??谢谢
Can some body help me out to solve this error. ? thank you
推荐答案
在许多 SQL 数据库中，您必须 GROUP BY 所有非聚合字段；在这种情况下，您需要按job_id、imported 和invoice_no 进行分组.
In many SQL databases, you must GROUP BY all non-aggregated fields; in this case, you need to group by job_id, imported, and invoice_no.
在某些配置下，MySQL 确实允许您省略此类字段，但最近的默认设置倾向于限制此类语法，因为选择了诸如 invoices.imported 之类的内容，即不是主键分组的表的一部分，不分组可能会产生不确定的结果.
Under certain configurations MySQL does allow you to omit such fields, but the more recent defaults tend toward restricting such syntax since selecting something such as invoices.imported, that is not a part of a table whose primary key was grouped on, without grouping on it can have non-deterministic results.
这篇关于Mysql 三表连接聚合列和分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！