temp

select * from swebservicelog


truncate table swebservicelog


select * from bactivityList

SELECT ID,uid,urgentid,code,description,begindate,enddate,creationdt,updatedt,remark
FROM bactivityList
WHERE uid = #{UID}
AND deleted_flag=0

select

    YEARWEEK(date_format,1) as yearweek,
     min(date_format) as begin_date,
     max(date_format) as end_date
     from dim_date 
     where 
     substr(date_format,1,7)<=substr(CURRENT_DATE,1,7)
     group by YEARWEEK(date_format,1)



desc bactivityList

insert bactivityList(uid,urgentid,code,description,begindate,enddate,creationdt,updatedt,remark)
values(1,1,1,'近几天内打包好知识','2019-01-01','2020-01-01',now(),now(),null)


alter table hmotivate change column code commentDay datetime 

desc bmotivate
insert bmotivate(uid,code,description,begindate,enddate,creationdt,updatedt)
values(1,'Child007','每天整理自己玩具','2019-09-01','2030-12-12',now(),now())


insert bmotivate(uid,code,description,begindate,enddate,creationdt,updatedt)
values(1,'Mom003','每天进行一番总结','2019-09-01','2030-12-12',now(),now())


select * from hmotivate

select date_format(now(),'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

desc hmotivate

insert hmotivate(uid,commentDay,motiid,creationdt,updatedt,remark)
values(1,'2019-09-01',5,now(),now(),'done')

insert hmotivate(uid,commentDay,motiid,creationdt,updatedt,remark)
values(1,'2019-09-01',6,now(),now(),'done')

insert hmotivate(uid,commentDay,motiid,creationdt,updatedt,remark)
values(1,'2019-09-05',6,now(),now(),'done')



SELECT * FROM bmotivate
SELECT * FROM hmotivate


SELECT commentDay,motiid,remark FROM hmotivate WHERE DATA_FORMAT(commentDay,'%Y%m') =DATA_FORMAT(now(),'%Y%m') 


SELECT commentDay,motiid,remark FROM hmotivate WHERE DATA_SUB(CURDATE(),INTERVAL(N,N1,N2,N3,...)) <=date(commentDay)

SELECT commentDay,motiid,remark FROM hmotivate WHERE week(CURDATE()) =week(commentDay) and year(CURDATE()) =year(commentDay)


select WEEK(now())
select month(now())



SELECT b.description,motiid,DATE_FORMAT(commentDay,'%Y年%m月%d日') as commentDay,remark FROM hmotivate 
WHERE week(CURDATE()) =week(commentDay)
and year(CURDATE()) =year(commentDay)
and uid =1
group by motiid,commentDay,remark


desc bmotivate



SELECT b.description,motiid,DATE_FORMAT(commentDay,'%Y年%m月%d日') as commentDay, hmotivate.remark 
FROM hmotivate 
right join bmotivate b on motiid = b.id
WHERE week(CURDATE()) =week(commentDay)
and year(CURDATE()) =year(commentDay)
and hmotivate.uid =1
group by motiid,commentDay,remark





select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY);


SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());


select subdate(curdate(),DATE_FORMAT(curdate(),'%w')-1)
select DATE_FORMAT(curdate(),'%w')


desc bmotivate
desc hmotivate 


select  now()

select DATE_FORMAT(now(),INTERVAL(1 DAY))

SELECT DATA_SUB(CURDATE(),INTERVAL(1 Day)) as day1,remark 
FROM hmotivate a 
right join bmotivate b on motiid = id
WHERE week(CURDATE()) =week(commentDay)
and year(CURDATE()) =year(commentDay)
and a.uid =1
group by motiid,commentDay,remark



order by motiid asc



UPDATE bmotivate SET CODE = 'Child001' WHERE ID =1

UPDATE bmotivate SET CODE = 'Child002' WHERE ID =2

select id,uid,DATE_FORMAT(begindate,'%Y年%m月%d日') as begindate ,DATE_FORMAT(enddate,'%Y年%m月%d日') as enddate,
        description,remark,creationdt,updatedt
        from bmotivate
        WHERE uid =1

desc bactivityList

DROP TABLE IF EXISTS `bmotivate`;
CREATE TABLE `bmotivate`  (
  `id` int(4) NOT NULL AUTO_INCREMENT,
	`uid` int(4) NOT NULL,
  `code` int(4) NOT NULL,
	`description` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  `begindate` datetime(0) NOT NULL,
	`enddate` datetime(0) NOT NULL,
	`deleted_flag` bit(1) NULL DEFAULT b'0',

  `creationdt` datetime(0) NOT NULL,
  `updatedt` datetime(0) NOT NULL,
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;



DROP TABLE IF EXISTS `hmotivate`;
CREATE TABLE `hmotivate`  (
  `id` int(4) NOT NULL AUTO_INCREMENT,
	`uid` int(4) NOT NULL,
  `code` int(4) NOT NULL,
	`motiid` int(4) NOT NULL,
  `creationdt` datetime(0) NOT NULL,
  `updatedt` datetime(0) NOT NULL,
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;



DROP TABLE IF EXISTS `bactivityList`;
CREATE TABLE `bactivityList`  (
  `id` int(4) NOT NULL AUTO_INCREMENT,
	`uid` int(4) NOT NULL,
	`urgentid` int(4) NOT NULL,	
  `code` int(4) NOT NULL,
	`description` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	`begindate` datetime(0) NOT NULL,
	`enddate` datetime(0) NOT NULL,
	`deleted_flag` bit(1) NULL DEFAULT b'0',
  `creationdt` datetime(0) NOT NULL,
  `updatedt` datetime(0) NOT NULL,
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;