SQL条件逻辑——SQL读书笔记

    1.1 概念：      条件逻辑：条件逻辑是程序执行时从多个路径中选取其一的能力      1）简单例子演示：
         例子1：查询客户信息时根据客户类型从individual表中检索fname/lname列或者从business表中  获取name列 (左外连接)                   SELECT c.cust_id,c.fed_id,c.cust_type_cd,     CONCAT(i.fname,‘ ‘,i.lname) AS indv_name,     b.`name` AS business_name     FROM customer c     LEFT JOIN individual i     ON c.cust_id = i.cust_id     LEFT JOIN business b     ON c.cust_id = b.cust_id;       结果如图所示        

                    

           例子2：查询客户信息时根据客户类型从individual表中检索fname/lname列或者从business表中  获取name列 (使用case表达式使用条件逻辑决定客户类型，进而返回恰当的字符串)                           SELECT c.cust_id, c.fed_id,     CASE         WHEN c.cust_type_cd = ‘I‘             THEN CONCAT(i.fname,‘ ‘,i.fname)         WHEN c.cust_type_cd = ‘B‘             THEN b.`name`         ELSE ‘Unkown‘     END AS `name` -- 给这个条件判断的结果取个别名     FROM customer c     LEFT JOIN individual i     ON c.cust_id = i.cust_id     LEFT JOIN business b     ON c.cust_id = b.cust_id;                       结果如图所示                   

          分析：查询只返回由case表达式生成的单个name列，这个从查询的第二行起的case表达式首先检查cust_type_cd列的值，然后依据该值决定返回个人名称还是企业名称。

         1.2 case表达式      主流的数据库服务器中模拟条件判断的内置函数包括：Oracle的decode()函数，MySQL中if()函数以及SQL Server的coalesce()函数。       case表达式作为一种条件逻辑表达式，具备以下特点：           case表达式是SQL标准的一部分（SQL92），并且在多种数据库中实现；           case表达式已经内置于SQL语法，可以用于select,insert,update和delete语句。       下面介绍两种不同类型的case表达式        1）查找型case表达式，其语法如下：           CASE                WHEN C1 THEN E1                WHEN C2 THEN E2                ....                [ELSE ED]           END AS 别名
       其中C1,C2....代表条件，E1,E2....表示case表达式返回的表达式结果，else子句是可选的。        注意：case表达式返回的类型可以为日期型，数字性，字符串类型等，但是同一个case表达式中每个THEN返回的表达式结果必须相同
          例子1：使用子查询代替外连接从individual和business表中检索个人名称/企业名称                  SELECT c.cust_id,c.fed_id,     CASE         WHEN c.cust_type_cd = ‘I‘         THEN (             SELECT CONCAT(i.fname,‘ ‘,i.lname)             FROM individual i             WHERE i.cust_id = c.cust_id         )         WHEN c.cust_type_cd = ‘B‘         THEN (             SELECT b.`name`             FROM business b             WHERE b.cust_id = c.cust_id         )      END  AS `name`                   FROM customer c                          结果如图所示                          

        2）简单case表达式，其语法如下：

     CASE V0         WHEN V1 THEN E1         WHEN V2 THEN E2         .....         [ELSE END]      END     简单case表达式主要是通过自动构建等式条件，通过对V1,V2....与V0的值进行匹配，然后返回相应的表达式结果                           例子1.下面修改1.1中例子1那个查找型case表达式              SELECT c.cust_id, c.fed_id,     CASE c.cust_type_cd         WHEN ‘I‘             THEN CONCAT(i.fname,‘ ‘,i.fname)         WHEN ‘B‘             THEN b.`name`         ELSE ‘Unkown‘     END AS `name` -- 给这个条件判断的结果取个别名     FROM customer c     LEFT JOIN individual i     ON c.cust_id = i.cust_id     LEFT JOIN business b     ON c.cust_id = b.cust_id;                      结果如图所示                          

 

          分析：上面的例子将查找型case表达式转化成简单case表达式，注意这里由于查找型case表达式的条件单一，这样转换并不会出现什么问题，但是在范围条件，不等条件以及基于and/or/not这些运算符的复合条件对于简单case条件并不适用。             
 1.3 case表达式用途       case表达式适用于那些场景：1.结果集变换，2.选择性聚合，3.存在性检查，4.除0失误，5.有条件更新，6.null值处理
      1）结果集变换                     结果集变换：对结果集的显示形式进行变换，如多行转列           例子1.查询展示从2000年到2005年每年的开户数目：          SELECT YEAR(a.open_date) `YEAR`, COUNT(*) YearCount FROM account a WHERE (a.open_date >= ‘2000-01-01‘ AND a.open_date <= ‘2005-12-30‘ ) GROUP BY YEAR(a.open_date);          结果如图所示           

     例子2.将上面的结果变换成单行多列显示

    SELECT     SUM(         CASE                 WHEN EXTRACT(YEAR FROM a.open_date) = 2000                 THEN 1                 ELSE 0         END    ) year_2000,     SUM(         CASE                 WHEN EXTRACT(YEAR FROM a.open_date) = 2001                 THEN 1                 ELSE 0         END    )  year_2001,     SUM(         CASE                 WHEN EXTRACT(YEAR FROM a.open_date) = 2002                 THEN 1                 ELSE 0         END    )  year_2002,     SUM(         CASE                 WHEN EXTRACT(YEAR FROM a.open_date) = 2003                 THEN 1                 ELSE 0         END    ) year_2003,     SUM(         CASE                 WHEN EXTRACT(YEAR FROM a.open_date) = 2004                 THEN 1                 ELSE 0         END    ) year_2004,         SUM(         CASE                 WHEN EXTRACT(YEAR FROM a.open_date) = 2005                 THEN 1                 ELSE 0         END    )  year_2005      FROM account a;              结果如图所示        

       分析：这种少量数据的由行转列的可以这样实现，但是当行数过多时，那就要用到后面的解决办法了。

         2）选择性聚合                  选择性聚合：通过判断条件进行对某些数据进行查找，筛选，聚合
                例子1.查找account表中那些账户余额与transaction表中账户余额,代收余额不相符的地方。          分析：           1）由于交易账户总是正的，所以读者需要查看交易类型是借款(‘DBT‘)还是存款(‘CBT‘),借款则应该将金额数变成负的(乘以-1);           2) 如果funds_avail_date列中的日期大于当前日期(未到期)，交易应该被加到代收余额总和，而不是可用余额总和;           3)同时，有些交易需要被排除在可用余额之外，而所有交易应该都被包含在代收余额之内。
    
     SELECT a.account_id AS unbalance_account_id FROM account a WHERE (a.avail_balance, a.pending_balance) <>( -- avail_balance账户余额,pending_balance 代收余额         SELECT             SUM(             CASE                 WHEN t.funds_avail_date > CURRENT_TIMESTAMP()                 THEN 0                 WHEN t.txn_type_cd = ‘DBT‘                 THEN t.amount * -1                 ELSE t.amount             END         ),         SUM(             CASE                 WHEN t.txn_type_cd = ‘DBT‘                 THEN  t.amount * -1                 ELSE t.amount             END         )         FROM `transaction` t         WHERE t.account_id = a.account_id      )      结果如图所示      

     3）存在性检查

                    存在性检测：对某些数据进行是否存在进行判断，或者对数据量进行统计 
               例子1.查询客户是否存在支票账户或者储蓄账户          SELECT c.cust_id,c.fed_id,c.cust_type_cd, CASE     WHEN EXISTS(         SELECT 1 FROM account a         WHERE a.cust_id = c.cust_id         AND a.product_cd = ‘CHK‘     )   THEN ‘Y‘     ELSE ‘N‘ END AS has_checking , CASE     WHEN EXISTS(         SELECT 1 FROM account a         WHERE a.cust_id = c.cust_id         AND a.product_cd = ‘SAV‘     )     THEN ‘Y‘     ELSE ‘N‘ END AS has_saving FROM customer c;            结果如图所示           

          分析： 每个case表达式包含了一个对account表的关联子查询：一个查找支票账户，另一个查找储蓄账户。

          由于每一个when子句都使用了exists运算符，因此只要客户至少存在一个相应的账户那么条件为真                     例子2. 使用简单case表达式为每个客户计算账户数目，然后返回None,1,2,3+              SELECT CASE COUNT(a.account_id)     WHEN 0 THEN ‘None‘     WHEN 1 THEN ‘1‘     WHEN 2 THEN ‘2‘     ELSE ‘3+‘ END AS AccountCount ,c.cust_id FROM customer c RIGHT JOIN account a ON c.cust_id = a.cust_id GROUP BY c.cust_id;                     结果如图所示           

        4）除0错误

                 除0错误检测：执行除法运算时避免分母为0的情况，进行判断。同时不同数据库对除0出错进行不同处理方法，Oracle在遇到0分母时会抛出一个错误，而MySQL只是简单的将结果值置为null.        例子1.查询计算同一产品类型的所有账户的每个账户余额与总余额的比率                     SELECT a.product_cd,SUM(a.avail_balance) FROM  account a GROUP BY a.product_cd;   SELECT an.cust_id,an.product_cd,an.avail_balance/ CASE     WHEN newCount.totalAvail = 0 THEN 1   ELSE newCount.totalAvail END AS rate FROM account an INNER JOIN (     SELECT a.product_cd,SUM(a.avail_balance) AS totalAvail     FROM  account a     GROUP BY a.product_cd ) AS newCount ON an.product_cd = newCount.product_cd;   结果如图所示             5）有条件更新                  有条件更新：更新表中的行时，常常需要指定的列应该置什么值，但这个值往往需要根据其他的表中数值进行判断，才对该值进行更新。          例子1.假定插入一个ID为999的一个交易，但此时需要修改account表中avail_balance,prending_balance和last_activity_date这3列的值，后两个值比较容易更新，更新avavil_balance列则必须检查transaction表的funds_avail_date列判断交易资金是否立即可以使用。          UPDATE account a SET a.last_activity_date = CURRENT_TIMESTAMP(), a.pending_balance = a.pending_balance + (     SELECT t.amount*         CASE             WHEN t.txn_type_cd = ‘DBT‘             THEN -1             ELSE  1         END     FROM `transaction` t     WHERE t.txn_id = 22 ), a.avail_balance = a.avail_balance + (     SELECT t.amount*      CASE             WHEN t.funds_avail_date > CURRENT_TIMESTAMP() THEN 0             WHEN t.txn_type_cd = ‘DBT‘ THEN -1             ELSE 1         END     FROM `transaction` t     WHERE t.txn_id = 22 ) WHERE a.account_id = (     SELECT t.account_id     FROM `transaction` t     WHERE t.txn_id = 22 )          修改之前的数据：          

                    修改之后的数据：

         

                    在transaction中插入的那条数据

          

                   分析：这个语句共包含2个case语句，第一个case表达式对交易账户金额进行判断，是否是存款还是借款

          第二个case表达式进行两种判断，首先用于检查资金的可用性日期，如果日期是未来，则只对可用余额加0           否则，返回1;然后对交易账户金额进行判断，是否是存款还是借款,存款返回1,借款返回-1。                             6）null值处理                   null值处理：null是某列的值未知时存储到表中的值，不过检索时显示null值或者null参与表达式运算会出现错误         样例： SELECT <some calculation> +               CASE                  WHEN avail_balance IS NULL THEN 0                 ELSE avail_balance               END
            + <some calculation>      

SQL条件逻辑——SQL读书笔记

标签：