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)
)
View Code
 1 INSERT INTO TEST (ID, STATUS, STATUS_NAM)
 2 values (1001, 1, '正确');
 3 
 4 INSERT INTO TEST (ID, STATUS, STATUS_NAM)
 5 values (1001, 2, '错误');
 6 
 7 INSERT INTO TEST (ID, STATUS, STATUS_NAM)
 8 values (1001, 3, '未知');
 9 
10 INSERT INTO TEST (ID, STATUS, STATUS_NAM)
11 values (1002, 1, '正确');
12 
13 INSERT INTO TEST (ID, STATUS, STATUS_NAM)
14 values (1002, 2, '错误');
15 
16 INSERT INTO TEST (ID, STATUS, STATUS_NAM)
17 values (1003, 1, '正确');
18 
19 INSERT INTO TEST (ID, STATUS, STATUS_NAM)
20 values (1003, 3, '未知');
21 
22 INSERT INTO TEST (ID, STATUS, STATUS_NAM)
23 values (1004, 2, '错误');
24 
25 INSERT INTO TEST (ID, STATUS, STATUS_NAM)
26 values (1004, 3, '未知');

(有兴趣的可以先自己试试,然后看下文解决方法)

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

1 SELECT ID,  CASE   WHEN EXISTS (SELECT 1 FROM TEST T1 WHERE T1.ID = T.ID AND STATUS_NAM = '正确') THEN 1 
2                    WHEN EXISTS (SELECT 1 FROM TEST T2 WHERE T2.ID = T.ID AND STATUS_NAM = '错误') THEN 2
3                    WHEN EXISTS (SELECT 1 FROM TEST T3 WHERE T3.ID = T.ID AND STATUS_NAM = '未知') THEN 3
4              END AS STATUS
5  FROM TEST T
6  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,而从业务逻辑思考。

扫描上面二维码关注我
如果你真心觉得文章写得不错,而且对你有所帮助,那就不妨帮忙“推荐"一下,您的“推荐”和”打赏“将是我最大的写作动力!
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.
原文地址:https://www.cnblogs.com/kerrycode/p/2442908.html