平时在开发中,有些sql只是取出了基本数据,于是还要边循环边查询数据库,非常不利于代码的效率。
mysql中,使用case when来进行简单的逻辑判断,这样,可以省去很多麻烦,而且大大提高页面的整体访问速度。
另外还有个很大的好处:一些数据是用再循环中做差得到的结果,对于这类数据是没法直接进行排序,使用case when后,可以把数据在sql中整理取出来,可以用order by直接排序
$sql = "select o.*,s.company_name,s.leibie,itm.tuanhao,itm.start_time as item_start_time,s.is_supplier,"
. "case when s.leibie=1 then coalesce((select sum(fukuan_money) from finance_lss_fukuan sk where sk.order_id=o.id and sk.is_shenhe=1 and fukuan_type=2),0) "
. " when s.is_supplier=1 then coalesce((select sum(shoukuan_money) from finance_pfs_shoukuan sk where sk.order_id=o.id and sk.is_shenhe=1 and shoukuan_type=2),0) "
. " when s.is_supplier=0 then coalesce((select sum(shoukuan_money) from finance_lss_shoukuan sk where sk.order_id=o.id and sk.is_shenhe=1),0) end as yishouMoney "
. "from tourline_order o join supplier s on o.lss_id=s.id "
. "left join tourline_item itm on o.tourline_item_id=itm.id "
. "where " . $condition . " order by " . $param['orderField'] . " " . $param["orderDirection"] . " limit " . $limit;