注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

前端开发那点事儿

冒犯之处,敬请谅解。

 
 
 

日志

 
 
 
 

MYSQL统计 -- COUNT  

2012-11-06 13:56:25|  分类: MySQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
select a2.groupnum,count(*) c7,count(l2.id) c8,count(l3.id) c9
            from vsactionlog l
        join vsacdagent a2 on l.operator = a2.extnum
        left join vsactionlog l2 on l.id = l2.id and l2.result = 1
    left join vsactionlog l3 on l.id = l3.id and l3.result = 0
            where l.actiontype = 28
group by groupnum

MYSQL统计 -- COUNT - Diligent - Diligent
 
MYSQL统计 -- COUNT - Diligent - Diligent



+++++++++++++

select t.groupnum
            ,ifnull(t1.c1,0) c1
            ,ifnull(t1.c2,0) c2
            ,ifnull(t1.c3,0) c3
            ,ifnull(t1.c4,0) c4
            ,ifnull(t1.c5,0) c5
            ,ifnull(t1.c6,0) c6
            ,ifnull(t2.c7,0) c7
            ,ifnull(t2.c8,0) c8
            ,ifnull(t2.c9,0) c9
            from vsacdgroup t
            left join (select ifnull(a.groupnum,g.groupnum) groupnum
                ,count(r1.id) c1
                ,count(r2.id) c2
                ,count(r3.id) c3
                ,count(r4.id) c4
                ,count(r5.id) c5
                ,count(r6.id) c6
                ,count(r7.id) c7
                ,count(r8.id) c8
                ,count(r9.id) c9
                from vscallrecord r
                left join vscallrecord r1 on r.id = r1.id
                    and r1.starttime between UNIX_TIMESTAMP(CURDATE()) and UNIX_TIMESTAMP(concat(CURDATE(),' 23:59:59'))
                    and r1.calltype = 0

                left join vscallrecord r2 on r.id = r2.id
                    and r2.starttime between UNIX_TIMESTAMP(CURDATE()) and UNIX_TIMESTAMP(concat(CURDATE(),' 23:59:59'))
                    and r2.calltype = 0 and r2.callstate = 1
                left join vscallrecord r3 on r.id = r3.id
                    and r3.starttime between UNIX_TIMESTAMP(CURDATE()) and UNIX_TIMESTAMP(concat(CURDATE(),' 23:59:59'))
                    and r3.calltype = 0 and r3.callstate = 0
                left join vscallrecord r4 on r.id = r4.id
                    and r4.starttime between UNIX_TIMESTAMP(CURDATE()) and UNIX_TIMESTAMP(concat(CURDATE(),' 23:59:59'))
                    and r4.calltype = 2
                left join vscallrecord r5 on r.id = r5.id
                    and r5.starttime between UNIX_TIMESTAMP(CURDATE()) and UNIX_TIMESTAMP(concat(CURDATE(),' 23:59:59'))
                    and r5.calltype = 2 and r5.callstate = 1
                left join vscallrecord r6 on r.id = r6.id
                    and r6.starttime between UNIX_TIMESTAMP(CURDATE()) and UNIX_TIMESTAMP(concat(CURDATE(),' 23:59:59'))
                    and r6.calltype = 2 and r6.callstate = 0
                left join vsacdagent a on r.callee = a.extnum
                left join vsacdgroup g on r.callee = g.groupnum
                where r.callee in (select extnum from vsacdagent) or r.callee in (select groupnum from vsacdgroup)
                group by groupnum) t1 on t.groupnum = t1.groupnum
            left join (select a2.groupnum,count(*) c7,count(l2.id) c8,count(l3.id) c9
                from vsactionlog l
                join vsacdagent a2 on l.operator = a2.extnum
                left join vsactionlog l2 on l.id = l2.id and l2.result = 1
                left join vsactionlog l3 on l.id = l3.id and l3.result = 0
                where l.actiontype = 28
                    and l.curtime between UNIX_TIMESTAMP(CURDATE()) and UNIX_TIMESTAMP(concat(CURDATE(),' 23:59:59'))
                group by groupnum) t2 on t.groupnum = t2.groupnum
            limit $start, $limit";

