复杂sql(跨库,多表,判断,截取字符串) 附带TP5写法

SELECT
	`so`.`id`,
	`sg`.`title`,
	`sg`.`status`,
	`sg`.`total`,
	`so`.`address`,
	`so`.`ordersn`,
	so.STATUS AS order_status,
	`so`.`paytype`,
	`so`.`dispatchprice`,
	`so`.`goodsprice`,
	`so`.`price`,
	`so`.`expresscom`,
	`so`.`expresssn`,
	`so`.`express`,
	`so`.`createtime`,
	IF ( ifnull( `hc`.`name`, '' ) = '', '自主客户', `hc`.`name`) as customer_name,
-- 	`hc`.`name` as customer_name,
-- 	`hsu`.`username` as staff_name
	IF ( ifnull( `hsu`.`username`, '' ) = '', '总部',`hsu`.`username`) as staff_name
FROM
	`shopoa`.`ims_wshoto_shop_order` `so`
	LEFT JOIN `shopoa`.`ims_wshoto_shop_order_goods` `sog` ON `so`.`id` = `sog`.`orderid`
	LEFT JOIN `shopoa`.`ims_wshoto_shop_goods` `sg` ON `sog`.`goodsid` = `sg`.`id` 
	LEFT JOIN `crm`.`5kcrm_customer` `hc` ON  substring_index(substring_index(`so`.`address`,'mobile";s:11:"',-1),'";s:8:"province',1) = `hc`.phone
	LEFT JOIN `crm`.`5kcrm_system_user` as hsu ON hc.creator = hsu.id
where `hc`.creator = 225 AND `so`.`ordersn` is not null
ORDER BY `so`.`id` DESC LIMIT 0,20

$rows = Db::view('shopoa.ims_wshoto_shop_order so','id as so_id')
            ->view('shopoa.ims_wshoto_shop_order_goods sog','id as sog_id','so.id = sog.orderid','left')
            ->view('shopoa.ims_wshoto_shop_goods sg','id as sg_id','sog.goodsid= sg.id','left')
            ->view('crm.5kcrm_customer hc',"id as hc_id","substring_index(substring_index(so.address,'mobile\";s:11:\"',-1),'\";s:8:\"province',1) = hc.phone",'left')
            ->view('crm.5kcrm_system_user hsu',"id as hsu_id",'hc.creator = hsu.id','left')
            ->order('so.id','DESC')
            ->field([
                $this->field,
                "if( ifnull(`hc`.`name`,'') = '','自主客户',`hc`.`name`) as customer_name",
                "if( ifnull(`hsu`.`username`,'') = '','总部',`hsu`.`username`) as staff_name",
            ])
            ->fetchSql()
            ->where($where)
//            ->where('so.ordersn','not null')
            ->paginate($num)
            ->toArray();

场景及一些参数自己根据情况进行修改整合哦