一.MySQL中实现date字段上的上周、上月查询
1.创建表
Create Table: CREATE TABLE `tab` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
2.
返回上个周的记录:
(1)
select *,DAYNAME(birthday)
from tab
where birthday <= date_add(now(),interval -(1 + weekday(now())) day)
and birthday >= date_add(now(),interval -(8 + weekday(now())) day)
order by birthday;
返回上个月的记录
(1)
select *,
(case
when month(now()) = 1
then
year(birthday) = year(now()) - 1 and month(birthday) = 12
else
year(birthday) = year(now()) and month(birthday) = month(now()) -1 end ) as isFlag
from tab
having isFlag = 1;
Create Table: CREATE TABLE `tab` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
2.
返回上个周的记录:
(1)
select *,DAYNAME(birthday)
from tab
where birthday <= date_add(now(),interval -(1 + weekday(now())) day)
and birthday >= date_add(now(),interval -(8 + weekday(now())) day)
order by birthday;
返回上个月的记录
(1)
select *,
(case
when month(now()) = 1
then
year(birthday) = year(now()) - 1 and month(birthday) = 12
else
year(birthday) = year(now()) and month(birthday) = month(now()) -1 end ) as isFlag
from tab
having isFlag = 1;
---关于case -- when和having的妙用,呵呵以前从没有想过还能这么使用~
(2)
select *,MONTHNAME(birthday)
from tab
where birthday <= last_day(date_add(now(),interval -1 MONTH))
and birthday >= concat(extract(year_month from date_add(now(),interval -1 MONTH)),'01')
order by birthday;
说明:
MySQL weekday() 函数和 dayofweek() 类似,都是返回“某天”在一周中的位置。不同点在于参考的标准, weekday:(0 = Monday, 1 = Tuesday, …, 6 = Sunday); dayofweek:(1 = Sunday, 2 = Monday, …, 7 = Saturday)
(2)
select *,MONTHNAME(birthday)
from tab
where birthday <= last_day(date_add(now(),interval -1 MONTH))
and birthday >= concat(extract(year_month from date_add(now(),interval -1 MONTH)),'01')
order by birthday;
说明:
MySQL weekday() 函数和 dayofweek() 类似,都是返回“某天”在一周中的位置。不同点在于参考的标准, weekday:(0 = Monday, 1 = Tuesday, …, 6 = Sunday); dayofweek:(1 = Sunday, 2 = Monday, …, 7 = Saturday)
这里说明下,在itpub论坛上有兄弟给出了
select * from TABLE
where PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(register_date,'%Y%m')) = 1
的解决方案,该方案确实能够实现,但是如果在date字段上面加上索引的话,该索引由于使用了函数处理,索引会失效。
二.问题描述如下:
A表
c1 c2 c3
--------------
1 1 a1
1 2 a2
1 3 a3
1 4 a4
2 1 b1
2 2 b2
2 3 b3
2 4 b4
3 1 c1
3 2 c2
3 3 c3
3 4 c4
表中有三组数据,用c1表示,每组数据中有4项内容,用C2表示,每项内容用C3表示
要求:将每组数据中的第二项复制到第三项
A表
c1 c2 c3
--------------
1 1 a1
1 2 a2
1 3 a2
1 4 a4
2 1 b1
2 2 b2
2 3 b2
2 4 b4
3 1 c1
3 2 c2
3 3 c2
3 4 c4
如何用SQL语句操作?
c1 c2 c3
--------------
1 1 a1
1 2 a2
1 3 a3
1 4 a4
2 1 b1
2 2 b2
2 3 b3
2 4 b4
3 1 c1
3 2 c2
3 3 c3
3 4 c4
表中有三组数据,用c1表示,每组数据中有4项内容,用C2表示,每项内容用C3表示
要求:将每组数据中的第二项复制到第三项
A表
c1 c2 c3
--------------
1 1 a1
1 2 a2
1 3 a2
1 4 a4
2 1 b1
2 2 b2
2 3 b2
2 4 b4
3 1 c1
3 2 c2
3 3 c2
3 4 c4
如何用SQL语句操作?
解决方案如下:
1.创建表并插入记录
create table A(c1 int,c2 int,c3 varchar(10));
insert into A values(1,1,'a1'),(1,2,'a2'),(1,3,'a3'),(1,4,'a4');
insert into A values(2,1,'b1'),(2,2,'b2'),(2,3,'b3'),(2,4,'b4');
insert into A values(3,1,'c1'),(3,2,'c2'),(3,3,'c3'),(3,4,'c4');
mysql> select * from A;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1 | 1 | a1 |
| 1 | 2 | a2 |
| 1 | 3 | a3 |
| 1 | 4 | a4 |
| 2 | 1 | b1 |
| 2 | 2 | b2 |
| 2 | 3 | b3 |
| 2 | 4 | b4 |
| 3 | 1 | c1 |
| 3 | 2 | c2 |
| 3 | 3 | c3 |
| 3 | 4 | c4 |
+------+------+------+
12 rows in set (0.00 sec)
2.实现过程
(1)实现分组排名
create table A(c1 int,c2 int,c3 varchar(10));
insert into A values(1,1,'a1'),(1,2,'a2'),(1,3,'a3'),(1,4,'a4');
insert into A values(2,1,'b1'),(2,2,'b2'),(2,3,'b3'),(2,4,'b4');
insert into A values(3,1,'c1'),(3,2,'c2'),(3,3,'c3'),(3,4,'c4');
mysql> select * from A;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1 | 1 | a1 |
| 1 | 2 | a2 |
| 1 | 3 | a3 |
| 1 | 4 | a4 |
| 2 | 1 | b1 |
| 2 | 2 | b2 |
| 2 | 3 | b3 |
| 2 | 4 | b4 |
| 3 | 1 | c1 |
| 3 | 2 | c2 |
| 3 | 3 | c3 |
| 3 | 4 | c4 |
+------+------+------+
12 rows in set (0.00 sec)
2.实现过程
(1)实现分组排名
初始化用户变量:set @c1=1;
mysql> select result.c1,result.c1,result.c3,
result.rownum,
result.rank
from
(select ff.c1,ff.c2,ff.c3,
@rownum:=@rownum+1 rownum,
if(@c1=ff.c1,@rank:=@rank+1,@rank:=1) as rank,
@c1 := ff.c1
from (select c1,c2,c3
from A
group by c1,c2,c3
order by c1,c3,c3) ff,
(select @rownum :=0,@rank:=0,@c1=null) tt) result;
+------+------+------+--------+------+
| c1 | c1 | c3 | rownum | rank |
+------+------+------+--------+------+
| 1 | 1 | a1 | 1 | 1 |
| 1 | 1 | a2 | 2 | 2 |
| 1 | 1 | a3 | 3 | 3 |
| 1 | 1 | a4 | 4 | 4 |
| 2 | 2 | b1 | 5 | 1 |
| 2 | 2 | b2 | 6 | 2 |
| 2 | 2 | b3 | 7 | 3 |
| 2 | 2 | b4 | 8 | 4 |
| 3 | 3 | c1 | 9 | 1 |
| 3 | 3 | c2 | 10 | 2 |
| 3 | 3 | c3 | 11 | 3 |
| 3 | 3 | c4 | 12 | 4 |
+------+------+------+--------+------+
12 rows in set (0.00 sec)
(2)将排名后的记录插入到两个临时表中
mysql> create table temp(c1 int,c2 int,c3 varchar(10),rownum int,rank int);
Query OK, 0 rows affected (0.13 sec)
mysql> insert into temp
-> select result.c1,result.c2,result.c3,
result.rownum,
result.rank
from (select ff.c1,ff.c2,ff.c3,
@rownum:=@rownum+1 rownum,
if(@c1=ff.c1,@rank:=@rank+1,@rank:=1) as rank,
@c1 := ff.c1
from
(select c1,c2,c3
from A
group by c1,c2,c3
order by c1,c3,c3) ff,
(select @rownum :=0,@rank:=0,@c1=null) tt)result;
Query OK, 12 rows affected (0.03 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+------+------+------+--------+------+
| c1 | c2 | c3 | rownum | rank |
+------+------+------+--------+------+
| 1 | 1 | a1 | 1 | 1 |
| 1 | 2 | a2 | 2 | 2 |
| 1 | 3 | a3 | 3 | 3 |
| 1 | 4 | a4 | 4 | 4 |
| 2 | 1 | b1 | 5 | 1 |
| 2 | 2 | b2 | 6 | 2 |
| 2 | 3 | b3 | 7 | 3 |
| 2 | 4 | b4 | 8 | 4 |
| 3 | 1 | c1 | 9 | 1 |
| 3 | 2 | c2 | 10 | 2 |
| 3 | 3 | c3 | 11 | 3 |
| 3 | 4 | c4 | 12 | 4 |
+------+------+------+--------+------+
12 rows in set (0.00 sec)
mysql> create table temp2 as select * from temp;
Query OK, 12 rows affected (0.16 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select * from temp2;
+------+------+------+--------+------+
| c1 | c2 | c3 | rownum | rank |
+------+------+------+--------+------+
| 1 | 1 | a1 | 1 | 1 |
| 1 | 2 | a2 | 2 | 2 |
| 1 | 3 | a3 | 3 | 3 |
| 1 | 4 | a4 | 4 | 4 |
| 2 | 1 | b1 | 5 | 1 |
| 2 | 2 | b2 | 6 | 2 |
| 2 | 3 | b3 | 7 | 3 |
| 2 | 4 | b4 | 8 | 4 |
| 3 | 1 | c1 | 9 | 1 |
| 3 | 2 | c2 | 10 | 2 |
| 3 | 3 | c3 | 11 | 3 |
| 3 | 4 | c4 | 12 | 4 |
+------+------+------+--------+------+
12 rows in set (0.00 sec)
(3)更新记录
mysql> update temp2,temp set temp2.c3=temp.c3 where temp.c1=temp2.c1 and temp2.rank=2 and temp.rank=3;
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> update temp2,temp set temp2.c3=temp.c3 where temp.c1=temp2.c1 and temp2.rank=3 and temp.rank=2;
Query OK, 3 rows affected (0.08 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from temp2;
+------+------+------+--------+------+
| c1 | c2 | c3 | rownum | rank |
+------+------+------+--------+------+
| 1 | 1 | a1 | 1 | 1 |
| 1 | 2 | a3 | 2 | 2 |
| 1 | 3 | a2 | 3 | 3 |
| 1 | 4 | a4 | 4 | 4 |
| 2 | 1 | b1 | 5 | 1 |
| 2 | 2 | b3 | 6 | 2 |
| 2 | 3 | b2 | 7 | 3 |
| 2 | 4 | b4 | 8 | 4 |
| 3 | 1 | c1 | 9 | 1 |
| 3 | 2 | c3 | 10 | 2 |
| 3 | 3 | c2 | 11 | 3 |
| 3 | 4 | c4 | 12 | 4 |
+------+------+------+--------+------+
12 rows in set (0.00 sec)
(4)temp2中的记录即是想要的结果。
mysql> select result.c1,result.c1,result.c3,
result.rownum,
result.rank
from
(select ff.c1,ff.c2,ff.c3,
@rownum:=@rownum+1 rownum,
if(@c1=ff.c1,@rank:=@rank+1,@rank:=1) as rank,
@c1 := ff.c1
from (select c1,c2,c3
from A
group by c1,c2,c3
order by c1,c3,c3) ff,
(select @rownum :=0,@rank:=0,@c1=null) tt) result;
+------+------+------+--------+------+
| c1 | c1 | c3 | rownum | rank |
+------+------+------+--------+------+
| 1 | 1 | a1 | 1 | 1 |
| 1 | 1 | a2 | 2 | 2 |
| 1 | 1 | a3 | 3 | 3 |
| 1 | 1 | a4 | 4 | 4 |
| 2 | 2 | b1 | 5 | 1 |
| 2 | 2 | b2 | 6 | 2 |
| 2 | 2 | b3 | 7 | 3 |
| 2 | 2 | b4 | 8 | 4 |
| 3 | 3 | c1 | 9 | 1 |
| 3 | 3 | c2 | 10 | 2 |
| 3 | 3 | c3 | 11 | 3 |
| 3 | 3 | c4 | 12 | 4 |
+------+------+------+--------+------+
12 rows in set (0.00 sec)
(2)将排名后的记录插入到两个临时表中
mysql> create table temp(c1 int,c2 int,c3 varchar(10),rownum int,rank int);
Query OK, 0 rows affected (0.13 sec)
mysql> insert into temp
-> select result.c1,result.c2,result.c3,
result.rownum,
result.rank
from (select ff.c1,ff.c2,ff.c3,
@rownum:=@rownum+1 rownum,
if(@c1=ff.c1,@rank:=@rank+1,@rank:=1) as rank,
@c1 := ff.c1
from
(select c1,c2,c3
from A
group by c1,c2,c3
order by c1,c3,c3) ff,
(select @rownum :=0,@rank:=0,@c1=null) tt)result;
Query OK, 12 rows affected (0.03 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+------+------+------+--------+------+
| c1 | c2 | c3 | rownum | rank |
+------+------+------+--------+------+
| 1 | 1 | a1 | 1 | 1 |
| 1 | 2 | a2 | 2 | 2 |
| 1 | 3 | a3 | 3 | 3 |
| 1 | 4 | a4 | 4 | 4 |
| 2 | 1 | b1 | 5 | 1 |
| 2 | 2 | b2 | 6 | 2 |
| 2 | 3 | b3 | 7 | 3 |
| 2 | 4 | b4 | 8 | 4 |
| 3 | 1 | c1 | 9 | 1 |
| 3 | 2 | c2 | 10 | 2 |
| 3 | 3 | c3 | 11 | 3 |
| 3 | 4 | c4 | 12 | 4 |
+------+------+------+--------+------+
12 rows in set (0.00 sec)
mysql> create table temp2 as select * from temp;
Query OK, 12 rows affected (0.16 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select * from temp2;
+------+------+------+--------+------+
| c1 | c2 | c3 | rownum | rank |
+------+------+------+--------+------+
| 1 | 1 | a1 | 1 | 1 |
| 1 | 2 | a2 | 2 | 2 |
| 1 | 3 | a3 | 3 | 3 |
| 1 | 4 | a4 | 4 | 4 |
| 2 | 1 | b1 | 5 | 1 |
| 2 | 2 | b2 | 6 | 2 |
| 2 | 3 | b3 | 7 | 3 |
| 2 | 4 | b4 | 8 | 4 |
| 3 | 1 | c1 | 9 | 1 |
| 3 | 2 | c2 | 10 | 2 |
| 3 | 3 | c3 | 11 | 3 |
| 3 | 4 | c4 | 12 | 4 |
+------+------+------+--------+------+
12 rows in set (0.00 sec)
(3)更新记录
mysql> update temp2,temp set temp2.c3=temp.c3 where temp.c1=temp2.c1 and temp2.rank=2 and temp.rank=3;
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> update temp2,temp set temp2.c3=temp.c3 where temp.c1=temp2.c1 and temp2.rank=3 and temp.rank=2;
Query OK, 3 rows affected (0.08 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from temp2;
+------+------+------+--------+------+
| c1 | c2 | c3 | rownum | rank |
+------+------+------+--------+------+
| 1 | 1 | a1 | 1 | 1 |
| 1 | 2 | a3 | 2 | 2 |
| 1 | 3 | a2 | 3 | 3 |
| 1 | 4 | a4 | 4 | 4 |
| 2 | 1 | b1 | 5 | 1 |
| 2 | 2 | b3 | 6 | 2 |
| 2 | 3 | b2 | 7 | 3 |
| 2 | 4 | b4 | 8 | 4 |
| 3 | 1 | c1 | 9 | 1 |
| 3 | 2 | c3 | 10 | 2 |
| 3 | 3 | c2 | 11 | 3 |
| 3 | 4 | c4 | 12 | 4 |
+------+------+------+--------+------+
12 rows in set (0.00 sec)
(4)temp2中的记录即是想要的结果。
本文转自 yubowei 51CTO博客,原文链接:http://blog.51cto.com/samyubw/232080