| | |
|---|
| | | COUNT(user_id)allUser, |
|---|
| | | COUNT(case WHEN DATE_FORMAT(t.create_at,'%m-%d-%Y')=DATE_FORMAT(SYSDATE(),'%m-%d-%Y') then user_id else null end )addUser, |
|---|
| | | COUNT(case WHEN DATE_FORMAT(last_login_time,'%m-%d-%Y')=DATE_FORMAT(SYSDATE(),'%m-%d-%Y') then user_id else null end )activeDayUser, |
|---|
| | | (SELECT count(user_id) FROM sys_user t left join com_act t1 on t.community_id = t1.community_id WHERE type = 1 AND last_login_time > date_sub(SYSDATE(), INTERVAL 7 DAY))activeWeekUser |
|---|
| | | (SELECT count(user_id) FROM sys_user t left join com_act t1 on t.community_id = t1.community_id WHERE t.type = 1 AND last_login_time > date_sub(SYSDATE(), INTERVAL 7 DAY))activeWeekUser |
|---|
| | | FROM |
|---|
| | | sys_user t LEFT JOIN com_act t1 on t.community_id = t1.community_id |
|---|
| | | where t.type=1 and t.community_id is not null |
|---|
| | | </select> |
|---|
| | | |
|---|
| | | <select id="selectCommunityUserOrder" resultType="com.panzhihua.service_user.model.dtos.DataKanbanDTO"> |
|---|
| | | select t.* from ( |
|---|
| | | SELECT |
|---|
| | | a.`name`, |
|---|
| | | COUNT(u.user_id)num, |
|---|
| | | (SELECT count( user_id ) FROM sys_user WHERE type = 1 and community_id = a.community_id |
|---|
| | | AND last_login_time > DATE_FORMAT( date_sub( SYSDATE(), INTERVAL 0 DAY ), '%Y-%m-%d 00:00:00')) as dayNum |
|---|
| | | t1.dayNum |
|---|
| | | FROM |
|---|
| | | com_act a |
|---|
| | | LEFT JOIN sys_user u ON a.community_id = u.community_id and u.type=1 and a.state=0 |
|---|
| | | GROUP BY a.community_id)t ORDER BY t.num desc |
|---|
| | | left join (SELECT count( user_id ) dayNum,community_id FROM sys_user WHERE type = 1 |
|---|
| | | AND last_login_time > DATE_FORMAT( date_sub( SYSDATE(), INTERVAL 0 DAY ), '%Y-%m-%d 00:00:00') GROUP BY community_id) t1 on a.community_id = t1.community_id GROUP BY a.community_id |
|---|
| | | </select> |
|---|
| | | |
|---|
| | | <select id="selectCommunityUserOrderMonth" resultType="com.panzhihua.service_user.model.dtos.DataKanbanDTO"> |
|---|
