主页 > 编程 > php >

Mysql not in语句配合三表联合查询代码例子

2019-01-29 13:13 阅读:107 来源:智宇SEO自媒体

  Mysql not in语句配合三表联合查询

  功能:小区物业管理系统缴费明细查询
Mysql not in语句配合三表联合查询代码例子

  通过查找缴费明细表,缴费到期时间小于今天日期的则为未缴费,否为已缴费

  通过业主表 id not ni 已缴费id,得出所有未缴费的物业信息

  业主表,缴费类型,缴费明细表结构见。

  SQL代码:

SELECT
	*
FROM
	yuwy_user a
WHERE
	id NOT IN (
		"." SELECT
			a.id
		FROM
			(
				yuwy_user a
				LEFT JOIN yuwy_pay b ON a.id = b.userid
			)
		LEFT JOIN yuwy_type c ON b.typeid = c.id
		WHERE
			b.typeid = "." '$type' "."
		AND b.enddate > "." '$today' "."
	) "

  控制器层代码:

if(!empty($_POST['btn2']))
            {
              
                $today=date("Y-m-d H:i:s");
                $type=$_POST['paytype2'];
                $nopay=$_POST['nopay'];
                if($nopay==1)
                {
                    //已缴费查询
                    $sql=" SELECT a.id,a.name as uname,a.numberplate, c.name as pyname, b.cycle,b.enddate  FROM (yuwy_user a LEFT JOIN yuwy_pay b ON a.id=b.userid )LEFT JOIN yuwy_type c ON b.typeid=c.id  WHERE b.typeid="."'$type'"."and b.enddate>"."'$today'";
                    $this->db->query($sql);
                    $this->res1=$this->db->queryFetchAll();
               
                }else
                {
                    //欠费查询
                    $sql="select * from yuwy_user a WHERE id not in("." SELECT a.id FROM (yuwy_user a LEFT JOIN yuwy_pay b ON a.id=b.userid )LEFT JOIN yuwy_type c ON b.typeid=c.id  WHERE b.typeid="."'$type'"."and b.enddate>"."'$today'".")";
                    $this->db->query($sql);
                    $this->res2=$this->db->queryFetchAll();
                  
                }