www.weide1946.comjoin关联合检查询,表关联合检查询

2019-06-15 21:45 来源:未知

使用sql查询:
SELECT a.*,b.gs_schoolnamecn,b.gs_schoolnameen FROM tsh_greenaction a  LEFT JOIN tsh_greenschool b on a.greenschoolid=b.id 

sql:

(1)

 

select cityid,cityname from tsh_city where cityid in(

$model = M();

  使用thinkphp连贯操作:

select gs_cityid from tsh_greenschool group by gs_cityid 

        $info = $model->table('vote_info')->alias('v')

     $model=M('greenaction');

);

            ->field('v.*, u.real_name, g.group_name')

     $list=$model->alias('a')->join(' LEFT JOIN tsh_greenschool b on a.greenschoolid=b.id')

 

            ->join('user_info as u on v.user_id = u.id')

     ->field('a.*,b.gs_schoolnamecn,b.gs_schoolnameen')->select();

 

            ->join('group_info as g on g.id = u.group_id')

     $temp='';

thinkphp:      

            ->where($where)

  for($i=0;$i<count($list);$i ){

 

            ->order('v.create_time desc')

  $temp=$temp.$list[$i]['gs_schoolnamecn'].',';

$subquery=M('greenschool')->field('gs_cityid')->group('gs_cityid')->select(false);

            ->select();

  }

     $citylist=M('city')->field('cityid,cityname')->where('cityid in '.$subquery)->select();

       dump($temp); 

     $temp='';

$model = M();


     foreach ($citylist as $value) {

  $list = $model->table("message m")

     $temp=$temp.$value['cityname']www.weide1946.com ,;

   ->field("m.time,m.content,u.nickname as name,u.head_img as img")

     }

   ->join("left join user_info as u on m.user_id=u.id")

     dump($temp);

   ->where($map)->select();


 

$model = M(); 

$list = $model->table("message m left join user_info u on m.user_id=u.id")

   ->field("m.time,m.content,u.nickname as name,u.head_img as img")

   ->where($map)->select();

 

(2)

$sql = "SELECT u.real_name, n.id, n.note_title, n.note_tags, n.create_time, t.type_name FROM notes_info AS n INNER JOIN user_info AS u ON n.user_id=u.id INNER JOIN note_type AS t ON n.sort_id=t.id WHERE n.status=0 AND n.user_id=".$where['user_id']." ORDER BY n.id DESC";

 

$res = $personal_list->query($sql);

 

 

select a.id,a.name,a.keyword,a.descripttion,b.l_id,b.listcoment,b.text from dome as a, list as b where a.id=b.id and a.id=1 order by a.sord ASC,order by id DESC;

我们也足以改写成LEFT JOIN ..ON语句

select a.id,a.name,a.keyword,a.description,b.l_id,b.listcoment,b.text from dome as a LEFT JOIN list as b ON a.id=b.id where a.id=1 order by a.sord ASC,order by id DESC;

一体化来讲第二条语句是比较实用,好处在查询数据表dome表的数码,不论多少表list中是不是有dome中的数据那么都会询问的出来dome中的值

首先条查询语句与第二条语句不通之处是询问dome数据表与list数据表中的id为1时,借使dome中有数量而list中并未有数量那么查询出来的结果为空,

也得以多涉及查询语句与双涉及查询方式类似

from dome as a LEFT JOIN list as b ON a.id=b.id,LEFT JOIN TABLE AS c ON a.id=c.id,LEFT JOIN TABLE as d ON a.id=d.id

 

group by语句

当语句中冒出count只怕max 也许别的的有的函数的时候就会用到group by语句

select a.*,count(b.id) from dome as a left join list as b ON a.id=b.id where a.id=1 GROUP BY a.id;

 

 

版权声明:本文由韦德娱乐1946_韦德娱乐1946网页版|韦德国际1946官网发布于网络编程,转载请注明出处:www.weide1946.comjoin关联合检查询,表关联合检查询