ThinkPHP查询结果内的查询

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

select ...( select ...)...形式的SQL封装

我想要一个这样的SQL分页查询：

SELECT
	TEMPTABLE .aid,
	TEMPTABLE .aliid,
	TEMPTABLE .etime,
	TEMPTABLE .end_time,
	TEMPTABLE .endtime
FROM
	`sd_o_agreement` `ag`
LEFT JOIN (
	SELECT
		sa.id AS aid,
		`sa`.`starttime`,
		`sa`.`endtime`,
		sali.id AS aliid,
		sal.id AS alid,
		`sal`.`start_time`,
		`sal`.`end_time`,
		CASE
	WHEN isnull(sal.end_time) THEN
		sa.endtime
	ELSE
		sal.end_time
	END etime
	FROM
		`sd_o_agreement` `sa`
	LEFT JOIN `sd_o_agreement_list_info` `sali` ON `sali`.`agreement_id` = `sa`.`id`
	LEFT JOIN `sd_o_agreement_list` `sal` ON `sali`.`agreement_list_id` = `sal`.`id`
	GROUP BY
		`sa`.`id`
	ORDER BY
		etime DESC
) TEMPTABLE ON `tempTable`.`aid` = `ag`.`id`
WHERE
	(etime < '1618796943')
LIMIT 0,
 10

首先先做内部sql的查询封装：

内部查询的字段：

  $field = "	sa.id as aid,
        `sa`.`starttime`,
        `sa`.`endtime`,
        sali.id AS aliid,
        sal.id AS alid,
        `sal`.`start_time`,
        `sal`.`end_time`,
           CASE
            WHEN isnull(sal.end_time) THEN
                sa.endtime
            ELSE
                sal.end_time
            END
            etime";

内部sql的联查：

        $join = [
            [ 'sd_o_agreement_list_info sali', 'sali.agreement_id = sa.id','LEFT'],
            [ 'sd_o_agreement_list sal', 'sali.agreement_list_id = sal.id','LEFT'],
        ];

然后我们把内部查询的sql生成出来：

        $tempTableSql = $this->alias("sa")
            ->field($field)
            ->join($join)
            ->group("sa.id")
            ->order("etime desc")
            ->select(false);

然后开始封装我们最后的sql: 封装查询字段：

 $newField = ["tempTable.aid,tempTable.aliid,tempTable.etime,tempTable.end_time,tempTable.endtime"];

关键性操作在这里了，【敲黑板】 联查封装：

    $newJoin = [
        [ '('.$tempTableSql.' ) tempTable', '  tempTable.aid = ag.id','LEFT']
    ];

说明：如正常我们写联查对应关系一样。注意点是：之前写表名字的位置，放置上我们开始生成的临时sql，并且它的前后需要被‘（’、‘）’包起来，后面给他起别名，其他按照正常对应关系写即可！

后封装我们的查询语句：

        $list = $this->alias("ag")
            ->field($newField)
            ->where($where)
            ->join($newJoin)
            ->paginate(10);
        echo $this->getLastSql()."<br>";exit;

拼接一个mybatis:

<resultMap type="io.renren.modules.generator.entity.TEsintSjxxBpcclxxbEntity" id="tEsintSjxxBpcclxxbMap">
        <result property="bqmc" column="BQMC"/>
        <result property="bqxxsj" column="BQXXSJ"/>
        <result property="bz" column="BZ"/>
        <result property="clpp" column="CLPP"/>
        <result property="clsbdh" column="CLSBDH"/>
        <result property="csys" column="CSYS"/>
        <result property="hphm" column="HPHM"/>
        <result property="hpzl" column="HPZL"/>
        <result property="id" column="ID"/>
        <result property="industrialid" column="INDUSTRIALID"/>
        <result property="jdcsyr" column="JDCSYR"/>
        <result property="jdcsyrsfzh" column="JDCSYRSFZH"/>
        <result property="kkhchrlx" column="KKHCHRLX"/>
        <result property="kkid" column="KKID"/>
        <result property="kklx" column="KKLX"/>
        <result property="kkmc" column="KKMC"/>
        <result property="kksfsz" column="KKSFSZ"/>
        <result property="kkssdszdm" column="KKSSDSZDM"/>
        <result property="kkssdszmc" column="KKSSDSZMC"/>
        <result property="kkssdwdm" column="KKSSDWDM"/>
        <result property="kkssdwmc" column="KKSSDWMC"/>
        <result property="kkssfsxdm" column="KKSSFSXDM"/>
        <result property="kkssfsxmc" column="KKSSFSXMC"/>
        <result property="kkszmc" column="KKSZMC"/>
        <result property="kkxzqh" column="KKXZQH"/>
        <result property="pccljg" column="PCCLJG"/>
        <result property="pccljgmc" column="PCCLJGMC"/>
        <result property="pcclyjdw" column="PCCLYJDW"/>
        <result property="pcclyjyy" column="PCCLYJYY"/>
        <result property="pcdjd" column="PCDJD"/>
        <result property="pcdwd" column="PCDWD"/>
        <result property="pcsj" column="PCSJ"/>
        <result property="pcsrlx" column="PCSRLX"/>
        <result property="sjly" column="SJLY"/>
        <result property="ssdszdm" column="SSDSZDM"/>
        <result property="ssdszmc" column="SSDSZMC"/>
        <result property="ssdwdm" column="SSDWDM"/>
        <result property="ssdwdycs" column="SSDWDYCS"/>
        <result property="ssdwmc" column="SSDWMC"/>
        <result property="ssfsxdm" column="SSFSXDM"/>
        <result property="ssfsxmc" column="SSFSXMC"/>
        <result property="userid" column="USERID"/>
        <result property="username" column="USERNAME"/>
        <result property="xtCjsj" column="XT_CJSJ"/>
        <result property="xtLrip" column="XT_LRIP"/>
        <result property="xtLrrbm" column="XT_LRRBM"/>
        <result property="xtLrrbmid" column="XT_LRRBMID"/>
        <result property="xtLrrid" column="XT_LRRID"/>
        <result property="xtLrrxm" column="XT_LRRXM"/>
        <result property="xtLrsj" column="XT_LRSJ"/>
        <result property="xtZhxgip" column="XT_ZHXGIP"/>
        <result property="xtZhxgrbm" column="XT_ZHXGRBM"/>
        <result property="xtZhxgrbmid" column="XT_ZHXGRBMID"/>
        <result property="xtZhxgrid" column="XT_ZHXGRID"/>
        <result property="xtZhxgrxm" column="XT_ZHXGRXM"/>
        <result property="xtZhxgsj" column="XT_ZHXGSJ"/>
        <result property="xtZxbz" column="XT_ZXBZ"/>
        <result property="xtZxyy" column="XT_ZXYY"/>
        <result property="ywid" column="YWID"/>
        <result property="ywkzzd1" column="YWKZZD1"/>
        <result property="ywkzzd2" column="YWKZZD2"/>
        <result property="ywkzzd3" column="YWKZZD3"/>
        <result property="ywkzzd4" column="YWKZZD4"/>
        <result property="ywkzzd5" column="YWKZZD5"/>
        <result property="ywlx" column="YWLX"/>
        <result property="zdch" column="ZDCH"/>
        <result property="zdsim" column="ZDSIM"/>
        <result property="zdxh" column="ZDXH"/>
        <result property="rksjZyk" column="RKSJ_ZYK"/>
        <result property="source" column="SOURCE"/>
        <result property="esintInsertTime" column="ESINT_INSERT_TIME"/>
        <result property="sourceInfo" column="SOURCE_INFO"/>
    </resultMap>