mysql Case关键字 使用的实例

        $sql = 'SELECT'
   . ' T1.orders_date AS orders_date'
   . ' T1.customers_id AS customers_id'
   . ' T1.billaddress_company AS billaddress_company'
   . ' T1.billaddress_street AS billaddress_street'
   . ' T1.billaddress_suburb AS billaddress_suburb'
   . ' T1.billaddress_city AS billaddress_city'
   . ' T1.billaddress_state AS billaddress_state'
   . ' T1.billaddress_country_name AS billaddress_country_name'
   . ' T1.billaddress_postcode AS billaddress_postcode'
   . ' T1.shipaddress_company AS shipaddress_company'
   . ' T1.shipaddress_street AS shipaddress_street'
   . ' T1.shipaddress_suburb AS shipaddress_suburb'
   . ' T1.shipaddress_city AS shipaddress_city'
   . ' T1.shipaddress_state AS shipaddress_state'
   . ' T1.shipaddress_country_name AS shipaddress_country_name'
   . ' T1.shipaddress_postcode AS shipaddress_postcode'
   . ' T1.duedate AS duedate'
   . ' T1.servicedate AS servicedate'
   . ' T1.fob AS fob'
   . ' T1.ponumber AS ponumber'
   . ' T1.tax1total AS tax1total'
   . ' T1.tax2total AS tax2total'
   . ' T1.exchangerate AS exchangerate'
   . ' T1.purchaseorder_status AS purchaseorder_status'
   . ' T1.orders_payflag AS orders_payflag'
   . ' T1.orders_shipflag AS orders_shipflag'
   . ' T1.payment_id AS payment_id'
   . ' T1.shipping_id AS shipping_id'
   . ' T1.shipping_no AS shipping_no'
   . ' T1.shipping_cost AS shipping_cost'
   . ' T1.orders_date_ended AS orders_date_ended'
   . ' T1.orders_back AS orders_back'
   . ' T1.products_score AS products_score'
   . ' T1.products_content AS products_content'
   . ' T1.orders_comments AS orders_comments'
   . ' T1.update_date_time AS update_date_time'
   . ' T1.orders_year AS orders_year'
   . ' T3.customers_id AS customers_id'
   . ' T3.customers_gander AS customers_gander'
   . ' T3.customers_firstname AS customers_firstname'
   . ' T3.customers_middlename AS customers_middlename'
   . ' T3.customers_lastname AS customers_lastname'
   . ' T3.customers_company AS customers_company'
   . ' T3.shipping_id AS shipping_id'
   . ' T3.customers_telephone AS customers_telephone'
   . ' T3.customers_email AS customers_email'
   . ' T3.customers_fax AS customers_fax'
   . ' T3.customers_credit AS customers_credit'
   . ' T3.customers_type AS customers_type'
   . ' T3.customers_role AS customers_role'
   . ' T2.orderTotal AS orderTotal'
   . ' T4.tax1rate AS tax1rate'
   . ' T4.tax2rate AS tax2rate'
   . ' T4.istax1exempt AS istax1exempt'
   . ' T4.istax2exempt AS istax2exempt'
   . ' T4.ispiggybackrate AS ispiggybackrate'
   . " CASE WHEN istax1exempt = '0'"
   . ' THEN tax1rate'
   . " ELSE '0'"
   . ' END AS tax1'
   . " CASE WHEN istax2exempt = '0'"
   . ' THEN tax2rate'
   . " ELSE '0'"
   . ' END AS tax2'
   . " CASE WHEN istax1exempt = '0'"
   . ' THEN (tax1rate * orderTotal)'
   . " ELSE '0' END AS gst"
   . " CASE WHEN istax2exempt = '0'"
   . " THEN CASE WHEN (ispiggybackrate = '1' AND istax1exempt = '0')"
   . ' THEN (tax2rate * ((1 + tax1rate)* orderTotal))'
   . ' ELSE (tax2rate * orderTotal)'
   . ' END'
   . " ELSE '0'"
   . ' END AS ont'
   . " CASE WHEN istax1exempt = '0' AND istax2exempt = '0'"
   . " THEN CASE WHEN ispiggybackrate = '0'"
   . ' THEN ((tax1rate + tax2rate) * orderTotal)'
   . ' ELSE ((tax1rate * orderTotal) + tax2rate *((1+tax1rate )* orderTotal))'
   . ' END'
   . " WHEN istax1exempt = '0' AND istax2exempt = '1'"
   . ' THEN (tax1rate * orderTotal)'
   . " WHEN istax1exempt = '1' AND istax2exempt = '0'"
   . ' THEN (tax2rate * orderTotal)'
   . " WHEN istax1exempt = '1' AND istax2exempt = '1'"
   . " THEN '0'"
   . ' END AS taxtotal'
   . ' FROM orders AS T1'
   . ' LEFT JOIN'
   . ' ('
   . ' SELECT'
   . ' orders_year'
   . ' orders_num'
   . ' SUM(final_price * orders_products.orders_quantity) AS orderTotal'
   . ' FROM orders_products'
   . ' WHERE CONCAT(orders_year, orders_num) ='
   . $pOrderId
   . ' ) AS T2'
   . ' ON (T1.orders_num = T2.orders_num AND T1.orders_year = T2.orders_year)'
   . ' LEFT JOIN customers AS T3'
   . ' ON T1.customers_id = T3.customers_id'
   . ' LEFT JOIN tax_rate AS T4'
   . ' ON T1.shipaddress_state = T4.zone_id'
   . ' WHERE CONCAT(T1.orders_year, T1.orders_num) ='
   . $pOrderId;

       return $sql;