第一种:insert 所添加的参数全部来自select查询的结果集
案例:
1、全部字段都是select查询的结果(不需要添加 VALUES),并且所查询的列 是 作为所添加字段的values的值
INSERT INTO crm_promotion_orders(PROMOTIONORDERS_ID,PHONE,ORDER_ID)
select tb.CUSTOMER_UUID, tb.CUSTOMER_MOBILE,tb.PROJECT_ID from(SELECT ccm.CUSTOMER_UUID,ccm.CUSTOMER_MOBILE,ccm.PROJECT_ID FROM crm_customer_manage ccm
where ccm.PROJECT_ID = '1429006975654264834' and ccm.CUSTOMER_MOBILE = '13773262930') AS tb
第二种:(相对复杂点)insert 所添加的参数是(前端)所传入的字段和来自select查询的结果集
1、只有一个字段是select查询的结果(需要添加 VALUES),并且所查询的这一个列和所传入的字段拼接成values的值
INSERT INTO crm_promotion_orders(PROMOTIONORDERS_ID,PHONE,ORDER_ID)
VALUES(10111111 , (SELECT ccm.CUSTOMER_MOBILE FROM crm_customer_manage ccm where ccm.CUSTOMER_UUID = '6b316e018a4a4bf89b0510953f416e8c'),10000111)
如下图
2、有多个字段是select查询的结果(不需要添加 VALUES),并且所查询的多个列和所传入的字段拼接成values的值,select所查询的列要放入子查询里面,并且外面嵌套select中要拼接前端所传入的参数,将前端传入的参数和select查询的多个列拼接成insert所需要的values的值;
INSERT INTO crm_promotion_orders
( PROMOTIONORDERS_ID, ORDER_ID, ORDER_TIME, TOTAL_PRICE, PROJECT_ID,PAID_PRICE,
ORDER_HEADER,
PHONE
) SELECT 101000, 2, '2021-09-04 15:24:45', 4, '1429006975654264834',6,
tb.CUSTOMER_UUID,tb.CUSTOMER_MOBILE FROM (select ccm.CUSTOMER_UUID,ccm.CUSTOMER_MOBILE from crm_customer_manage ccm where
ccm.PROJECT_ID = '1429006975654264834' and ccm.CUSTOMER_MOBILE = '13773262930') as tb;
如下如(括号里面是子查询外面拼接前端传入去其他参数)
表结构sql (crm_promotion_orders)
CREATE TABLE `crm_promotion_orders` (
`PROMOTIONORDERS_ID` varchar(36) NOT NULL COMMENT '主键',
`ORDER_ID` varchar(36) DEFAULT NULL COMMENT '订单号',
`ORDER_TIME` datetime DEFAULT NULL COMMENT '下单时间',
`TOTAL_PRICE` decimal(12,2) DEFAULT NULL COMMENT '订单总金额',
`PROJECT_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '项目ID',
`PAID_PRICE` decimal(12,2) DEFAULT NULL COMMENT '已付款金额',
`ORDER_HEADER` varchar(36) DEFAULT NULL COMMENT '订单归属人(客户id)',
`PHONE` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '手机号',
`SHOP_UUID` varchar(255) DEFAULT NULL COMMENT '所属店铺',
`PLATFORM` varchar(255) DEFAULT NULL COMMENT '所属平台',
`DATETYPE` int(1) DEFAULT '0' COMMENT '是否删除:0否1是;0否',
`CREATE_USER` varchar(36) CHARACTER SET latin1 DEFAULT NULL COMMENT '添加人',
`CREATE_USER_NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '创建人名称',
`CREATE_TIME` datetime DEFAULT NULL COMMENT '添加时间',
`UPDATE_USER` varchar(36) CHARACTER SET latin1 DEFAULT NULL COMMENT '修改人',
`UPDATE_USER_NAME` varchar(36) CHARACTER SET latin1 DEFAULT NULL COMMENT '修改人名称',
`UPDATE_TIME` datetime DEFAULT NULL COMMENT '修改时间',
`DATATYPE` int(1) DEFAULT NULL COMMENT '数据类型0有效 1无效',
PRIMARY KEY (`PROMOTIONORDERS_ID`) USING BTREE,
KEY `ORDER_HEADER` (`ORDER_HEADER`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='推广订单表';
表结构sql (crm_promotion_orders)
CREATE TABLE `crm_customer_manage` (
`CUSTOMER_UUID` varchar(36) NOT NULL COMMENT '客户uuid',
`CUSTOMER_NAME` varchar(60) DEFAULT NULL COMMENT '客户姓名',
`CUSTOMER_SEX` int(1) DEFAULT NULL COMMENT '性别',
`CUSTOMER_BIRTHDAY` datetime DEFAULT NULL COMMENT '出生日期',
`CUSTOMER_MOBILE` varchar(20) DEFAULT NULL COMMENT '手机',
`CUSTOMER_PHONE` varchar(20) DEFAULT NULL COMMENT '电话',
`CUSTOMER_PROVINCE_UUID` varchar(360) DEFAULT NULL COMMENT '州/省',
`CUSTOMER_ADDRESS` varchar(511) DEFAULT NULL COMMENT '详细地址',
`CUSTOMER_WECHAT` varchar(100) DEFAULT NULL COMMENT '微信',
`CUSTOMER_QQ` varchar(15) DEFAULT NULL COMMENT 'qq',
`CUSTOMER_EMAIL` varchar(63) DEFAULT NULL COMMENT '邮箱',
`CUSTOMER_PRINCIPAL` varchar(36) DEFAULT NULL COMMENT '负责人',
`CUSTOMER_BATCHUPDATEPRI` varchar(36) DEFAULT NULL COMMENT '批量修改负责人',
`CUSTOMER_P_WECHAT` varchar(63) DEFAULT NULL COMMENT '负责人微信',
`CUSTOMER_INTENDEDPRO` varchar(4000) DEFAULT NULL COMMENT '意向产品',
`CUSTOMER_REMARK` varchar(500) DEFAULT NULL COMMENT '备注',
`CUSTOMER_SIGN` int(3) DEFAULT '1' COMMENT '标志',
`CONTINENT_CHANNELLABELS` varchar(36) DEFAULT NULL COMMENT '渠道标签选择',
`CUSTOMER_UPDATESIGN` int(1) DEFAULT '1' COMMENT '修改标志; 1未修改 2已修改 跟踪使用',
`ALLOCATION_TIME` datetime DEFAULT NULL COMMENT '分配时间',
`CUSTOMERY_TYPE` int(1) DEFAULT '0' COMMENT '客户类型;0其他客户 1国际客户',
`CUSTOMER_COUNTRY` varchar(36) DEFAULT NULL COMMENT '国家',
`ISCHANGED` varchar(25) DEFAULT '0 ' COMMENT '是否已修改',
`CUSTOMER_COUNTY` varchar(255) DEFAULT NULL COMMENT '国家(县)',
`CUSTOMER_INS` varchar(50) DEFAULT NULL COMMENT 'ins账号',
`CUSTOMER_FACEBOOK` varchar(50) DEFAULT NULL COMMENT 'facebook账号',
`CUSTOMER_WHATSAPP` varchar(50) DEFAULT NULL COMMENT 'WhatsApp账号',
`LAST_PRINCIPAL` varchar(255) DEFAULT NULL COMMENT '回收前负责人',
`CREATE_USER` varchar(36) DEFAULT NULL COMMENT '添加人',
`CREATE_USER_NAME` varchar(36) DEFAULT NULL COMMENT '创建人名称',
`CREATE_TIME` datetime DEFAULT NULL COMMENT '添加时间',
`UPDATE_USER` varchar(36) DEFAULT NULL COMMENT '修改人',
`UPDATE_USER_NAME` varchar(36) DEFAULT NULL COMMENT '修改人名称',
`UPDATE_TIME` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`DATATYPE` int(1) DEFAULT NULL COMMENT '数据类型0有效 1无效',
`SHOP_ID` varchar(36) DEFAULT NULL COMMENT '店铺ID',
`SHOP_NAME` varchar(100) DEFAULT NULL COMMENT '店铺名',
`ORDER_NUM` varchar(100) DEFAULT NULL COMMENT '订单号',
`ORDER_PRICE` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
`PROJECT_ID` varchar(36) DEFAULT NULL COMMENT '项目id',
`PROJECT_NAME` varchar(255) DEFAULT NULL COMMENT '项目名称',
KEY `customer_name` (`CUSTOMER_NAME`) USING BTREE,
KEY `C_CHANNELLABEL_M` (`CONTINENT_CHANNELLABELS`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='客户管理表';
-- 添加测试数据
INSERT INTO `crm_customer_manage` VALUES ('6b3d05389140486986bfbc05851a3cb0', '姓名', NULL, '2021-09-03 16:00:00', '13773262930', '', '1422796917127856130', '详细地址', '', '', '', '1431072634502090754', '1431072634502090754', NULL, NULL, '', 1, '2d8bd79b0c0e4c1aa75ec59217f2bb28', 1, NULL, 0, '1423903864577150978', '0 ', NULL, '', '', '', '5b41bb1c4cf94387a498b8c8e1553c1e', NULL, NULL, '2021-07-29 00:01:43', 'liyunpeng', NULL, '2021-09-04 07:03:32', NULL, NULL, NULL, NULL, NULL, '1429006975654264834', NULL);