php 子查询

第一：子查询的定义

概念：　子查询允许把一个查询嵌套在另一个查询当中。

第二：子查询的分类

A.标量子查询：

　　　是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。可以使用 = > < >= <= <> 这些操作符对子

查询的标量结果进行比较，通常子查询的位置在比较式的右侧

　　　　示例：

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)

SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)

SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

B.列子查询

指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。

　　　　可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧

　　　　可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。

　　　　示例：

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)

SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)

SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)

　　　　NOT IN 是 <> ALL 的别名，二者相同。

　　　　特殊情况

　　　　　　如果 table2 为空表，则 ALL 后的结果为 TRUE；

　　　　　　如果子查询返回如 (0,NULL,1) 这种尽管 s1 比返回结果都大，但有空行的结果，则 ALL 后的结果为 UNKNOWN 。

　　　　注意：对于 table2 空表的情况，下面的语句均返回 NULL：

SELECT s1 FROM table1 WHERE s1 > (SELECT s2 FROM table2)

SELECT s1 FROM table1 WHERE s1 > ALL (SELECT MAX(s1) FROM table2)

C.行子查询

指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。

　　　　例子：

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)

SELECT * FROM product WHERE(BigClass_ID,SmallClass_ID)=(SELECT BigClass_ID,SmallClass_ID from smallclass WHERE SmallClass_ID=356);

注：(1,2) 等同于 row(1,2)

SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

D.表子查询

指子查询返回的结果集是 N 行 N 列的一个表数据。

　　　　例子：

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)

SELECT * FROM product WHERE (BigClass_ID,SmallClass_ID) IN(SELECT BigClass_ID,SmallClass_ID from smallclass);

第三：子查询的总结

子查询对数据的查找带来很多方便，从上面的分类种类一层一层往下了解。

thinkphp3子查询

$subQuery1 = D('device_factory df')->join('inner join device d on d.factory=df.factory')
        ->field("df.name as '机型品牌',d.factory,model,type,case when d.name='' then d.orgin else d.name end as name")
        ->select(false);

select df.name as '机型品牌',d.factory,model,type,case when d.name='' then d.orgin else d.name end as name from device_factory df inner join device d on d.factory=df.factory

第二个子查询

$subQuery1 = D('device_factory df')->join('inner join device d on d.factory=df.factory')
        ->field("df.name as '机型品牌',d.factory,model,type,case when d.name='' then d.orgin else d.name end as name")
        ->select(false);
        
$c = D()->table("record r,($subQuery1)b")
            ->field('count(*) as times,b.*')->where(
            'r.device&0xffff=b.model and r.device>>16&0xffff=b.factory'
                    )->group('device&0xffff')->select(false);

select count(*) as times, b.* from record r ,(select df.name as '机型品牌',d.factory,model,type,case when d.name='' then d.orgin else d.name end as name from device_factory df inner join device d on 
d.factory=df.factory)b where r.device&0xffff=b.model and r.device>>16&0xffff = b.factory group by device&0xffff

总查询

$subQuery1 = D('device_factory df')->join('inner join device d on d.factory=df.factory')
        ->field("df.name as '机型品牌',d.factory,model,type,case when d.name='' then d.orgin else d.name end as name")
        ->select(false);
        
$c = D()->table("record r,($subQuery1)b")
    ->field('count(*) as times,b.*')->where(
    'r.device&0xffff=b.model and r.device>>16&0xffff=b.factory'
    )->group('device&0xffff')->select(false);
        
$d = D()->table("(select @rank:=0)rank,($c)c")
    ->field('@rank:=@rank+1 as rank,c.*')
    ->order('c.times desc')->select();

SELECT @rank:=@rank+1 as rank,c.* FROM
(select @rank:=0)rank,(
SELECT count(*) as times,
b.* FROM record r,
(SELECT df.name as '机型品牌',d.factory,`model`,`type`,case when d.name='' then d.orgin 
else d.name end as name FROM device_factory df inner join device d on d.factory=df.factory  )b 
WHERE ( r.device&0xffff=b.model and r.device>>16&0xffff=b.factory ) GROUP BY device&0xffff )c ORDER BY c.times desc

首先构造子查询SQL，可以使用下面三种的方式来构建子查询。

thinkphp5子查询

使用`fetchSql`方法

fetchSql方法表示不进行查询而只是返回构建的SQL语句，并且不仅仅支持select，而是支持所有的CURD查询。

$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id', '>', 10)
    ->fetchSql(true)
    ->select();

生成的subQuery结果为：

SELECT `id`,`name` FROM `think_user` WHERE `id` > 10

使用`buildSql`构造子查询

$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id', '>', 10)
    ->buildSql();

生成的subQuery结果为：

( SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 )

调用buildSql方法后不会进行实际的查询操作，而只是生成该次查询的SQL语句（为了避免混淆，会在SQL两边加上括号），然后我们直接在后续的查询中直接调用。

然后使用子查询构造新的查询：

Db::table($subQuery . ' a')
    ->where('a.name', 'like', 'thinkphp')
    ->order('id', 'desc')
    ->select();

生成的SQL语句为：

SELECT * FROM ( SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 ) a WHERE a.name LIKE 'thinkphp' ORDER BY `id` desc

使用闭包构造子查询

IN/NOT IN和EXISTS/NOT EXISTS之类的查询可以直接使用闭包作为子查询，例如：

Db::table('think_user')
    ->where('id', 'IN', function ($query) {
        $query->table('think_profile')->where('status', 1)->field('id');
    })
    ->select();

生成的SQL语句是

SELECT * FROM `think_user` WHERE `id` IN ( SELECT `id` FROM `think_profile` WHERE `status` = 1 )

Db::table('think_user')
    ->whereExists(function ($query) {
        $query->table('think_profile')->where('status', 1);
    })->find();

生成的SQL语句为

SELECT * FROM `think_user` WHERE EXISTS ( SELECT * FROM `think_profile` WHERE `status` = 1 )

除了上述查询条件外，比较运算也支持使用闭包子查询

posted @ 2018-11-19 20:03 isuzu2019 阅读(805) 评论(0) 收藏举报

刷新页面返回顶部

isuzu2019

php 子查询

使用fetchSql方法

使用buildSql构造子查询

使用闭包构造子查询

公告

使用`fetchSql`方法

使用`buildSql`构造子查询