一条sql语句

SELECT
	t.id,
	t.id_uw,
	t.card_id,
	t.list_id,
	t.trade_time,
	t.trade_type,
CASE
		
		WHEN t.trade_type = 3 
		AND ods.order_entry_scene = "i_interface" 
		AND instr( t.trade_no, '_' )= 0 THEN
			t.trade_no ELSE " " 
		END AS trade_no,
	CASE
			
			WHEN t.trade_type IN ( 3, 4, 6 ) THEN
			- ROUND( t.trade_amount / 100, 2 ) ELSE ROUND( t.trade_amount / 100, 2 ) 
		END AS trade_amount,
	CASE
			
			WHEN t.trade_type IN ( 3, 4, 6 ) THEN
			- ROUND( t.pay_amount / 100, 2 ) ELSE ROUND( t.pay_amount / 100, 2 ) 
		END AS pay_amount,
	CASE
			
			WHEN t.trade_type IN ( 3, 4, 6 ) THEN
			- ROUND(( t.trade_amount - t.pay_amount ) / 100, 2 ) ELSE ROUND(( t.trade_amount - t.pay_amount ) / 100, 2 ) 
		END AS discount_amount,
		g.NAME AS guide_name,
		g.staff_num AS guide_num,
		g.phone AS guide_phone,
		g.stcode AS guide_stcode,
		g.part_three AS guide_branch,
		g.part_two AS guide_company,
		ui.phone,
		u.NAME AS user_name,
		ods.pos_mch_id AS trade_store_id,
		ods.pos_mch_name AS trade_store_name 
	FROM
		`dx_store_card_trade` `t`
		LEFT JOIN `shopping_guide` `g` ON `g`.`id_guide` = `t`.`id_guide_confirm`
		LEFT JOIN `user_ext_info` `ui` ON `ui`.`id_uw` = `t`.`id_uw`
		LEFT JOIN `user_wxsp` `u` ON `u`.`id_uw` = `t`.`id_uw`
		LEFT JOIN `dx_store_card_trade_ods` `ods` ON `ods`.`listid` = `t`.`list_id` 
	WHERE
		`trade_state` IN ( 2, 4 ) 
		AND `trade_type` < 7 
		AND `t`.`trade_type` IN ( '3' ) 
	ORDER BY
		`trade_time` DESC 
	LIMIT 780,
	10

两点关键：

一个函数 instr

一个判断 CASE