mysql 两表联合查询 A表+B表=模拟C表无缝翻页

发表于： 2016年12月29日 2018年7月26日
分类：未分类

https://segmentfault.com/q/1010000007957291 首先得感谢一下这个回答的哥们给的思路

哥们的思路代码 很明确 还需要稍加改动
SELECT id,title,time,flag FROM (
select id,title,time,'zhuan' flag from zhuan 
UNION ALL
select id,title,time,'post' from post) a GROUP BY time DESC LIMIT 0,10;

select id,title,cdn,time FROM (
(select id,title,111 as cdn,time from set_gif where zhuanid = 0) 
UNION ALL
(select id,name as title,222 as cdn,time from set_zhuan) order by time desc) a LIMIT 0,100

如果是翻页的话只需要先统计到总量即可 select count(*) limit 1

下面贴我用THINKPHP 结合改造的翻页类做的两表联合模拟第三个表的查询 MODEL

public function Mquery2($pid,$state,$fenye = 5,$notzhuan,$fenyeurlpre = 0,$encryptpwd){
        if($pid){
            $sql1 = "select count(*) FROM (
(select id,title,111 as cdn,time from set_gif where zhuanid = 0 and catid = {$pid} and empty = 0) 
UNION ALL
(select id,name as title,222 as cdn,time from set_zhuan where empty = 0 and catid = {$pid} ) order by time desc) a LIMIT 1";
        }else{
            $sql1 = "select count(*) FROM (
(select id,title,111 as cdn,time from set_gif where zhuanid = 0 and catid !=9 and empty = 0) 
UNION ALL
(select id,name as title,222 as cdn,time from set_zhuan where empty = 0 and catid !=9) order by time desc) a LIMIT 1";
        }
        $sqlcount = $this->query($sql1);
        $count  = $sqlcount[0]['count(*)'];
        // P($count);
        // exit();
        //分页
		$Page       = new \Think\Page($count,$fenye);// 实例化分页类 传入总记录数和每页显示的记录数($fenye)
		$Page->setConfig('prev','上一页');
		$Page->setConfig('next','下一页');
        
       
        if($fenyeurlpre){
            $Page->baseUrl = $fenyeurlpre;
        }else{
            $Page->baseUrl = U('',array('catid'=>$pid)).'/p/';
        }
        
        $Page->houzhui = '';
		$show       = $Page->show();// 分页显示输出
        // P($show);
        // exit();
        //$list = $this->where(array('empty'=>1,'zhuanid'=> 0))->order('id desc')->limit($Page->firstRow.','.$Page->listRows)->select();
        if($pid){
            $sql2 = "select * FROM (
(select id,webid,catid,pathall,title,1 as type,cdn,time from set_gif where zhuanid = 0 and catid = {$pid} and empty = 0) 
UNION ALL
(select id,0 as webid,catid,'' as pathall,name as title,2 as type,0 as cdn,time from set_zhuan where empty = 0 and catid = {$pid}) order by time desc) a LIMIT {$Page->firstRow},{$Page->listRows}";
        }else{
            $sql2 = "select * FROM (
(select id,webid,catid,pathall,title,1 as type,cdn,time from set_gif where zhuanid = 0 and catid !=9 and empty = 0) 
UNION ALL
(select id,0 as webid,catid,'' as pathall,name as title,2 as type,0 as cdn,time from set_zhuan where empty = 0 and catid !=9) order by time desc) a LIMIT {$Page->firstRow},{$Page->listRows}";
        }



        $list = $this->query($sql2);
        $arr = array('list'=>$list,'page'=>$show);
        /*P($arr);
        exit();*/

        $lee = new \Think\Lee;
        $Mpostcate = new \Home\Model\PostcateModel;
        $Mtags = new \Home\Model\TagsModel;
        foreach($arr['list'] as $k=>$v){ //处理文章字段
            $arr['list'][$k]['time']=date('Y-m-d H:i:s',$v['time']); //转换时间
            $arr['list'][$k]['pinfo']=$Mpostcate->Mfind($v['catid']); //获取postcate信息
            $arr['list'][$k]['catenname'] = \Home\Controller\IndexController::getcaturl($v['catid']); //跨Home控制器调用英文名字
            
            $arr['list'][$k]['urlkey'] = $lee->alphaID($v['id'],false,6,$encryptpwd);//指定生成加密字母ID的长度为6.
            

            if($v['type'] == 1){ //1是GIF 2是专题
                $arr['list'][$k]['href'] = '/'.$arr['list'][$k]['catenname'].'/'.$arr['list'][$k]['urlkey'].'.html';
                $arr['list'][$k]['tagsarr'] = $Mtags->tagsallarr($v['id']);
                $arr['list'][$k]['pathall'] = $this->cdnlink($v['webid'],$v['cdn'],$v['pathall']); //获取cdn链接
               // P($arr['list'][$k]['pathall']);
            }else{
                $arr['list'][$k]['href'] = '/topic/'.$arr['list'][$k]['urlkey'].'.html'; //生成内页超链接
                $arr['list'][$k]['gifarr'] = $this->zhuanshow($v['id'],3); //专题下取三个图片
                $arr['list'][$k]['tagsarr'] = $Mtags->tagsallarr( $arr['list'][$k]['gifarr'][0]['id']); //取第一个图片的标签
            }

            


    	}

        //P($arr);
		return $arr;
    }

admin

1014