mysql duplicate_排除mysql 8.0中的重复项[duplicate](Exclude duplicates in mysql 8.0 [duplicate])...

before upgrading to MySQL 8.0 I used to use GROUP BY to do not select duplicates but now I’m getting:

1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘database.orders.ID’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.028000s

I know that I could change mysql mode as follows:

mysql -u root -p

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But I would like to keep the current mode but fix the problem.

This MySQL query Works:

SELECT Country FROM orders GROUP BY Country

+---------+

| Country |

+---------+

| USA |

| UK |

+---------+

But when I add another MySQL column such as ID:

mysql> SELECT Country,ID FROM orders GROUP BY Country;

The error appears, as someone suggest before I have to add all select columns to GROUP BY so the query becomes

SELECT Country,ID FROM orders GROUP BY Country, ID

But that doesn’t remove the duplicates and it shows all the countries.

Expected Result is:

mysql> SELECT Country,ID,userID FROM orders GROUP BY Country;

+------+---------+------------+

| ID | Country | UserID |

+------+---------+------------+

| 51 | USA | 99 |

| 61 | UK | 12 |

+------+---------+------------+

Note: columns names are just for simplicity and in my case each “Country” has same “UserID”