在SQL里如何写条件逻辑？

主要涉及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;