首页 > 代码库 > SQL条件逻辑——SQL读书笔记
SQL条件逻辑——SQL读书笔记
《SQL学习指南》中的第11章
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读书笔记
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。