开发者社区> 科技小能手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【MySQL从入门到精通】【高级篇】(二十二)慢查询日志分析,SHOW PROFILE查看SQL执行成本
上一篇文章我们介绍数据库的优化步骤【MySQL从入门到精通】【高级篇】(二十一)数据库优化步骤_查看系统性能参数,其中,说到了通过开启慢查询日志来分析慢查询的SQL。这篇文章就是具体来介绍如何开启慢查询日志以及如何分析慢查询日志。
0 0
【MySQL从入门到精通】【高级篇】(五)MySQL的SQL语句执行流程
今天正式进入核心知识点的学习,本文主要介绍MySQL中SQL的执行流程。熟悉SQL的执行流程对后期数据库的优化至关重要。话不多说直接进入今天的学习。
0 0
Mysql深入优化 (二) --- 体系结构、存储引擎、SQL优化
在查询语句中表名的后面,添加 use index 来提供希望MySQL去参考的索引列表,就可以让MySQL不再考虑其他可用的索引。
0 0
【大数据系列之MySQL】(十):使用Navicat运行本地sql文件
【大数据系列之MySQL】(十):使用Navicat运行本地sql文件
0 0
MySQL的条件分组查询SQL语句写法
MySQL的条件分组查询SQL语句写法
0 0
MySQL实战,SQL语句
MySQL实战,SQL语句
0 0
MySQL:通过增加索引进行SQL查询优化
【实验】 一次非常有意思的SQL优化经历:从30248.271s到0.001s
0 0
Mysql中 慢查询日志和show profile进行sql分析
MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。
0 0
SQL注入笔记记录+MySQL的事务隔离级别
(一)SQL注入。 1.如何理解SQL注入? SQL注入是一种将SQL代码添加到输入参数中,传递到SQL服务器解析并执行的一种攻击手法。 2.SQL注入是如何产生的?
0 0
MySQL对于千万级的大表要怎么优化(SQL语句的优化)?
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引
0 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载