mysql - 不使用 GROUP BY 从重复数据中获取唯一记录

Question

我对GROUP BY有一些问题。由于某种原因，我无法使用它。所以我正在寻找一些替代解决方案。我正在使用在线虚拟主机，并且使用GROUP BY我收到此错误：

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'denieuwe_db.guest.gid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Bit Intro：我正在制作一个餐厅网站，在Guest表中我有所有唯一的guest_code。在购买表内有客人的所有购买，购买ID和客人代码

表：采购

+------------------------+
|purchaseid | guest_code | 
+------------------------+
|    1      | CUST7408668|   
|    2      | CUST7408668|    
|    3      | CUST4425874| 
|    4      | CUST4425874|   
|    5      | CUST4425874|  
+------------------------+

表：客人

    +-----------------------------------+
    |   gid     | guest_code |   Name   |
    +-----------------------------------+
    |    1      | CUST7408668|    Mia   |     
    |    5      | CUST4425874|    zoi   |
    +------------------------+----------+

SQL：

     SELECT purchase.purchaseid,
            purchase.guest_code, 
            guest.guest_code,
            guest.name,    
     FROM purchase
     INNER JOIN guest ON purchase.guest_code=guest.guest_code GROUP BY purchase.guest_code;

我想要的结果：

+-----------------------------------+--------------------+
|purchaseid | guest_code |   name   |  gid  | guest_code | 
+-----------------------------------+--------------------+
|    1      | CUST7408668|    Mia   |   1   | CUST7408668|
|    3      | CUST4425874|    zoi   |   5   | CUST4425874|
+------------------------+----------+--------------------+

我在本地主机上使用 GROUP BY 得到了准确的结果，但在我的主机上遇到了问题。所以我正在寻找替代解决方案。任何帮助表示赞赏

标签： mysqlsql

Answer 1

您似乎想要最低购买 ID。你可以使用min()它。您可能还需要GROUP BY使用其他列扩展该子句。

SELECT min(purchase.purchaseid) purchaseid,
       purchase.guest_code,
       guest.guest_code,
       guest.name
       FROM purchase
            INNER JOIN guest
                       ON purchase.guest_code = guest.guest_code
       GROUP BY purchase.guest_code,
                guest.guest_code,
                guest.name;