在SQL里如何写条件逻辑？-白红宇

在SQL里如何写条件逻辑？

阅读量：6072 次

发布时间：2019-06-20

本文共 1047 字，大约阅读时间需要 3 分钟。

主要涉及CASE,WHEN之类。。

不同的服务器上实现if...else...是不一样的。

建议用CASE ,WHEN，因为它们是SQL国标呢。

mysql> SELECT     -> SUM(CASE     -> WHEN EXTRACT(YEAR FROM open_date) = 2000 THEN 1    -> ELSE 0    -> END) year_2000,    -> SUM(CASE    -> WHEN EXTRACT(YEAR FROM open_date) = 2001 THEN 1    -> ELSE 0    -> END) year_2001    -> FROM account    -> WHERE open_date > '1999-12-31' AND open_date < '2006-01-01';

mysql> 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 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 has_savings    -> FROM customer c;

mysql> SELECT c.cust_id, c.fed_id, c.cust_type_cd,    -> CASE (SELECT COUNT(*) FROM account a    -> WHERE a.cust_id = c.cust_id)    -> WHEN 0 THEN 'None'    -> WHEN 1 THEN '1'    -> WHEN 2 THEN '2'    -> ELSE '3+'    -> END num_accounts    -> FROM customer c;