mysql查询表中数据,根据pcode和tcode两个字段进行分组,合并code字段
SELECT pcode, tcode, GROUP_CONCAT(code SEPARATOR ',') AS codes
FROM your_table
GROUP BY pcode, tcode;
这个查询会根据pcode
和tcode
的值对数据进行分组,并将同一组内的code
字段值合并为一个由逗号分隔的字符串。记得将your_table
替换为你的实际表名。
mysql 将select 到的数据 insert 到新表中
要将一个SELECT
查询的结果插入到新表中,可以使用INSERT INTO ... SELECT ...
语句。以下是一个例子:假设我们有一个现有表existing_table
,它有两列id
和data
,我们想要将所有数据复制到一个新表new_table
中
CREATE TABLE new_table LIKE existing_table;
INSERT INTO new_table
SELECT * FROM existing_table;
如果你只想复制特定的列,可以指定列名:
INSERT INTO new_table (id, data)
SELECT id, data FROM existing_table;
如果new_table
尚未创建,可以在INSERT
语句中直接创建:(会创建一个新表new_table
并将existing_table
中满足条件的行插入到新表中。)
INSERT INTO new_table (id, data)
SELECT id, data
FROM existing_table
WHERE id = 1; -- 可选的条件
综合以上知识,真是使用案例:
#查看表中所有字段
#SHOW FULL COLUMNS FROM test1;
CREATE TABLE new_test1 LIKE test1;
INSERT ignore INTO new_test1 (id,personType,weight,`type`,`time`,regiterTime,regiterUser,channelCode,num,boxType
,state,updateTime,updateUser,destination,siteCode,currentAddress,source,`desc`,eImg
,personCode,transCode,code)
SELECT ANY_VALUE(id) as id,
ANY_VALUE(personType) as personType,
ANY_VALUE(weight) as weight,
ANY_VALUE(`type`) as `type`,
ANY_VALUE(`time`) as `time`,
ANY_VALUE(regiterTime) as regiterTime,
ANY_VALUE(regiterUser) as regiterUser,
ANY_VALUE(channelCode) as channelCode,
ANY_VALUE(num) as num,
ANY_VALUE(boxType) as boxType,
ANY_VALUE(state) as state,
ANY_VALUE(updateTime) as updateTime,
ANY_VALUE(updateUser) as updateUser,
ANY_VALUE(destination) as destination,
ANY_VALUE(siteCode) as siteCode,
ANY_VALUE(currentAddress) as currentAddress,
ANY_VALUE(source) as source,
ANY_VALUE(`desc`) as `desc`,
ANY_VALUE(eImg) as eImg,personCode, transCode, GROUP_CONCAT(code SEPARATOR ',') AS code
FROM test1
GROUP BY personCode, transCode;