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_sumFROM(SELECT  usr.user_id,usr.real_name,workday.date FROM(SELECT tbl_calendar.* FROM tbl_calendarWHERE  DATE >='2013-07-01' AND DATE<='2013-07-06'AND isorwork=0 ) workday,(SELECT tbl_user.* FROM tbl_user,tbl_group_memberWHERE tbl_user.user_id = tbl_group_member.user_idAND tbl_group_member.group_id = 1) usr) usr_calLEFT JOIN(SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance ,tbl_calendarWHERE TYPE = 1AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06'AND tbl_attendance.checkdate = tbl_calendar.dateAND tbl_calendar.isorwork=0) checkintblON usr_cal.user_id = checkintbl.user_id AND usr_cal.date =checkintbl.checkdateLEFT JOIN(SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance,tbl_calendarWHERE TYPE = 2AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06'AND tbl_attendance.checkdate = tbl_calendar.dateAND tbl_calendar.isorwork=0) checkouttblON 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.addressFROM(SELECT  usr.user_id,usr.real_name,workday.date FROM(SELECT tbl_calendar.* FROM tbl_calendarWHERE  DATE="."'".$date."'"."AND isorwork=0 ) workday,(SELECT tbl_user.* FROM tbl_user,tbl_group_memberWHERE tbl_user.user_id = tbl_group_member.user_idAND tbl_group_member.group_id = $group_id AND tbl_user.type!=-11 and tbl_group_member.user_type = 4) usr) usr_calLEFT 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_geoWHERE tbl_attendance.geo_id = tbl_geo.geo_id AND tbl_attendance.group_id = $group_id) attend ,tbl_calendarWHERE TYPE = $typeAND  checkdate ="."'".$date."'"."AND attend.checkdate = tbl_calendar.dateAND tbl_calendar.isorwork=0) checktblON usr_cal.user_id = checktbl.user_id AND usr_cal.date =checktbl.checkdate where $where_searchGROUP 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换着试试，
怎么就这么不灵活呢，自己在一遍一遍的告诉自己