ThinkPHP之诡异sql查询

    很久没用TP了，觉得TP 用久了，都不知道PHP为何物。来到了公司又用起了TP2.1，行，没问题，哥之前用过3.1.
    问题来了，做的是日考勤和月考勤统计，我汗，TP的关联查询，统计查询，是搞不定了，能搞的话，效率也是不过关，大哥说的，我还体会不到。
    参考大哥的SQL改吧：

ALTER TABLE `tbl_attendance` ADD COLUMN `check_unnormal` INT NULL default 1 AFTER `geo_normal`; 
ALTER TABLE `tbl_attendance` DROP COLUMN `real_name`; 
  
SELECT usr_cal.user_id,usr_cal.real_name, 
IFNULL(SUM(checkintbl.nolate),0) AS checkin_late_sum, 
IFNULL (SUM(checkintbl.geo_normal),0) AS checkin_geo_unnormal_sum, 
IFNULL (SUM(checkouttbl.nolate),0) AS checkout_late_sum,  
IFNULL (SUM(checkouttbl.geo_normal),0) AS checkout_geo_unnormal_sum, 
IFNULL (SUM(checkintbl.check_unnormal),0) AS checkin_check_sum, 
IFNULL (SUM(checkouttbl.check_unnormal),0) AS checkout_check_sum, 
IFNULL (SUM(COALESCE(checkintbl.nocheck,1)),0) AS checkin_nocheck_sum, 
IFNULL (SUM(COALESCE(checkouttbl.nocheck,1)),0) AS checkout_nocheck_sum 
FROM 
( 
SELECT  usr.user_id,usr.real_name,workday.date FROM  
(SELECT tbl_calendar.* FROM tbl_calendar  
WHERE  DATE >='2013-07-01' AND DATE<='2013-07-06'  
AND isorwork=0 ) workday, 
(SELECT tbl_user.* FROM tbl_user,tbl_group_member 
WHERE tbl_user.user_id = tbl_group_member.user_id 
AND tbl_group_member.group_id = 1 
) usr 
 
 
) usr_cal 
LEFT JOIN  
(SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance ,tbl_calendar 
WHERE TYPE = 1  
AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06' 
AND tbl_attendance.checkdate = tbl_calendar.date 
AND tbl_calendar.isorwork=0 
) checkintbl 
ON usr_cal.user_id = checkintbl.user_id AND usr_cal.date =checkintbl.checkdate 
LEFT JOIN  
(SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance,tbl_calendar 
WHERE TYPE = 2 
AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06' 
AND tbl_attendance.checkdate = tbl_calendar.date 
AND tbl_calendar.isorwork=0 
) checkouttbl 
ON usr_cal.user_id = checkouttbl.user_id AND usr_cal.date =checkouttbl.checkdate 
 
 
GROUP BY usr_cal.user_id 

ALTER TABLE `tbl_attendance` ADD COLUMN `check_unnormal` INT NULL default 1 AFTER `geo_normal`;
ALTER TABLE `tbl_attendance` DROP COLUMN `real_name`;
 
SELECT usr_cal.user_id,usr_cal.real_name,
IFNULL(SUM(checkintbl.nolate),0) AS checkin_late_sum,
IFNULL (SUM(checkintbl.geo_normal),0) AS checkin_geo_unnormal_sum,
IFNULL (SUM(checkouttbl.nolate),0) AS checkout_late_sum,
IFNULL (SUM(checkouttbl.geo_normal),0) AS checkout_geo_unnormal_sum,
IFNULL (SUM(checkintbl.check_unnormal),0) AS checkin_check_sum,
IFNULL (SUM(checkouttbl.check_unnormal),0) AS checkout_check_sum,
IFNULL (SUM(COALESCE(checkintbl.nocheck,1)),0) AS checkin_nocheck_sum,
IFNULL (SUM(COALESCE(checkouttbl.nocheck,1)),0) AS checkout_nocheck_sum
FROM
(
SELECT  usr.user_id,usr.real_name,workday.date FROM
(SELECT tbl_calendar.* FROM tbl_calendar
WHERE  DATE >='2013-07-01' AND DATE<='2013-07-06'
AND isorwork=0 ) workday,
(SELECT tbl_user.* FROM tbl_user,tbl_group_member
WHERE tbl_user.user_id = tbl_group_member.user_id
AND tbl_group_member.group_id = 1
) usr


) usr_cal
LEFT JOIN
(SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance ,tbl_calendar
WHERE TYPE = 1
AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06'
AND tbl_attendance.checkdate = tbl_calendar.date
AND tbl_calendar.isorwork=0
) checkintbl
ON usr_cal.user_id = checkintbl.user_id AND usr_cal.date =checkintbl.checkdate
LEFT JOIN
(SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance,tbl_calendar
WHERE TYPE = 2
AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06'
AND tbl_attendance.checkdate = tbl_calendar.date
AND tbl_calendar.isorwork=0
) checkouttbl
ON usr_cal.user_id = checkouttbl.user_id AND usr_cal.date =checkouttbl.checkdate

