MySQL中的两个SQL问题的实现方案

一.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;

---关于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）

这里说明下，在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语句操作？

解决方案如下：

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&gt; 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&gt; 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&gt; create  table temp(c1 int,c2 int,c3 varchar(10),rownum int,rank int); 
Query OK, 0 rows affected (0.13 sec) 
mysql&gt; insert into temp 
    -&gt; 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&gt; 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&gt; create table temp2 as select * from temp; 
Query OK, 12 rows affected (0.16 sec) 
Records: 12  Duplicates: 0  Warnings: 0 
mysql&gt; 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&gt; 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&gt; 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&gt; 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