代码改变世界

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

2012-04-12 00:22  潇湘隐者  阅读(1459)  评论(3)  编辑  收藏  举报

今天有个同事问我一个问题，描述如下： 有一个日志信息表，对应同一个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，而从业务逻辑思考。