chd mysql 作用_mySql记录

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);