mysql查询每个月入职人数_Mysql查询最近6个月每月入驻的企业数量

public List countForSix() throws DaoException{

String sql = "SELECT\r\n" +

"    six_month.`month` 'month',\r\n" +

"    IFNULL(COUNT(u.userId), 0) COUNT\r\n" +

"FROM\r\n" +

"    (\r\n" +

"        SELECT DATE_FORMAT(NOW(), '%Y-%m') MONTH FROM DUAL\r\n" +

"        UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m') MONTH FROM DUAL\r\n" +

"        UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 MONTH), '%Y-%m') MONTH FROM DUAL\r\n" +

"        UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH), '%Y-%m') MONTH FROM DUAL\r\n" +

"        UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 4 MONTH), '%Y-%m') MONTH FROM DUAL\r\n" +

"        UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 5 MONTH), '%Y-%m') MONTH FROM DUAL\r\n" +

"    ) six_month LEFT JOIN USER u\r\n" +

"    ON  DATE_FORMAT(u.createDate, '%Y-%m') = six_month.`month`\r\n" +

"    GROUP BY  six_month.`month`\r\n" +

"    ORDER BY six_month.`month` ASC";

try {

Session session = super.getSessionFactory().getCurrentSession();

Query query = session.createSQLQuery(sql);

return query.list();

} catch (Exception e) {

// TODO Auto-generated catch block

log.error("统计园区近半年每月入驻数量出错了！", e);

throw new DaoException("统计园区近半年每月入驻数量出错了！", e);

}

}