MySQL case when 实例

 1 <select id="selectByItem" resultType="com.shinho.dc3.dsfa.dto.SfaOrderRes" parameterType="com.shinho.dc3.dsfa.dto.SfaOrderSearch">
 2   select  a.orderid,a.ordercode,a.e_number,a.storeid,a.storecode,a.storename,a.bs_name,a.depment_id,
 3       case a.order_type
 4           when '100' then
 5           '销售'
 6           when '200' then
 7           '退货'
 8           else
 9           ''
10       end order_type,
11       a.amt,
12       a.row_count,
13       a.link_man,
14       a.link_tel,
15       a.addr,
16       a.pay_yu_amt,
17       a.pay_sh_amt,
18       a.pay_type,
19       a.gis,
20       a.remark,
21       a.did,
22       a.sfa_type,
23       a.deliverydate,
24       case a.DN_STATUS
25           when '100' then
26           '成功'
27           when '80' then
28           '失败'
29           when '10' then
30           '未创建'
31           else
32           ''
33       end dn_status,
34       a.createtime,
35       a.insdt,
36       a.do_vbeln,
37       a.dn_vbeln,
38       a.do_msg,
39       a.dn_msg,
40       a.ca_vbeln,
41       a.ca_msg,
42       a.car_num,
43       a.do_status
44   from sfa_order a
45   where a.SFA_TYPE = '2'
46   <if test="pkeyWord != null and pkeyWord!=''" >
47     <bind name="pkeyWord" value="'%' + pkeyWord + '%'"/>
48   and
49     (UPPER (a.ordercode) like #{pkeyWord} or
50     UPPER (a.storecode) like #{pkeyWord} or
51     UPPER (a.storename) like #{pkeyWord} or
52     UPPER (a.do_vbeln) like #{pkeyWord} or
53     UPPER (a.dn_vbeln) like #{pkeyWord} or
54     UPPER (a.bs_name) like #{pkeyWord}
55     )
56 </if>
57   <if test="pStatus != null" >
58     and a.do_status= #{pStatus}
59   </if>
60   <if test="pDid != null" >
61     and a.did= #{pDid}
62   </if>
63   <if test="pDateStart != null" >
64     and a.insdt &gt;= pDateStart
65   </if>
66   <if test="pDateEnd != null" >
67     and a.insdt &lt;= pDateEnd
68   </if>
69   order by a.insdt desc
70 </select>