例子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读书笔记
标签：