实用SQL（1）根据年月，获取不超过当前月的最新一条

最新推荐文章于 2024-07-22 03:03:57 发布

原创最新推荐文章于 2024-07-22 03:03:57 发布 · 191 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

数据库专栏收录该内容

6 篇文章

订阅专栏

该博客展示了如何从数据库中提取按月城市代码、分享费用、年份和月份的数据，并进行汇总分析。它聚焦于城市间的财务成本平均分摊情况，揭示了截至2021年5月前的数据趋势。关键操作包括筛选有效数据和按城市代码进行分组。

 select cityCode, shareExpense, year, month
        from (SELECT s.city_code      cityCode,
                     s.year,
                     s.month,
                     s.point_avg_cost shareExpense
              FROM bulletin_data_city_point_avg_cost s
              WHERE s.point_avg_cost IS NOT NULL
                AND concat(s.YEAR, CASE WHEN s.MONTH <10 THEN CONCAT("0", s.MONTH) ELSE s.MONTH END) <= 202105
                AND s.city_code is not null
                AND s.is_enable = 1
              ORDER BY s.year DESC,
                       s.month DESC) as b
        GROUP BY b.cityCode

private Map<String, FinancialCostBasicMonthProject> getPointsYearShareCostMap(ManageTimeUtils.ManageTime manageTime, String time) {
        List<FinancialCostBasicMonthProject> settlementCostBasisList = financialMapper.findPointsYearShareCost(manageTime.getYear(), manageTime.getMonth(), ManageTimeUtils.getMonthIdForNatural(time));
        Map<String, FinancialCostBasicMonthProject> cityShareCostMap = settlementCostBasisList.stream().collect(Collectors.toMap(FinancialCostBasicMonthProject::getCityCode, vo -> vo, (key1, key2) -> key2));
        if (MapUtils.isEmpty(cityShareCostMap)) {
            return new HashMap<>(FinancialConstants.CITY_NUMS);
        }
        return cityShareCostMap;
    }