我有一张表里面有String类型的year\month\seller\exam等字段
表里面存有以往的信息
我想在每月1号把上个月同seller\year\month的信息插入并修改year\month成为本月的,如果本年本月本人的信息已经存在,则不在插入
敬侯佳音
表里面存有以往的信息
我想在每月1号把上个月同seller\year\month的信息插入并修改year\month成为本月的,如果本年本月本人的信息已经存在,则不在插入
敬侯佳音
seller,year,month,exam
A 2009 01 A
B 2009 01 A
C 2009 01 A
A 2009 02 A
B 2009 02 A
C 2009 02 A
根据2009 02 生成 2009 03记录,并exam 为空
A 2009 03
B 2009 03
C 2009 03 insert into t (seller,year,month)
select seller,'2009','03'
from t
where year='2009' and month='02'
select seller,'2009','03' from tt a
left join (select * from tt where year='2009' and month='02') b
on a.seller=b.seller
where b.seller is null and a.year<>year(curdate()) and
a.month<>month(curdate())
特别是12月的时候 怎样写语句比较简单
select year(CURDATE()), month(CURDATE())
select DATE_FORMAT(CURDATE(),'%Y') ,DATE_FORMAT(CURDATE(),'%m') 上月时间 ADDDATE(CURDATE(),INTERVAL -1 month)
select year(ADDDATE(CURDATE(),INTERVAL 1 month), month(ADDDATE(CURDATE(),INTERVAL 1 month)
select DATE_FORMAT(ADDDATE(CURDATE(),INTERVAL -1 month),'%Y') ,DATE_FORMAT(ADDDATE(CURDATE(),INTERVAL -1 month),'%m') 假设表 t 字段 (seller,year,month,exam) t
seller,year,month,exam
A 2009 01 A
B 2009 01 A
C 2009 01 A
A 2009 02 A
B 2009 02 A
C 2009 02 A
根据2009 02 生成 2009 03记录,并exam 为空
A 2009 03
B 2009 03
C 2009 03 insert into t (seller,year,month)
select seller,DATE_FORMAT(CURDATE(),'%Y') ,DATE_FORMAT(CURDATE(),'%m')
from t
where year=DATE_FORMAT(ADDDATE(CURDATE(),INTERVAL -1 month),'%Y')
and month=DATE_FORMAT(ADDDATE(CURDATE(),INTERVAL -1 month),'%m')
A.aimSort,A.seller,'2009','04' FROM result A
LEFT JOIN result B
ON A.seller=B.seller AND A.year=2009 AND A.month=03
WHERE B.SELLER IS NULL