TP里的case when用法。可以代替if判断

最新推荐文章于 2024-04-24 22:58:04 发布

原创最新推荐文章于 2024-04-24 22:58:04 发布 · 5.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#case when thinkphp

thinkphp 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用ThinkPHP框架进行复杂联表查询的方法，包括动态SQL构建及视图方式查询，涉及字段选择、条件过滤及结果排序等操作。

public functiondetail_jump(Request$request)

{

$name=
$request->param('name');

$video_id=
$request->param('id');

$sql="(select
 *,(case when picture_type = 'remote' then picture_url when picture_type = 'local' then picture_path else 'ww' end) pic_addr from tv_picture)";

$sql="SELECT
 v.*,p1.pic_addr as p1_pic,p2.pic_addr as p2_pic FROM tv_video AS
 v JOIN {$sql}
 AS p1 ON v.picture_id = p1.picture_id JOIN tv_cate AS c ON c.`cate_id` = v.`cate_plat_id` JOIN{$sql}
 AS p2 ON p2.`picture_id`

= c.`picture_id` WHERE video_id ={$video_id}";

$video_res=
 Db::query($sql);

j($video_res);

}

用TP查询器也可以写在field里面当做一个字段查询，示例如下


$sql = Db::view('video')
     ->view('picture',['picture_id' => 'pid','picture_url','picture_path'],'picture.picture_id = video.picture_id')
     ->view('vod',['vod_id' => 'vid','actor_ids'],'vod.vod_id = video.vod_id')
     ->where('video.vod_id','in',$hot_id)
     ->field("(case when picture_type = 'remote' then picture_url when picture_type = 'local' then picture_path else 'asd' end) picaddr")
     ->order('video_subname desc')
     ->buildSql();

$sql = "select * from {$sql} as v group by v.vod_id order by v.video_subname desc";

$hot_res =  Db::query($sql);

j($hot_res);