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


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

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

下面我们简化模拟一下：



mysql基本表中有外码时数据怎么输入_SQL



CREATE TABLE TEST 
(
         ID                  　 NUMBER(10) ,
         STATUS             　　NUMBER(1)  ,
         STATUS_NAM  　　　　　　VARCHAR(12),
         CONSTRAINT PK_TEST  PRIMARY KEY(ID, STATUS)
)



mysql基本表中有外码时数据怎么输入_SQL



 




mysql基本表中有外码时数据怎么输入_Code_03

View Code


mysql基本表中有外码时数据怎么输入_SQL



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, '未知');



mysql基本表中有外码时数据怎么输入_SQL



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

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



mysql基本表中有外码时数据怎么输入_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



mysql基本表中有外码时数据怎么输入_SQL



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



mysql基本表中有外码时数据怎么输入_SQL



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;



mysql基本表中有外码时数据怎么输入_SQL



 

mysql基本表中有外码时数据怎么输入_SQL_10

mysql基本表中有外码时数据怎么输入_Code_11

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

 



作者： 潇湘隐者