GROUP BY usr_cal.user_id我去，看的我晕，晕……各种SQL知识回顾，各种查询，总算搞明白了，于是改照出了自己的日考勤SQL方案，小有成就感，在数据库上一测，可以，

$sql = "SELECT usr_cal.user_id,usr_cal.real_name,COALESCE(checktbl.nocheck,1)  AS nocheck,COALESCE(checktbl.nolate,0)  AS late, 
COALESCE(checktbl.geo_normal,0)  AS geo_normal,checktbl.checktime,checktbl.address 
FROM( 
SELECT  usr.user_id,usr.real_name,workday.date FROM  
(SELECT tbl_calendar.* FROM tbl_calendar  
WHERE  DATE="."'".$date."'"."  
AND isorwork=0 ) workday, 
(SELECT tbl_user.* FROM tbl_user,tbl_group_member 
WHERE tbl_user.user_id = tbl_group_member.user_id 
AND tbl_group_member.group_id = $group_id AND tbl_user.type!=-11 and tbl_group_member.user_type = 4  
) usr 
) usr_cal 
LEFT JOIN  
(SELECT user_id,checkdate,nolate,checktime,geo_normal,0 AS nocheck,check_unnormal,address FROM (SELECT tbl_attendance.*,tbl_geo.address FROM tbl_attendance,tbl_geo 
WHERE tbl_attendance.geo_id = tbl_geo.geo_id AND tbl_attendance.group_id = $group_id 
) attend ,tbl_calendar 
WHERE TYPE = $type  
AND  checkdate ="."'".$date."'"." 
AND attend.checkdate = tbl_calendar.date 
AND tbl_calendar.isorwork=0 
) checktbl 
ON usr_cal.user_id = checktbl.user_id AND usr_cal.date =checktbl.checkdate where $where_search 
GROUP BY usr_cal.user_id ORDER  BY nocheck desc,late asc,geo_normal asc 
"； 

$sql = "SELECT usr_cal.user_id,usr_cal.real_name,COALESCE(checktbl.nocheck,1)  AS nocheck,COALESCE(checktbl.nolate,0)  AS late,
COALESCE(checktbl.geo_normal,0)  AS geo_normal,checktbl.checktime,checktbl.address
FROM(
SELECT  usr.user_id,usr.real_name,workday.date FROM
(SELECT tbl_calendar.* FROM tbl_calendar
WHERE  DATE="."'".$date."'"."
AND isorwork=0 ) workday,
(SELECT tbl_user.* FROM tbl_user,tbl_group_member
WHERE tbl_user.user_id = tbl_group_member.user_id
AND tbl_group_member.group_id = $group_id AND tbl_user.type!=-11 and tbl_group_member.user_type = 4
) usr
) usr_cal
LEFT JOIN
(SELECT user_id,checkdate,nolate,checktime,geo_normal,0 AS nocheck,check_unnormal,address FROM (SELECT tbl_attendance.*,tbl_geo.address FROM tbl_attendance,tbl_geo
WHERE tbl_attendance.geo_id = tbl_geo.geo_id AND tbl_attendance.group_id = $group_id
) attend ,tbl_calendar
WHERE TYPE = $type
AND  checkdate ="."'".$date."'"."
AND attend.checkdate = tbl_calendar.date
AND tbl_calendar.isorwork=0
) checktbl
ON usr_cal.user_id = checktbl.user_id AND usr_cal.date =checktbl.checkdate where $where_search
GROUP BY usr_cal.user_id ORDER  BY nocheck desc,late asc,geo_normal asc
"；

好了，交给TP了：。

不管是2.1还是3.1都是这么说的，我怎么尝试怎么不行，怎么查怎么不行，$Model = M();网上的解决方案，也不行，问梁哥，说随便实例化一个对象就行了，各种尝试

 $tag = M('Tag'); 
$tag  = new Model('Tag'); 
$tag->query('select * from tbl_tag');这么简单的都不行，我彻底无语，崩溃了， 

$tag = M('Tag');
$tag  = new Model('Tag');
$tag->query('select * from tbl_tag');这么简单的都不行，我彻底无语，崩溃了，

 

哥过来了说都跟你说了随便实例化一个对象，一边打开GroupAction,一边说都不知道怎么说你
粘贴了$group = D('Group');$group->query($sql); 结果出来了，就这么出来了，怎么就这么不灵活呢，就不知道M和D换着试试，
怎么就这么不灵活呢，自己在一遍一遍的告诉自己

补充：Web开发 , php ,

上一个：php与文件操作
下一个：php图片自动裁剪工具，解决图片变形问题，缩略图问题