MySql 5.7 ORDER BY子句不在GROUP BY子句中，并包含非聚集列 [英] MySql 5.7 ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

问题描述

我试图弄清楚在my.ini中禁用only_full_group_by

这里是我的查询：

 选择
 p.title，
 COUNT（t.qty）AS总计
 FROM 
付款t 
 LEFT JOIN产品AS p 
 ON p.id = t.item 
 WHERE t.user = 1 
 GROUP BY t.item 
 ORDER BY t.created DESC; 
  

和表格：

付款方式：

  id用户创建的
 =================== ========= 
 1 1 1 2017-01-10 
 2 2 1 2017-01-11 
 3 3 1 2017-01-12 
 4 4 1 2017-01-13 
 5 1 1 2017-01-14 
  

产品：

  id标题已创建
 =================== ======= 
 1首先2016-12-10 
 1秒2016-12-11 
 1第三2016-12-12 
第四2016-12- 13 
  

最终结果应该是谎言：

 姓名总计
第一个2 
第二个1 
第三个1 
第四个1 
  

但是如果我将查询更改为 GROUP BY t.item，t.created 错误消失了，但我结束了五个记录而不是四个，这不是我想要的。由于我是基于item字段对项目进行分组，所以应该只有四条记录 解决方案

这是您的查询：

  SELECT p.title，COUNT（t.qty）AS total 
 ------- ^ 
 FROM支付t LEFT JOIN 
产品AS p 
 ON p.id = t.item 
 WHERE t.user = 1 
 GROUP BY t.item 
  - -------- ^ 
 ORDER BY t.created DESC; 
 --------- ^ 
  

指向地点有问题。请注意， SELECT 和 GROUP BY 指的是不同的列。在 LEFT JOIN 中，您几乎总是希望通过第一个表中的内容进行聚合，而不是第二个。 b
$ b

ORDER BY 是另一个问题。您不是通过此专栏进行汇总，因此您需要决定您想要的值。我猜测 MIN（）或 MAX（）：

  SELECT p.title，COUNT（t.qty）AS total 
 FROM payments t LEFT JOIN 
 products as p 
 ON p.id = t .item 
 WHERE t.user = 1 
 GROUP BY p.title 
 ORDER BY MAX（t.created）DESC; 
  

我还会添加 COUNT（t.qty）是可疑的。通常 qty 指的是数量，你想要的是总和： SUM（t.qty） p>

I'm trying to figure out without disabling "only_full_group_by" in my.ini

here is my query:

SELECT 
  p.title,
  COUNT(t.qty) AS total 
FROM
  payments t 
  LEFT JOIN products AS p 
    ON p.id = t.item 
WHERE t.user = 1 
GROUP BY t.item
ORDER BY t.created DESC;

and tables:

Payments:

id     item   user   created
============================
1      1      1      2017-01-10
2      2      1      2017-01-11
3      3      1      2017-01-12
4      4      1      2017-01-13
5      1      1      2017-01-14

Products:

id     title    created
==========================
1      First     2016-12-10
1      Second    2016-12-11
1      Third     2016-12-12
1      Fourth    2016-12-13

The final result should look lie:

Name    Total
First   2
Second  1
Third   1
Fourth  1

But if I change my query to GROUP BY t.item, t.created Error is gone, but I end up with five records instead of four, which is not what I want. Since I'm grouping items based on "item" field, there should be only four records

解决方案

This is your query:

SELECT p.title, COUNT(t.qty) AS total 
-------^
FROM payments t LEFT JOIN
     products AS p 
     ON p.id = t.item 
WHERE t.user = 1 
GROUP BY t.item
---------^
ORDER BY t.created DESC;
---------^

The pointed to places have issues. Notice that the SELECT and GROUP BY are referring to different column. In a LEFT JOIN, you (pretty much) always want to aggregate by something in the first table, not the second.

The ORDER BY is another problem. You are not aggregating by this column, so you need to decide which value you want. I am guessing MIN() or MAX():

SELECT p.title, COUNT(t.qty) AS total 
FROM payments t LEFT JOIN
     products AS p 
     ON p.id = t.item 
WHERE t.user = 1 
GROUP BY p.title
ORDER BY MAX(t.created) DESC;

I will also add that COUNT(t.qty) is suspect. Normally qty refers to "quantity" and what you want is the sum: SUM(t.qty).

这篇关于MySql 5.7 ORDER BY子句不在GROUP BY子句中，并包含非聚集列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！