统计常用sql

1.gm_answer_question 表

2.统计表中的type=0对应的type=1的记录数 

SELECT 
  B.main_id,
  B.dt,
  COUNT(A.TYPE) 
FROM
  (SELECT 
    aq.main_id,
    t.subject_code,
    class.level_code,
    DATE_FORMAT(aq.create_time, '%Y-%m-%d') dt 
  FROM
    gm_answer_question aq,
    gm_teacher t,
    gm_class class,
    gm_school school 
  WHERE aq.TYPE = 0 
    AND aq.school_id = t.school_id 
    AND aq.receiver_id = t.user_id 
    AND aq.class_id = class.id 
    AND aq.delete_flag = 0 
    AND t.delete_flag = 0 
    AND class.delete_flag = 0 
    AND school.delete_flag = 0 
    AND school.id = aq.school_id 
    AND school.name LIKE CONCAT('%', '101', '%') 
    AND aq.create_time > STR_TO_DATE('2015-03-29', '%Y-%m-%d') 
    AND aq.create_time < STR_TO_DATE('2015-04-07', '%Y-%m-%d') 
  GROUP BY main_id) B 
  LEFT JOIN gm_answer_question A 
    ON B.main_id = A.main_id 
    AND A.TYPE = 1 
    AND A.delete_flag = 0 
GROUP BY B.main_id