SQL语言艺术实践篇——局外思考

今天有个同事问我一个问题，描述如下： 有一个日志信息表，对应同一个ID，可能有一条、两条、三条不同状态的记录。例如ID= 10001的日志记录可能有三条，一条记录状态为正确， 一条记录状态为错误， 一条记录状态是未知。也有可能只有其中一条记录或两条，现在的问题是，对应同一日志ID，我们只需要取一条记录，取数规则是：
1：如果有状态为正确、错误、未知三条记录，我们只取状态为正确的记录。
2：如果只有状态为正确、错误状态两条记录的，我们只取状态为正确的记录
3：如果只有状态为错误、未知记录两条记录的，我们只取状态为错误的记录
4：如果只有状态为正确、未知记录两条记录的，我们只取状态为正确的记录
5：如果只有一种状态的记录，我们就取这条状态的记录。

归纳起来就是状态的优先级别为：正确 错误 未知。

下面我们简化模拟一下：

CREATE TABLE TEST 

 ID NUMBER(10) ,

 STATUS NUMBER(1) ,

 STATUS_NAM VARCHAR(12),

 CONSTRAINT PK_TEST PRIMARY KEY(ID, STATUS)

)

INSERT INTO TEST (ID, STATUS, STATUS_NAM)

values (1001, 1, 正确);

INSERT INTO TEST (ID, STATUS, STATUS_NAM)

values (1001, 2, 错误);

INSERT INTO TEST (ID, STATUS, STATUS_NAM)

values (1001, 3, 未知);

INSERT INTO TEST (ID, STATUS, STATUS_NAM)

values (1002, 1, 正确);

INSERT INTO TEST (ID, STATUS, STATUS_NAM)

values (1002, 2, 错误);

INSERT INTO TEST (ID, STATUS, STATUS_NAM)

values (1003, 1, 正确);

INSERT INTO TEST (ID, STATUS, STATUS_NAM)

values (1003, 3, 未知);

INSERT INTO TEST (ID, STATUS, STATUS_NAM)

values (1004, 2, 错误);

INSERT INTO TEST (ID, STATUS, STATUS_NAM)

values (1004, 3, 未知);

（有兴趣的可以先自己试试，然后看下文解决方法）

刚考虑这个问题，确实有点头大，问题逻辑比较复杂，想用一条SQL写出来，确实比较头大。当时头脑中被逻辑给搅晕了：三条记录时过滤出一条记录，两 条记录时过滤出一条记录，只有一条记录时......。觉得一条SQL实现比较不太现实，可能要借助自定义函数来实现这个功能，当时，另外一个同事迅速给 出了一个方案。

SELECT ID, CASE WHEN EXISTS (SELECT 1 FROM TEST T1 WHERE T1.ID = T.ID AND STATUS_NAM = 正确) THEN 1 

 WHEN EXISTS (SELECT 1 FROM TEST T2 WHERE T2.ID = T.ID AND STATUS_NAM = 错误) THEN 2

 WHEN EXISTS (SELECT 1 FROM TEST T3 WHERE T3.ID = T.ID AND STATUS_NAM = 未知) THEN 3

 END AS STATUS

 FROM TEST T

 GROUP BY ID

给人眼前一亮，居然可以这样处理，其实我们换个思维来考虑，不管这个日志ID有几条记录，我只需要一条记录，那么我可以使用ROW_NUMBER函数对 ID字段分组，然后取其中的一条ROWNUM，而我可以给三个状态值恰好按：正确——》1、 错误——》2、未知——》3，那么我只需要对记录按状态排序，取序号为1的记录即可。脚本如下：

SELECT ID, STATUS, STATUS_NAM

FROM (SELECT ID,

 ROW_NUMBER() OVER(PARTITION BY ID ORDER BY STATUS) AS ROWNUMS,

 STATUS,

 STATUS_NAM

 FROM TEST)

 WHERE ROWNUMS = 1;

 

刚好这阵子正好看过《SQL语言艺术》，有一章节就讲：战略大于战术，有时候解决问题，仅仅需要站在局外思考(Think Outside)，不要因为太关注问题本身而受到干扰。我们需要大胆的思维，站得跟远一些。试着从大局的角度来看待问题。这样就能让问题迎刃而解。其实我 刚开始一直想不到什么方法，就是因为自己没有跳出固定思维的模式，老在怎么从3条记录取一条、二条取一条、一条记录就取单条记录的思维模式里面，没有跳出 SQL，而从业务逻辑思考。

直播预告 | PolarDB-X 动手实践系列——如何在 PolarDB-X 中优化慢 SQL 在数据库的开发和运维过程中，慢 SQL 是最常见的问题之一， PolarDB-X 提供了原生的慢 SQL 分析能力，可以很好的帮助我们解决这一问题。本期分享将围绕这一场景演示如何在 PolarDB-X 中分析和解决慢 SQL。
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意，不善言辞。执意做一名会写代码的DBA，混迹于IT行业
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载