DROP TABLE IF EXISTS `temp_csh_distributor_tag`;
CREATE TABLE `temp_csh_distributor_tag` (
`id` int(11) NOT NULL COMMENT '编号',
`customer_code` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '客户编码',
`tag_id` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '标签id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='经销商标签 临时 表';
SELECT @rowNO :=100000;
UPDATE csh_distributor_user SET code=(@rowNO := @rowNo+1);
UPDATE csh_distributor_user SET code =concat('SP', code);
UPDATE csh_money_goods c,temp_csh_money_goods t set c.distributor_code = t.distributor_code
where t.platform_product_code = c.platform_product_code and t. platform_product_code = 'C00040'
SELECT
SUM(case when `coupon_code` is not null then 1 else 0 end) as couponNum,
SUM(case when `is_send` = 1 then 1 else 0 end) as sendNum,
SUM(case when `is_use` = 1 then 1 else 0 end) as useNum
FROM
yyh_coupon_date_census
select MAX(cast(id as SIGNED INTEGER)) from csh_money_goods;
SELECT b.user_id from yyh_baby b where b.is_delete = 0 AND TIMESTAMPDIFF(MONTH,b.birthday,now())>24
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
drop procedure if exists userCount;
create procedure userCount(in n int)
BEGIN
DECLARE date VARCHAR ( 1000 );
SET date = '';
SET n = n - 1;
WHILE n >= 0 DO
SET @date1 = YEARWEEK(date_sub( NOW(), INTERVAL n MONTH ));
SET date = concat( date, ',', @date1 );
SET n = n - 1;
END WHILE;
SET @date2 = SUBSTR( date, 2 );
SET @SQL = concat('SELECT u.id,u.openid FROM yyh_user u WHERE
is_delete = 0
AND u.is_upload_little_card = 0
AND u.status = 3
AND u.baby_birthday is NOT NULL AND DATE(u.baby_birthday) <= DATE(NOW())
and TIMESTAMPDIFF(month,u.baby_birthday,DATE(NOW())) <6 AND TIMESTAMPDIFF(month,u.baby_birthday,DATE(NOW())) >= 0
AND u.is_delete = 0 AND u.is_subscribe = 1
AND date(u.create_time) <= DATE(NOW())
and exists (
select id
from yyh_user_tag ut
where ut.is_delete = 0
and ut.user_id = u.id
AND (ut.tag_id = ','6002',')','
GROUP BY
ut.user_id
HAVING
COUNT(ut.user_id) = 1
)AND YEARWEEK(u.baby_birthday) in (', @date2, ')' );
PREPARE stmt FROM @SQL;
EXECUTE stmt;
deallocate prepare stmt;
END;
call userCount(2);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
drop procedure if exists userCount;
create procedure userCount(in n int)
BEGIN
DECLARE date VARCHAR ( 1000 );
SET n = n - 1;
SET date = '';
WHILE n >= 0 DO
SET @date1 = YEARWEEK(date_sub( NOW(), INTERVAL n MONTH ));
SET date = concat( date, ',', @date1 );
SET n = n - 1;
END WHILE;
SET @date2 = SUBSTR( date, 2 );
SET @SQL = concat( 'SELECT u.id,u.openid FROM yyh_user u WHERE is_delete =0 and YEARWEEK(u.baby_birthday) not in (', @date2, ')' );
SELECT @date1;
SELECT @date2;
PREPARE stmt FROM @SQL;
EXECUTE stmt;
deallocate prepare stmt;
END;
call userCount(1);