电玩城打鱼捕鱼-专业24小时上下分

分组排序取前N条记录以及生成自动数字序列

六月 6th, 2019  |  电玩城上下分数据库

[MySQL]分组排序取前N条记录以及生成自动数字序列,类似groupby后limit

  •  

前言:
同事的业务场景是,按照cid、author分组,再按照id倒叙,取出前2条记录出来。

oracle里面可以通过row_number() OVER (PARTITION BY cid,author ORDER BY
id DESC)
表示根据cid,author分组,在分组内部根据id排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的),而mysql数据库就没有这样的统计函数,需要自己写复杂的sql来实现。

1,录入测试数据

USE csdn;
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id INT PRIMARY KEY,
cid INT,
author VARCHAR(30)
) ENGINE=INNODB;

INSERT INTO test VALUES
(1,1,\’test1\’),
(2,1,\’test1\’),
(3,1,\’test2\’),
(4,1,\’test2\’),
(5,1,\’test2\’),
(6,1,\’test3\’),
(7,1,\’test3\’),
(8,1,\’test3\’),
(9,1,\’test3\’),
(10,2,\’test11\’),
(11,2,\’test11\’),
(12,2,\’test22\’),
(13,2,\’test22\’),
(14,2,\’test22\’),
(15,2,\’test33\’),
(16,2,\’test33\’),
(17,2,\’test33\’),
(18,2,\’test33\’);
INSERT INTO test VALUES (200,200,\’200test_nagios\’);
2,原始的效率比较低下的子查询实现方式
SELECT * FROM test a
WHERE
N>(
SELECT COUNT(*)
FROM test b
WHERE a.cid=b.cid AND a.`author`=b.`author` AND a.id<b.id
)ORDER BY cid,author,id DESC;
只要将N换成你要的数字比如2,就表示查询出每个分组的前2条记录,如下所示:
mysql> SELECT * FROM test a
-> WHERE
-> 2>(
-> SELECT COUNT(*)
-> FROM test b
-> WHERE a.cid=b.cid AND a.`author`=b.`author` AND a.id<b.id
-> )ORDER BY cid,author,id DESC;
+—–+——+—————-+
| id | cid | author |
+—–+——+—————-+
| 2 | 1 | test1 |
| 1 | 1 | test1 |
| 5 | 1 | test2 |
| 4 | 1 | test2 |
| 9 | 1 | test3 |
| 8 | 1 | test3 |
| 11 | 2 | test11 |
| 10 | 2 | test11 |
| 14 | 2 | test22 |
| 13 | 2 | test22 |
| 18 | 2 | test33 |
| 17 | 2 | test33 |
| 200 | 200 | 200test_nagios |
+—–+——+—————-+
13 ROWS IN SET (0.00 sec)

mysql>
3,使用动态sql来实现
先构造序列号码,引入一个@row来做rownumber
SET @row=0;SET @mid=”;SELECT cid, author,
@row:[email protected]+1
rownum FROM test ORDER BY cid, author LIMIT 10;
序列号码已经出来了,再加一个@mid来进行分组,重点在于CASE WHEN @mid =
author THEN
@row:[email protected]+1
ELSE @row:=1 END
rownum,表示分组的时候会自动从1计数指导这个分组数据遍历结束。

好了,再外面加一层inner JOIN 再对 rownumber 做限制
就可以拿到目标数据了。
SET @row=0;
执行结果如下所示:
mysql> SET @row=0;
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> SET @mid=\’\’;
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> SELECT a.*,b.rownum FROM test a
-> INNER JOIN (
-> SELECT cid, author, id, CASE WHEN @mid = author THEN
@row:[email protected]+1
ELSE @row:=1 END rownum, @mid:=author MID
-> FROM test
-> ORDER BY cid,author,id DESC
-> ) b ON b.author=a.author AND b.cid=a.cid AND b.id=a.id WHERE
b.rownum<3;
+—–+——+—————-+——–+
| id | cid | author | rownum |
+—–+——+—————-+——–+
| 2 | 1 | test1 | 1 |
| 1 | 1 | test1 | 2 |
| 5 | 1 | test2 | 1 |
| 4 | 1 | test2 | 2 |
| 9 | 1 | test3 | 1 |
| 8 | 1 | test3 | 2 |
| 11 | 2 | test11 | 1 |
| 10 | 2 | test11 | 2 |
| 14 | 2 | test22 | 1 |
| 13 | 2 | test22 | 2 |
| 18 | 2 | test33 | 1 |
| 17 | 2 | test33 | 2 |
| 200 | 200 | 200test_nagios | 1 |
+—–+——+—————-+——–+
13 ROWS IN SET (0.01 sec)

mysql>
参考文章地址:

