数据表
table 1
CREATE TABLE `sn` (
`DayID` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '日期号',
`StoreCode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '门店编码',
`DisCode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '经销商编号',
`OnOffstore` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应线上/线下门店',
`SubBrandAbbr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供货系列',
KEY `ind_StoreCode` (`StoreCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
table 2
CREATE TABLE `mdm` (
`storedistributor_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`storecode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '门店编码',
`discode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '经销商编号',
`onoffstore` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应线上/线下门店',
`subbrandabbr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供货系列',
PRIMARY KEY (`storedistributor_id`),
KEY `ind_storecode` (`storecode`)
) ENGINE=InnoDB AUTO_INCREMENT=131071 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
需求和注意点
1. 将sn表指定DayID的数据同步到mdm表
2. 其中StoreCode、DisCode、SubBrandAbbr(存在NULL值) 三个字段合并唯一
3. SubBrandAbbr存在NULL值
4. mdm已存在的数据不做任何操作,不存在的数据mdm做新增
实现
INSERT INTO mdm ( `storecode`, `discode`, `onoffstore`, `subbrandabbr` ) SELECT
sn.`StoreCode`,
sn.`DisCode`,
sn.`OnOffstore`,
sn.`SubBrandAbbr`
FROM
sn
LEFT JOIN mdm ON sn.`StoreCode` = mdm.`storecode`
AND sn.`DisCode` = mdm.`discode`
AND IFNULL( sn.SubBrandAbbr, '' ) = IFNULL( mdm.subbrandabbr, '' )
WHERE
mdm.storedistributor_id IS NULL
AND sn.`DayID` ='20230912'