{"id":24,"date":"2022-06-29T17:22:58","date_gmt":"2022-06-29T09:22:58","guid":{"rendered":"https:\/\/www.zhukaifu.cn\/?p=24"},"modified":"2022-06-29T17:22:58","modified_gmt":"2022-06-29T09:22:58","slug":"%e4%b8%80%e6%9d%a1sql%e8%af%ad%e5%8f%a5","status":"publish","type":"post","link":"https:\/\/www.zhukaifu.cn\/?p=24","title":{"rendered":"\u4e00\u6761sql\u8bed\u53e5"},"content":{"rendered":"\n<p><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n\tt.id,\n\tt.id_uw,\n\tt.card_id,\n\tt.list_id,\n\tt.trade_time,\n\tt.trade_type,\nCASE\n\t\t\n\t\tWHEN t.trade_type = 3 \n\t\tAND ods.order_entry_scene = \"i_interface\" \n\t\tAND instr( t.trade_no, '_' )= 0 THEN\n\t\t\tt.trade_no ELSE \" \" \n\t\tEND AS trade_no,\n\tCASE\n\t\t\t\n\t\t\tWHEN t.trade_type IN ( 3, 4, 6 ) THEN\n\t\t\t- ROUND( t.trade_amount \/ 100, 2 ) ELSE ROUND( t.trade_amount \/ 100, 2 ) \n\t\tEND AS trade_amount,\n\tCASE\n\t\t\t\n\t\t\tWHEN t.trade_type IN ( 3, 4, 6 ) THEN\n\t\t\t- ROUND( t.pay_amount \/ 100, 2 ) ELSE ROUND( t.pay_amount \/ 100, 2 ) \n\t\tEND AS pay_amount,\n\tCASE\n\t\t\t\n\t\t\tWHEN t.trade_type IN ( 3, 4, 6 ) THEN\n\t\t\t- ROUND(( t.trade_amount - t.pay_amount ) \/ 100, 2 ) ELSE ROUND(( t.trade_amount - t.pay_amount ) \/ 100, 2 ) \n\t\tEND AS discount_amount,\n\t\tg.NAME AS guide_name,\n\t\tg.staff_num AS guide_num,\n\t\tg.phone AS guide_phone,\n\t\tg.stcode AS guide_stcode,\n\t\tg.part_three AS guide_branch,\n\t\tg.part_two AS guide_company,\n\t\tui.phone,\n\t\tu.NAME AS user_name,\n\t\tods.pos_mch_id AS trade_store_id,\n\t\tods.pos_mch_name AS trade_store_name \n\tFROM\n\t\t`dx_store_card_trade` `t`\n\t\tLEFT JOIN `shopping_guide` `g` ON `g`.`id_guide` = `t`.`id_guide_confirm`\n\t\tLEFT JOIN `user_ext_info` `ui` ON `ui`.`id_uw` = `t`.`id_uw`\n\t\tLEFT JOIN `user_wxsp` `u` ON `u`.`id_uw` = `t`.`id_uw`\n\t\tLEFT JOIN `dx_store_card_trade_ods` `ods` ON `ods`.`listid` = `t`.`list_id` \n\tWHERE\n\t\t`trade_state` IN ( 2, 4 ) \n\t\tAND `trade_type` &lt; 7 \n\t\tAND `t`.`trade_type` IN ( '3' ) \n\tORDER BY\n\t\t`trade_time` DESC \n\tLIMIT 780,\n\t10\r\n<\/code><\/pre>\n\n\n\n<p>\u4e24\u70b9\u5173\u952e\uff1a<\/p>\n\n\n\n<p>\u4e00\u4e2a\u51fd\u6570 instr<\/p>\n\n\n\n<p>\u4e00\u4e2a\u5224\u65ad CASE<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e24\u70b9\u5173\u952e\uff1a \u4e00\u4e2a\u51fd\u6570 instr \u4e00\u4e2a\u5224\u65ad CASE<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-24","post","type-post","status-publish","format-standard","hentry","category-thinking"],"_links":{"self":[{"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=\/wp\/v2\/posts\/24","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=24"}],"version-history":[{"count":1,"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=\/wp\/v2\/posts\/24\/revisions"}],"predecessor-version":[{"id":25,"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=\/wp\/v2\/posts\/24\/revisions\/25"}],"wp:attachment":[{"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=24"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=24"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.zhukaifu.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=24"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}