前言:
同事的业务场景是,按照cid、author分组,再按照id倒叙,取出前…

SQL语句分类:

mysql_view,

概述:
视图即是虚拟表,也称为派生表,因为它们的内容都派生自其它表的查询结果。虽然视图看起来感觉和基本表一样,但是它们不是基本表。基本表的内容是持久的,而视图的内容是在使用过程中动态产生的。——摘自《SQLite权威指南》

使用视图的优点:
1.可靠的安全性
2.查询性能提高
3.有效应对灵活性的功能需求
4.轻松应对复杂的查询需求

视图的基本使用:
创建:
例如我们本身有一个这样的基本表:
mysql> select * from students;
+——+—————-+——-+
| id | name | age |
+——+—————-+——-+
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
+——+—————-+——-+
那么就可以像这样来创建一个视图:
CREATE VIEW stu_view AS SELECT name FROM students;
Query OK, 0 rows affected (0.01 sec)
创建完一个视图,可以通过查看数据库中的全部数据表来查看:
MySQL> show tables;
+——————-+
| Tables_in_student |
+——————-+
| stu_view |
| students |
+——————-+
2 rows in set (0.00 sec)
可以看到当前数据库中已经把刚刚创建的视图放进数据库的表集合中了。因为视图也是一种表,是虚拟表。

查询:
视图的查询和基本表的查询一样,因为视图也是一种数据表,所以你可以像这样的来查询它
mysql> select * from stu_view;
+—————-+
| name |
+—————-+
| bumblebee |
| king of monkey |
+—————-+

删除:
DROP VIEW stu_view;
删除之后可以再次查询进行验证:
mysql> select * from stu_view;
ERROR 1146 (42S02): Table ‘student.stu_view’ doesn’t exist

接下来我们看看如果我们变动了原始的基本表,视图会有什么改变:
mysql> INSERT INTO students(id, name, age) VALUES (2, ‘Zeus’,
100000);
Query OK, 1 row affected (0.00 sec)
检查基本表:
mysql> SELECT * FROM students;
+——+—————-+——–+
| id | name | age |
+——+—————-+——–+
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
| 2 | Zeus | 100000 |
+——+—————-+——–+
3 rows in set (0.00 sec)
检查视图:
mysql> SELECT * FROM stu_view;
+—————-+
| name |
+—————-+
| bumblebee |
| king of monkey |
| Zeus |
+—————-+
3 rows in set (0.00 sec)

更新:
mysql> CREATE VIEW stu_view2 AS SELECT id, name FROM students;
Query OK, 0 rows affected (0.01 sec)
验证:
mysql> select * from stu_view2;
+——+—————-+
| id | name |
+——+—————-+
| 1 | bumblebee |
| 1 | king of monkey |
| 2 | Zeus |
+——+—————-+
3 rows in set (0.00 sec)
更新视图:
mysql> UPDATE stu_view2 SET name=’Medusa’ WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
检查视图更新结果:
mysql> SELECT * FROM stu_view2;
+——+—————-+
| id | name |
+——+—————-+
| 1 | bumblebee |
| 1 | king of monkey |
| 2 | Medusa |
+——+—————-+
3 rows in set (0.00 sec)
检查基本表更新结果:
mysql> SELECT * FROM students;
+——+—————-+——–+
| id | name | age |
+——+—————-+——–+
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
| 2 | Medusa | 100000 |
+——+—————-+——–+
3 rows in set (0.00 sec)

关联多表的视图:
以上都是基于单表的操作,接下来我们从两张表中来做一些实战。
我们额外创建一个info表作为辅助的数据表,如下:
mysql> select * from info;
+—-+——–+———————————+
| id | stu_id | info |
+—-+——–+———————————+
| 1 | 1 | A member of the deformed steel. |
| 2 | 2 | Hero in Chinese Mythology. |
| 3 | 3 | In Greek mythology the Gorgon. |
+—-+——–+———————————+
3 rows in set (0.00 sec)

我们创建一个连接了两张基本表的视图stu_view3
mysql> CREATE VIEW stu_view3 AS SELECT s.id, s.name, s.age, i.info
FROM students s, info i WHERE i.stu_id=s.id;
Query OK, 0 rows affected (0.00 sec)
验证过程:
mysql> select * from stu_view3;
+——+—————-+——–+———————————+
| id | name | age | info |
+——+—————-+——–+———————————+
| 1 | bumblebee | 200 | A member of the deformed steel. |
| 2 | king of monkey | 10000 | Hero in Chinese Mythology. |
| 3 | Medusa | 100000 | In Greek mythology the Gorgon. |
+——+—————-+——–+———————————+
3 rows in set (0.00 sec)