MYSQL统计 -- COUNT - Diligent - Diligent



++++

function listAll($start,$limit,$from=false,$to=false,$num=false)
    {
        $cond = ' between ' . ($from ? $from : -10000000000)  . ' and ' . ($to ? $to : 10000000000);
        $where = " where 1";
        if(!in_array($num,array('-1',false,-1))){
            $where = " where ag.extnum = '$num'";
        }
        $sql = "select ag.groupnum,ag.empno,ag.extnum
            ,ifnull(t1.c1,0) c1
            ,ifnull(t1.c2,0) c2
            ,ifnull(t1.c3,0) c3
            ,ifnull(t1.c7,0) c7
            ,ifnull(t2.c8,0) c8
            ,ifnull(t3.c4,0) c4
            ,ifnull(t3.c5,0) c5
            ,ifnull(t3.c6,0) c6
            ,ifnull(t4.c9,0) c9
            ,ifnull(t4.c10,0) c10
            ,ifnull(t4.c11,0) c11
            ,ifnull(t3.c12,0) c12
            ,ifnull(t3.c13,0) c13
            from vsacdagent ag
            left join (select a.target,count(a1.id) c1,count(a2.id) c2,count(a3.id) c3,count(a4.id) c7
                from vsactionlog a
                left join vsactionlog a1 on a.id = a1.id and a1.actiontype=14 and a1.result=1 and a1.curtime $cond
                left join vsactionlog a2 on a.id = a2.id and a2.actiontype=12 and a2.result=1 and a2.curtime $cond
                left join vsactionlog a3 on a.id = a3.id and a3.actiontype=15 and a3.result=1 and a3.curtime $cond
                left join vsactionlog a4 on a.id = a4.id and a4.actiontype=36 and a4.result=1 and a4.curtime $cond
                group by target) t1 on ag.extnum = t1.target

            left join (select operator,count(*) c8 from vsactionlog
                where actiontype=28 and result=1 and curtime $cond
                group by operator) t2 on ag.extnum = t2.operator
            left join (select r.callee,sum(r1.duration) c4,sum(r2.duration) c5,avg(r3.duration) c6
                ,count(r7.id) c12
                ,sum(r8.duration) c13
                from vscallrecord r
                left join vscallrecord r1 on r.id = r1.id and r1.calltype=0 and r1.callstate=1 and r1.starttime $cond
                left join vscallrecord r2 on r.id = r2.id and r2.callstate=1 and r2.starttime $cond
                left join vscallrecord r3 on r.id = r3.id and r3.calltype=0 and r3.callstate=1 and r3.starttime $cond
                left join vscallrecord r7 on r.id = r7.id and r7.calltype=1 and r7.callstate=1 and r7.starttime $cond
                left join vscallrecord r8 on r.id = r8.id and r8.calltype=1 and r8.callstate=1 and r8.starttime $cond
                group by callee) t3 on ag.extnum = t3.callee
            left join (select r.caller
                ,count(r4.id) c9
                ,sum(r5.duration) c10
                ,avg(r6.duration) c11
                from vscallrecord r
                left join vscallrecord r4 on r.id = r4.id and r4.calltype=2 and r4.callstate=1 and r4.starttime $cond
                left join vscallrecord r5 on r.id = r5.id and r5.calltype=2 and r5.callstate=1 and r5.starttime $cond
                left join vscallrecord r6 on r.id = r6.id and r6.calltype=2 and r6.callstate=1 and r6.starttime $cond
                group by caller) t4 on ag.extnum = t4.caller $where order by groupnum
            limit $start, $limit";
        return $this->db->row_query($sql);
    }

对一个表多次统计,可以自身与自身LEFT JOIN,因为普通的直接LEFT JOIN因为笛卡尔机制导致数据统计有重复
  评论这张
 
阅读(545)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017