From a07b3420c0c94cf236d6bf92c8e26a8eedc0eaa6 Mon Sep 17 00:00:00 2001
From: huanghongfa <huanghongfa123456>
Date: 星期三, 25 八月 2021 14:30:58 +0800
Subject: [PATCH] 修复实有人口问题SQL
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java | 27 ++++++++++++++-------------
1 files changed, 14 insertions(+), 13 deletions(-)
diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java
index abc5173..cf3d33a 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java
@@ -196,18 +196,19 @@
+ " from com_mng_population_community_tags as cmpct where community_id = #{communityId}")
ComMngPopulationTotalVO getPopulationTotalByAdmin(@Param("communityId") Long communityId);
- @Select("select count(id) as populationTotal,"
- + "IFNULL((select count(id) from com_mng_population_house where community_id = cmp.act_id),0) as houseTotal, "
- + "IFNULL((select count(id) from com_mng_real_company where community_id = cmp.act_id),0) as companyTotal, "
- + "IFNULL((select count(village_id) from com_mng_village where community_id = cmp.act_id),0) as villageTotal "
- + " com_mng_population_community_tags as cmpct " + " where community_id = = #{communityId}")
+ @Select("select count(id) as populationTotal," +
+ "IFNULL((select count(id) from com_mng_population_house where community_id = cmpct.community_id),0) as houseTotal, " +
+ "IFNULL((select count(id) from com_mng_real_company where community_id = cmpct.community_id),0) as companyTotal, " +
+ "IFNULL((select count(village_id) from com_mng_village where community_id = cmpct.community_id),0) as villageTotal " +
+ " from com_mng_population_community_tags AS cmpct " +
+ " where cmpct.community_id = #{communityId}")
IndexBasicsStatisticsVO getScreenIndexByBasics(@Param("communityId") Long communityId);
@Select("SELECT " + " IFNULL( NULL, 1 ) AS type, " + " count( cmpct.id ) AS sum " + "FROM "
+ " com_mng_population_community_tags as cmpct "
+ " left join com_mng_population as cmp on cmpct.population_id = cmp.id " + "WHERE " + " cmp.sex = 1 "
+ " AND cmpct.community_id = #{communityId} UNION ALL " + "SELECT " + " IFNULL( NULL, 2 ) AS type, "
- + " count( id ) AS sum " + "FROM " + " com_mng_population_community_tags as cmpct "
+ + " count( cmpct.id ) AS sum " + "FROM " + " com_mng_population_community_tags as cmpct "
+ " left join com_mng_population as cmp on cmpct.population_id = cmp.id " + "WHERE " + " cmp.sex = 2 "
+ " AND cmpct.community_id = #{communityId}")
List<IndexPopulationSexStatisticsVO> getScreenIndexByPopulationSex(@Param("communityId") Long communityId);
@@ -270,8 +271,8 @@
List<ComMngUserTagVO> getUserTagListByCommunityId(@Param("communityId") Long communityId);
@Select("SELECT " + " count( id ) " + "FROM " + " com_mng_population_community_tags " + "WHERE "
- + " label like concat('%',#{label},'%')")
- Integer getSpecialStatisticsByLabel(@Param("label") String label);
+ + " community_id = #{communityId} and label like concat('%',#{label},'%')")
+ Integer getSpecialStatisticsByLabel(@Param("label") String label,@Param("communityId") Long communityId);
@Select("<script> " + "SELECT " + " count( egd.id ) AS gridTotal, "
+ " ( SELECT count( user_id ) FROM sys_user WHERE community_id = egd.grid_community_id AND type = 6 ) AS gridMemberTotal, "
@@ -506,15 +507,15 @@
@Select("SELECT " + "(SELECT COUNT(aa.age) FROM "
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id WHERE cmpct.community_id = #{communityId}) as aa where aa.age<= 16) as age16,"
+ "(SELECT COUNT(aa.age) FROM "
- + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 16 and aa.age<= 27) AS age27,"
+ + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id = #{communityId}) AS aa WHERE aa.age > 16 and aa.age<= 27) AS age27,"
+ "(SELECT COUNT(aa.age) FROM "
- + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 27 and aa.age<= 35) AS age35,"
+ + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id = #{communityId}) AS aa WHERE aa.age > 27 and aa.age<= 35) AS age35,"
+ "(SELECT COUNT(aa.age) FROM "
- + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 35 and aa.age<= 45) AS age45,"
+ + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id = #{communityId}) AS aa WHERE aa.age > 35 and aa.age<= 45) AS age45,"
+ "(SELECT COUNT(aa.age) FROM "
- + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 45 and aa.age<= 55) AS age55,"
+ + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id = #{communityId}) AS aa WHERE aa.age > 45 and aa.age<= 55) AS age55,"
+ "(SELECT COUNT(aa.age) FROM "
- + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 55) AS age55over")
+ + "(SELECT (SELECT TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id = #{communityId}) AS aa WHERE aa.age > 55) AS age55over")
Map<String, Long> indexCountByAge(@Param("communityId") Long communityId);
@Select("SELECT " + " id, " + " su.nick_name AS userName, " + " su.image_url, " + " caep.create_at, "
--
Gitblit v1.7.1