elab_mvp/resources/mvp.sql

CREATE TABLE `mvp_crowd_info_module` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `crowd_info_id` int(11) DEFAULT NULL COMMENT '用户人群信息id',
  `module_name` varchar(50) DEFAULT NULL COMMENT '模块名称',
  `default_value` float(5,2) DEFAULT NULL COMMENT '原始数值',
  `standard_value` float(5,2) DEFAULT NULL COMMENT '标准值',
  `content` varchar(50) DEFAULT NULL COMMENT '内容',
  `url` varchar(300) DEFAULT NULL COMMENT '链接',
  `remarks` varchar(500) DEFAULT NULL COMMENT '备注',
  `status` int(11) DEFAULT '1' COMMENT '1:EFFECTIVE:有效 -1:INVALID:无效',
  `created` datetime DEFAULT NULL COMMENT '创建时间',
  `creator` varchar(100) DEFAULT NULL COMMENT '创建人',
  `updated` datetime DEFAULT NULL COMMENT '修改时间',
  `updator` varchar(100) DEFAULT NULL COMMENT '修改人',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='模块分数'

# 行为兴趣分值
SELECT
	a.id,
	a.age_area,
	a.city_name,
	a.crowd_type,
	b.id,
	b.behavioral_interest,
	b.icon_id,
	b.standard_value,
	b.creator,
	b.created
FROM
	mvp_crowd_info a
LEFT JOIN mvp_crowd_info_behavior b ON a.id = b.crowd_info_id
WHERE
	a. STATUS = b. STATUS = 1 and a.city_name IN ('上海市', '上海周边')
AND a.age_area IN ('85后', '95后')
# 模块分值
SELECT
	a.id,
	a.age_area,
	a.city_name,
	a.crowd_type,
	b.id,
	b.module_name,
	b.standard_value,
	b.creator,
	b.created
FROM
	mvp_crowd_info a
LEFT JOIN mvp_crowd_info_module b ON a.id = b.crowd_info_id
WHERE
	a. STATUS = b. STATUS = 1 and a.city_name IN ('上海市', '上海周边')
AND a.age_area IN ('85后', '95后')

# 行为分值删除非85后+上海市
DELETE
FROM
	mvp_crowd_info_behavior
WHERE
	NOT FIND_IN_SET(
		crowd_info_id,
		(
			SELECT
				GROUP_CONCAT(id)
			FROM
				mvp_crowd_info
			WHERE
				age_area = '85后'
			AND city_name = '上海市'
		)
	)

# 模块分值删除非85后+上海市
DELETE
FROM
	mvp_crowd_info_module
WHERE
	NOT FIND_IN_SET(
		crowd_info_id,
		(
			SELECT
				GROUP_CONCAT(id)
			FROM
				mvp_crowd_info
			WHERE
				age_area = '85后'
			AND city_name = '上海市'
		)
	)