对连接了两张基本表的视图stu_view3进行更新操作:
mysql> UPDATE stu_view3 SET age=800 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

验证视图stu_view3:
mysql> select * from stu_view3;
+——+—————-+——–+———————————+
| id | name | age | info |
+——+—————-+——–+———————————+
| 1 | bumblebee | 800 | A member of the deformed steel. |
| 2 | king of monkey | 10000 | Hero in Chinese Mythology. |
| 3 | Medusa | 100000 | In Greek mythology the Gorgon. |
+——+—————-+——–+———————————+
3 rows in set (0.00 sec)

验证基本表:
mysql> select * from students;
+——+—————-+——–+
| id | name | age |
+——+—————-+——–+
| 1 | bumblebee | 800 |
| 2 | king of monkey | 10000 |
| 3 | Medusa | 100000 |
+——+—————-+——–+
3 rows in set (0.00 sec)

总结:

1.在使用视图的时候,就是与使用表的语法一样的
2.创建视图的时候,该视图的名字如果与已经存在表重名的话,那么会报错,不允许创建。视图就是一种特殊的表

其实mysql视图的原理就是把自己想要的数据查询出来作为一个独立的表(虚拟表),在去操作这个表的数据,自己的理解如下:

SELECT uid FROM(SELECT id,song_name,scores,uid,like_num,play_num FROM
vk_member_rec WHERE is_publish=2 AND privacy=1 AND is_pass=1 AND
STATUS=1 AND ulist=1 ORDER BY scores DESC,like_num DESC,play_num DESC)
vk_member_rec GROUP BY uid ORDER BY scores DESC,like_num
DESC,play_num DESC LIMIT 0,100

概述:
视图即是虚拟表,也称为派生表,因为它们的内容都派生自其它表的查询结果。虽然视图看起来感觉和基本表一样,但是…

1.DDL DATA define LANGUAGE 数据定义语句: CREATE    ALTER ;

2.DML DATA manipulation LANGUAGE 数据操作语句  增删改语句  INSERT / 
DELETE /UPDATE ;

3.DQL DATA QUERY LANGUAGE  数据查询语句  SELECT ;

一、增删改

1.增加 INSERT

方式1:

语法:INSERT INTO 表名 (字段1,字段2。。。) VALUES(值1,值2。。。);

mysql> INSERT INTO emp (empno,ename,deptno) VALUES(1,’tom’,1010);

方式2:插入部分字段的值,字段的个数和顺序要与值的个数和顺序一致

mysql> INSERT INTO emp (empno,ename) VALUES(2,’tom’);

方式3:省略字段,但是值的个数和顺序要和表里的一致;

mysql> INSERT INTO emp VALUES (3,’bob’,1002);

2.删除 DELETE

方式1:删除表中的所有数据;

语法:DELETE FROM  表名;

mysql> DELETE FROM emp;

方式2:删除符合条件的数据;

语法: DELETE FROM 表名 WHERE 条件;

mysql> DELETE FROM emp WHERE ename =’tom’;

操作符:

=等于

<小于

<=小于等于

>大于

>=大于等于

<>不等于  !=

BETWEEN 下限值 AND  上线值

注意:下限值和上线值是包括的。

如:mysql> DELETE FROM emp  WHERE empno BETWEEN 2 AND 4;  包含2和4;

逻辑表达式:

连接我们的条件表达式的

AND  并且  所有的条件都要满足的情况下才会去匹配。

mysql> DELETE FROM emp WHERE empno>=2 AND empno<=4;

OR 或者    满足其中之一的条件都能匹配。

mysql> DELETE FROM emp WHERE empno=1 OR empno =5;

3.更新数据UPDATE

不带条件可以更改整个字段的值:

mysql> UPDATE emp SET deptno =1001;

带条件,更改符合条件的数据:

mysql> UPDATE emp SET deptno =1002 WHERE ename=’tom’;

更改多个字段的值

mysql> UPDATE emp SET empno=5,deptno=1003 WHERE ename =’tom’;

