thinkphp多表查询语句

最新推荐文章于 2024-05-23 10:32:12 发布

原创最新推荐文章于 2024-05-23 10:32:12 发布 · 1.6k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#thinkphp

ThinkPHP 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了SQL查询中常用函数如join()、table()的使用方法，包括如何执行原生查询以及数据库操作技巧，旨在帮助开发者提升数据库查询效率。

1、join()函数
$userLog= M(‘userLog’);

$rows=$ userLog->table(‘user_log_201604’)->field(“tb.id,tb.user_name,tb.user_id,tb.msg,tb.created,a.group_id”)->where( $where)->join('left JOIN group_user as a on tb.user_id=a.user_id')->limit($ p->firstRow.”,”.$p->listRows)->order(“tb.id desc”)->select();//join第一个参数控制左右查询

echo $userLog->_sql();//打印sql语句

2、table()函数
$userLog= M(‘userLog’);

$list=$ userLog->table(‘user_log_201604 as tb,group_user as tc’)->where(“tb.user_id=tc.user_id and tb.module = ‘Admin’ “)->field(‘tb.id,tb.user_name,tb.user_id,tb.msg,tb.created,tc.group_id’)->order(‘tb.id desc’ )->limit(5)->select();
echo $userLog->_sql();//打印sql语句

3、原生查询
$sql=”SELECT tb.id,tb.user_name,tb.user_id,tb.msg,tb.created,tc.group_id FROM user_log_201604 left JOIN group_user as tc on tb.user_id=tc.user_id WHERE ( tb.module = ‘Admin’ and tb.created between ‘2016-04-19 00:00:00’ and ‘2016-04-19 23:59:59’ ) ORDER BY tb.id desc LIMIT 0,20 “

例子：
以一个 user 表和 order表联查为例,，
第一种方式：
$data = M(‘user as a’)->join(‘orderas b on b.id = a.id’)->where(‘a.id = 1’)->select();

第二种：
$data = M()->table(‘user as a’)->join(‘order as b on b.id = a.id’)->where(’ a.id = 1 ‘)->select();

如果有多个表，继续在table 后面加 join 就行了