分组之后排序取第一条

oracle 分组后取每组第一条数据

  [sql]
1
2
3
4
 SELECT *       
    FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn,       
          test1.* FROM test1)       
   WHERE rn = 1

关联查询

今天关联查询遇到一个有趣的现象。

以前遇到过没整理，记录一下，方便以后查阅。

本文测试环境：

  [plaintext]
1
MySQL   5.7.17

前车之鉴

sql 之 left join、right join、inner join 的区别

一言以蔽之

  [plaintext]
1
2
3
4
5
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接) 只返回两个表中联结字段相等的行

数据准备

  • create table
  [plaintext]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE mapping
(
	id           BIGINT AUTO_INCREMENT
	COMMENT '唯一标识'
		PRIMARY KEY,
	code         INT                                 NOT NULL
	COMMENT '错误编码',
	msg          VARCHAR(64)                         NOT NULL
	COMMENT '错误描述',
	created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
	COMMENT '创建时间'
)
	COMMENT '错误码映射表'
	
CREATE TABLE result
(
  id           BIGINT AUTO_INCREMENT
  COMMENT '唯一标识'
    PRIMARY KEY,
  code         INT                                 NOT NULL
  COMMENT '错误编码',
  created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
  COMMENT '创建时间'
)
  COMMENT '结果表'
  • init data
  [plaintext]
1
2
3
INSERT INTO result (code) VALUES (1), (2), (3);

INSERT INTO mapping (code, msg) VALUES (3, '错误3'), (4, '错误4');
  • query data
  [plaintext]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT * FROM mapping;
+----+------+---------+---------------------+
| id | code | msg     | created_time        |
+----+------+---------+---------------------+
|  1 |    3 | 错误3   | 2017-11-20 10:24:57 |
|  2 |    4 | 错误4   | 2017-11-20 10:24:57 |
+----+------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM result;
+----+------+---------------------+
| id | code | created_time        |
+----+------+---------------------+
|  1 |    1 | 2017-11-20 10:23:15 |
|  2 |    2 | 2017-11-20 10:23:15 |
|  3 |    3 | 2017-11-20 10:23:15 |
+----+------+---------------------+

LEFT JOIN

  [sql]
1
2
3
4
SELECT result.id, result.code, mapping.msg
FROM result
LEFT JOIN mapping
ON result.code = mapping.code;
  • 查询结果
  [plaintext]
1
2
3
4
5
6
7
+----+------+---------+
| id | code | msg     |
+----+------+---------+
|  3 |    3 | 错误3   |
|  1 |    1 | NULL    |
|  2 |    2 | NULL    |
+----+------+---------+

RIGHT JOIN

  [sql]
1
2
3
4
SELECT result.id, result.code, mapping.msg
FROM result
RIGHT JOIN mapping
ON result.code = mapping.code;
  • 查询结果
  [plaintext]
1
2
3
4
5
6
+------+------+---------+
| id   | code | msg     |
+------+------+---------+
|    3 |    3 | 错误3   |
| NULL | NULL | 错误4   |
+------+------+---------+

INNER JOIN

  [sql]
1
2
3
4
SELECT result.id, result.code, mapping.msg
FROM result
INNER JOIN mapping
ON result.code = mapping.code;
  • 查询结果
  [plaintext]
1
2
3
4
5
+----+------+---------+
| id | code | msg     |
+----+------+---------+
|  3 |    3 | 错误3   |
+----+------+---------+

1:n

当然上面都是基础回顾，下面才进入正题。

演示

如果我们的错误码映射表(mapping) 数据不是唯一的，会怎么样呢？

我们再次插入一条数据：

  [sql]
1
INSERT INTO mapping (code, msg) VALUES (3, '错误3');
  • mapping data
  [plaintext]
1
2
3
4
5
6
7
+----+------+---------+---------------------+
| id | code | msg     | created_time        |
+----+------+---------+---------------------+
|  1 |    3 | 错误3   | 2017-11-20 10:24:57 |
|  2 |    4 | 错误4   | 2017-11-20 10:24:57 |
|  3 |    3 | 错误3   | 2017-11-20 10:39:15 |
+----+------+---------+---------------------+
  • left join

这次查询的结果，结果出现了重复的情况。

  [plaintext]
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT result.id, result.code, mapping.msg
    -> FROM result
    -> LEFT JOIN mapping
    -> ON result.code = mapping.code;
+----+------+---------+
| id | code | msg     |
+----+------+---------+
|  3 |    3 | 错误3   |
|  3 |    3 | 错误3   |
|  1 |    1 | NULL    |
|  2 |    2 | NULL    |
+----+------+---------+

解决方式

group by

让 mapping 表对应的数据唯一即可

  [sql]
1
2
3
4
SELECT result.id, result.code, mapping.msg
FROM result
LEFT JOIN (SELECT code, msg FROM mapping GROUP BY code) AS mapping
ON result.code = mapping.code;

result:

  [plaintext]
1
2
3
4
5
6
7
+----+------+---------+
| id | code | msg     |
+----+------+---------+
|  1 |    1 | NULL    |
|  2 |    2 | NULL    |
|  3 |    3 | 错误3   |
+----+------+---------+

msg 没有包含在 GROUP BY 之中

同样的语句如果在 SQL SERVER 2008R2 中测试的话，可能会报错。可以按照实际业务使用聚合函数，比如：

  [sql]
1
2
3
4
SELECT result.id, result.code, mapping.msg
FROM result
LEFT JOIN (SELECT code, MAX(msg) AS msg FROM mapping GROUP BY code) AS mapping
ON result.code = mapping.code;

distinct

如果你只是想让结果保证唯一性，这样写会简洁一些：

注意：如果 mapping 在你的查询中，无法保证唯一性，则使用 GROUP BY 的方式。

  [sql]
1
2
3
4
SELECT DISTINCT result.id, result.code, mapping.msg
FROM result
LEFT JOIN mapping
ON result.code = mapping.code;

result:

  [plaintext]
1
2
3
4
5
6
7
+----+------+---------+
| id | code | msg     |
+----+------+---------+
|  3 |    3 | 错误3   |
|  1 |    1 | NULL    |
|  2 |    2 | NULL    |
+----+------+---------+