mysql 5.7group by查询非,MySql 5.7 ORDER BY子句不在GROUP BY子句中，并包含非聚集列

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).