sql高手进

Question

表一职工表:workerinfo    
gongid(工号)      bumenid(部门号)
1                  1
2                  2
3                  3表二考勤:  kqj
gongid    sbsj(上班迟到时间)   xnsk(下班早退时间)   rq(日期)
1               35                    20                2007.1.1
2                0                    0                 2007.1.1
3                25                   35                2007.1.11                39                     36              2007.1.2
2                0                      0               2007.1.2
3                35                     0              2007.1.2我想统计得到结果是:
gongid          cdc(迟到次数)        ztc(早退次数)
1                  2                     2
2                  0                      0
3                  2                      1
怎么写sql语句?

Answer 1

SELECT gongid AS gid,
       (SELECT COUNT(sbsj)
          FROM kqj
         WHERE gongid = gid
            && sbsj > 0
       ) AS cdc,
       (SELECT COUNT(xnsk)
          FROM kqj
         WHERE gongid = gid
            && xnsk > 0
       ) AS ztc
  FROM workerinfo;

Answer 2

+------+------+------+
| gid  | cdc  | ztc  |
+------+------+------+
|    1 |    2 |    2 |
|    2 |    0 |    0 |
|    3 |    2 |    1 |
+------+------+------+
3 rows in set (0.00 sec)