public List<Object[]> countForSix() throws DaoException{
String sql = "SELECT\r\n" +
" six_month.`month` 'month',\r\n" +
" IFNULL(COUNT(t.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 (SELECT * FROM USER u WHERE u.status='normal' AND u.type='orgn' ) t\r\n" +
" ON DATE_FORMAT(t.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);
}
}
Mysql查询最近6个月每月入驻的企业数量
最新推荐文章于 2024-07-29 04:01:42 发布