SELECT MIN( id ) AS id, max( stand_id ) AS stand_id, max( edds_stand_id ) AS edds_stand_id, MAX( stand_id ) AS d FROM pz_item_synonym GROUP BY item_code, item_name, subgroup_type 根据id将原表数据更新

你可以使用以下 SQL 语句来更新原表数据：

UPDATE pz_item_synonym t1
INNER JOIN (
    SELECT MIN(id) AS id, MAX(stand_id) AS stand_id, MAX(edds_stand_id) AS edds_stand_id, MAX(stand_id) AS d
    FROM pz_item_synonym
    GROUP BY item_code, item_name, subgroup_type
) t2
ON t1.id = t2.id
SET t1.stand_id = t2.stand_id,
    t1.edds_stand_id = t2.edds_stand_id,
    t1.d = t2.d;

这条 SQL 语句会按照 `item_code`、`item_name` 和 `subgroup_type` 进行分组，并选取每组中的最小 `id`，以及最大的 `stand_id`、`edds_stand_id` 和 `d`。然后将选取出来的这些字段的值更新到原表中对应的记录上。

相关问题

p2.connector_brand_name, p2.element_brand_name, p4.item_code, p4.k1_e_status from pz_k1_e12_item p1 left join pz_k1_e07_item p2 on p1.e07_id = p2.id left join pz_k1_e07 p3 on p2.harness_id = p3.id left join pz_k1_e02 p4 on p4.id = p1.e02_id where p3.vehicle_code='SX11AD-2021' GROUP BY p2.element_brand_name,p2.connector_code > 1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'edds.p3.harness_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by > 时间: 0.005s

根据你提供的 SQL 语句，错误信息显示在第 1 行，在 SELECT 子句中查询了一个未加入 GROUP BY 子句的列 p3.harness_name，需要修改。此外，你的 MySQL 数据库设置了 sql_mode=only_full_group_by，表示只有在 SELECT 语句中的列名都出现在 GROUP BY 子句中时，才能执行查询，因此还需要修改查询语句。如果你想查询 p2.connector_brand_name、p2.element_brand_name、p4.item_code 和 p4.k1_e_status 列，可以将它们加入 SELECT 子句和 GROUP BY 子句中，如下所示：

SELECT p2.connector_brand_name, p2.element_brand_name, p4.item_code, p4.k1_e_status 
FROM pz_k1_e12_item p1 
LEFT JOIN pz_k1_e07_item p2 ON p1.e07_id = p2.id 
LEFT JOIN pz_k1_e07 p3 ON p2.harness_id = p3.id 
LEFT JOIN pz_k1_e02 p4 ON p4.id = p1.e02_id 
WHERE p3.vehicle_code='SX11AD-2021' 
GROUP BY p2.connector_brand_name, p2.element_brand_name, p4.item_code, p4.k1_e_status;

这样就可以查询到 p2.connector_brand_name、p2.element_brand_name、p4.item_code 和 p4.k1_e_status 列，并且不会有 GROUP BY 子句的错误了。

Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'edds.p3.harness_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这是一个 SQL 错误，通常是由于 MySQL 的版本不同或者相关的 sql_mode 设置不同导致的。该错误的含义是在 GROUP BY 语句中出现了不在 GROUP BY 子句中的非聚合列，例如这里的 p3.harness_name 字段。解决这个问题的方法是将所有非聚合列都添加到 GROUP BY 子句中，或者在 MySQL 的配置文件中将 sql_mode 设置为非 only_full_group_by 模式。