首页 计算机学科 blog sql

# pgsql 从 5 张不同表查询字段数据并综合返回

编写的查询数据库表的 sql

思路：从 5 张不同的表中，它们的结构大致相同。我们从 5 张表中拿到不同的字段的数据并返回

SELECT 
    cr.YEAR,
    CAST(SUM(cr.operating_income) AS DECIMAL(10, 2)) AS total_operating_income,
    CAST(SUM(nr.total_profit) AS DECIMAL(10, 2)) AS total_total_profit,
    CAST(SUM(nr.net_profit) AS DECIMAL(10, 2)) AS total_net_profit,
    CAST(SUM(cf.asset_liability_ratio) AS DECIMAL(10, 2)) AS total_asset_liability_ratio,
    CAST(SUM(cf.return_on_equity) AS DECIMAL(10, 2)) AS total_return_on_equity,
    CAST(SUM(cf.operate_cash_ratio) AS DECIMAL(10, 2)) AS total_operate_cash_ratio,
    CAST(SUM(cf.assets) AS DECIMAL(10, 2)) AS total_assets,
    CAST(SUM(cf.operating_activities_cash_flow) AS DECIMAL(10, 2)) AS total_operating_activities_cash_flow,
    CAST(SUM(dr.liabilities) AS DECIMAL(10, 2)) AS total_liabilities
FROM
    (
    SELECT
        YEAR,
        SUM(operating_income) :: NUMERIC AS operating_income
    FROM
        pro_va_mea_province_cash_flow_financing_income_costs_result
    GROUP BY
        YEAR
    ) AS cr
LEFT JOIN 
    (
    SELECT
        YEAR,
        SUM(total_profit) :: NUMERIC AS total_profit,
        SUM(net_profit) :: NUMERIC AS net_profit
    FROM
        pro_va_mea_province_cost_profit_net_result
    GROUP BY
        YEAR
    ) AS nr ON cr.YEAR = nr.YEAR
LEFT JOIN 
    (
    SELECT
        YEAR,
        SUM(asset_liability_ratio) :: NUMERIC AS asset_liability_ratio,
        SUM(return_on_equity) :: NUMERIC AS return_on_equity,
        SUM(operate_cash_ratio) :: NUMERIC AS operate_cash_ratio,
        SUM(assets) :: NUMERIC AS assets,
        SUM(operating_activities_cash_flow) :: NUMERIC AS operating_activities_cash_flow
    FROM
        pro_va_mea_province_equity_ratio_cash_flow
    GROUP BY
        YEAR
    ) AS cf ON cr.YEAR = cf.YEAR
LEFT JOIN 
    (
    SELECT
        YEAR,
        SUM(liabilities) :: NUMERIC AS liabilities
    FROM
        pro_va_mea_province_liabilities_depreciation_result
    GROUP BY
        YEAR
    ) AS dr ON cr.YEAR = dr.YEAR
GROUP BY
    cr.YEAR 
ORDER BY
    cr.YEAR;

返回 List<Map<String, Object>>
返回结果：

[{
  year=2021,
  total_net_profit=0.00,
  total_asset_liability_ratio=0.00,
  total_return_on_equity=0.00,
  total_assets=0.00,
  total_operating_activities_cash_flow=0.00,
  total_liabilities=0.00,
  total_operating_income=0.00,
  total_total_profit=0.00,
  total_operate_cash_ratio=0.00
}
{
  year=2022,
  total_net_profit=0.00,
  total_asset_liability_ratio=0.00,
  total_return_on_equity=0.00,
  total_assets=0.00,
  total_operating_activities_cash_flow=0.00,
  total_liabilities=0.00,
  total_operating_income=0.00,
  total_total_profit=0.00,
  total_operate_cash_ratio=0.00
}
{
  year=2023,
  total_net_profit=0.00,
  total_asset_liability_ratio=0.00,
  total_return_on_equity=0.00,
  total_assets=0.00,
  total_operating_activities_cash_flow=0.00,
  total_liabilities=0.00,
  total_operating_income=0.00,
  total_total_profit=0.00,
  total_operate_cash_ratio=0.00
}]

对数据进行格式转换：

private static List<Map<String, Object>> formatAnnualEvaluation(List<Map<String, Object>> annualEvaluation) {
        List<Map<String, Object>> formattedData = new ArrayList<>();
        // 需要显示的字段列表
        AnnualEvaluationF[] values = AnnualEvaluationF.values();
        String fielder = Arrays.toString(values).replace("[", "").replace("]", "");
        String[] fields = fielder.split(",");
        // 遍历每个字段
        for (String field : fields) {
            // 初始化一个 Map 用于存储当前字段的数据
            Map<String, Object> fieldData = new HashMap<>();
            fieldData.put("field", field);
            // 遍历每个年份，将当前字段的值按年份填充到 fieldData 中
            for (Map<String, Object> entry : annualEvaluation) {
                Integer year = ObjectUtils.isNotNull(entry.get("year")) ? Integer.valueOf(String.valueOf(entry.get("year"))) : 0;
                Object value = ObjectUtils.isNotNull(entry.get(field)) ? entry.get(field) : null;
                fieldData.put(year.toString(), value);
            }
            // 将当前字段的数据添加到 formattedData 中
            formattedData.add(fieldData);
        }
        return formattedData;
    }

打印结果：

[
  {
    2023=0.00,
    2022=0.00,
    2021=0.00,
    field="total_asset_liability_ratio"
  },
  {
    2023=0.00,
    2022=0.00,
    2021=0.00,
    field="total_net_profit"
  },
  {
    2023=0.00,
    2022=0.00,
    2021=0.00,
    field="total_return_on_equity"
  },
  {
    2023=0.00,
    2022=0.00,
    2021=0.00,
    field="total_assets"
  },
  {
    2023=0.00,
    2022=0.00,
    2021=0.00,
    field="total_operating_activities_cash_flow"
  },
  {
    2023=0.00,
    2022=0.00,
    2021=0.00,
    field="total_liabilities"
  },
  {
    2023=0.00,
    2022=0.00,
    2021=0.00,
    field="total_operating_income"
  },
  {
    2023=0.00,
    2022=0.00,
    2021=0.00,
    field="total_total_profit"
  },
  {
    2023=0.00,
    2022=0.00,
    2021=0.00,
    field="total_operate_cash_ratio"
  }
]