分组之后排序取第一条
oracle 分组后取每组第一条数据
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn,
test1.* FROM test1)
WHERE rn = 1
关联查询
今天关联查询遇到一个有趣的现象。
以前遇到过没整理，记录一下，方便以后查阅。
本文测试环境：
MySQL 5.7.17
前车之鉴
一言以蔽之
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
数据准备
- create table
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
INSERT INTO result (code) VALUES (1), (2), (3);
INSERT INTO mapping (code, msg) VALUES (3, '错误3'), (4, '错误4');
- query data
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
SELECT result.id, result.code, mapping.msg
FROM result
LEFT JOIN mapping
ON result.code = mapping.code;
- 查询结果
+----+------+---------+
| id | code | msg |
+----+------+---------+
| 3 | 3 | 错误3 |
| 1 | 1 | NULL |
| 2 | 2 | NULL |
+----+------+---------+
RIGHT JOIN
SELECT result.id, result.code, mapping.msg
FROM result
RIGHT JOIN mapping
ON result.code = mapping.code;
- 查询结果
+------+------+---------+
| id | code | msg |
+------+------+---------+
| 3 | 3 | 错误3 |
| NULL | NULL | 错误4 |
+------+------+---------+
INNER JOIN
SELECT result.id, result.code, mapping.msg
FROM result
INNER JOIN mapping
ON result.code = mapping.code;
- 查询结果
+----+------+---------+
| id | code | msg |
+----+------+---------+
| 3 | 3 | 错误3 |
+----+------+---------+
1:n
当然上面都是基础回顾，下面才进入正题。
演示
如果我们的错误码映射表(mapping) 数据不是唯一的，会怎么样呢？
我们再次插入一条数据：
INSERT INTO mapping (code, msg) VALUES (3, '错误3');
- mapping data
+----+------+---------+---------------------+
| 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
这次查询的结果，结果出现了重复的情况。
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 表对应的数据唯一即可
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:
+----+------+---------+
| id | code | msg |
+----+------+---------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
| 3 | 3 | 错误3 |
+----+------+---------+
msg 没有包含在 GROUP BY 之中
同样的语句如果在 SQL SERVER 2008R2 中测试的话，可能会报错。可以按照实际业务使用聚合函数，比如：
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 的方式。
SELECT DISTINCT result.id, result.code, mapping.msg
FROM result
LEFT JOIN mapping
ON result.code = mapping.code;
result:
+----+------+---------+
| id | code | msg |
+----+------+---------+
| 3 | 3 | 错误3 |
| 1 | 1 | NULL |
| 2 | 2 | NULL |
+----+------+---------+