/*二、简单查询DQL (data query language)

1.查询所有列的所有数据;

使用*的效率相对较低,

select * from 表名;

select sid,sname ,birthday from 表名;

2.查询指定列的数据;

select 字段1,字段2.。。。from  表名;

3.去重查询

SELECT DISTINCT sname FROM student;

4.列计算:数值列可以进行加减乘除运算

SELECT sid,sname ,sal+comm  FROM student;

5.字段的别名 ,as可以省略,别名的引号可以省略

SELECT sid AS 编号,sname AS 姓名,birthday AS 生日 FROM student;

6.条件查询

SELECT * FROM student WHERE birthday >’2000-02-01’AND sex=’女’ OR
sid=1 ;

in关键词:

字段的值符合括号里的任何一个就满足条件

SELECT * FROM student WHERE sid IN(1,4,9);

SELECT * FROM student WHERE sname IN(‘tom’,’bob’) ;

not in: 不是括号里的任何一个就满足条件

is null :值为null;

is not null:值不为null;

7.模糊查询  like

%  代表任意多个字符

_  代表一个字符

select * from student where sname like ‘张’;

select * from student where sname like ‘___张’;

8.排序  order by

asc :ascend 升序,默认为升序;

desc : descend 降序;

select * from student order by sal asc;

按多个字段排序:

语法: select * from 表名 order by 字段1 desc ,字段2  desc;

select * from student order by sex desc ,sal  desc;

9.limit 限制返回的条数  一般用于数据分页

limit m,n;  m代表开始的索引,n代表个数;

SELECT * FROM emp LIMIT 15,5;

limit n;  n代表个数,索引从0开始;

  1. null和任何值计算都得null;

函数:ifnull(字段,值); 若该字段中有值为null;

SELECT ename ,sal+IFNULL(comm,0) FROM emp;

函数:concat(字段,值或字段);可以把字段的值拼接起来;

SELECT ename ,CONCAT(sal,’元’) sal FROM emp;

*/

1.创建学生表student:学生编号sid 主键自增长,姓名sname 非空,性别sex 
非空,生日birthday ,年龄age ,班级编号cid ;

mysql> create table student(sid int primary key auto_increment,sname
varchar(10)

not null,sex varchar(10) not null,birthday date,age int,cid int);

Query OK, 0 rows affected (0.03 sec)

这是学生表student:

+———-+————-+——+—–+———+—————-+

| Field    | Type        | Null | Key | Default | Extra          |

+———-+————-+——+—–+———+—————-+

| sid      | int(11)    | NO  | PRI | NULL    | auto_increment |

| sname    | varchar(10) | NO  |    |        |                |

| sex      | varchar(10) | NO  |    |        |                |

| birthday | date        | YES  |    | NULL    |                |

| age      | int(11)    | YES  |    | NULL    |                |

| cid      | int(11)    | YES  |    | NULL    |                |

+———-+————-+——+—–+———+—————-+

2.创建班级表class: 班级编号 cid 主键 ,班级名称cname 非空;

mysql> create table class(cid int primary key,cname varchar(10) not
null);

Query OK, 0 rows affected (0.01 sec)

mysql> desc class;

+——-+————-+——+—–+———+——-+

| Field | Type        | Null | Key | Default | Extra |

+——-+————-+——+—–+———+——-+

| cid  | int(11)    | NO  | PRI |        |      |

| cname | varchar(10) | NO  |    |        |      |

+——-+————-+——+—–+———+——-+

3.给学生表添加一个字段:住址address;

mysql> alter table student add column address varchar(10);

Query OK, 0 rows affected (0.09 sec)

Records: 0  Duplicates: 0  Warnings: 0

这是增加的字段address:

mysql> desc student;

+———-+————-+——+—–+———+—————-+

| Field    | Type        | Null | Key | Default | Extra          |

+———-+————-+——+—–+———+—————-+

| sid      | int(11)    | NO  | PRI | NULL    | auto_increment |

| sname    | varchar(10) | NO  |    |        |                |

| sex      | varchar(10) | NO  |    |        |                |

| birthday | date        | YES  |    | NULL    |                |

| age      | int(11)    | YES  |    | NULL    |                |

| cid      | int(11)    | YES  |    | NULL    |                |

| address  | varchar(10) | YES  |    | NULL    |                |

+———-+————-+——+—–+———+—————-+

7 rows in set (0.01 sec)

4.修改学生表性别字段为gender;

mysql> alter table student change sex gender varchar(10);

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

修改后的student表:

mysql> desc student;

+———-+————-+——+—–+———+—————-+

| Field    | Type        | Null | Key | Default | Extra          |

+———-+————-+——+—–+———+—————-+

| sid      | int(11)    | NO  | PRI | NULL    | auto_increment |

| sname    | varchar(10) | NO  |    |        |                |

| gender  | varchar(10) | YES  |    | NULL    |                |

| birthday | date        | YES  |    | NULL    |                |

| age      | int(11)    | YES  |    | NULL    |                |

| cid      | int(11)    | YES  |    | NULL    |                |

| address  | varchar(10) | YES  |    | NULL    |                |

+———-+————-+——+—–+———+—————-+

7 rows in set (0.01 sec)

5.给birthday添加默认值为2000-01-01;

mysql> alter table student modify birthday date default ‘2000-01-01’;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

改变birthday的默认值:

标签:,

Your Comments

近期评论

    功能


    网站地图xml地图