mysql Case关键字使用的实例

- 日理万妓 2022-08-23 14:53 173阅读 0赞

$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;