DB2 SQL聚合(DB2 SQL Aggregation)

DB2 SQL聚合(DB2 SQL Aggregation)

我在DB2数据库中有一组记录，我需要提供一个报告，其中为所有用户提供了用户完成的所有业务字段。 已为所有用户汇总了所有不同的MGD_TYPE。 我怎么能用SQL做到这一点？

USER_ID MGD_TYPE MTHLY_AMT EFF_DATE 1222 FX 332.21 06/23/10 3333 REAL_ESTATE 2221.03 06/23/10 1222 MERGERS 1882.43 02/03/12 3333 PORTFOLIO 721.44 01/22/15 USER FX_AMT REAL_EST_AMT MERGERS_AMT PORTFOLIO_AMT 1222 332.21 0.0 1882.43 0.0 3333 0.0 2221.03 0.0 721.44

I have a set of records in a DB2 database and I need to present a report in which all the fields for business done by users are provided for all users. All the different MGD_TYPE have been summarized for all users. How can I do this using SQL?

USER_ID MGD_TYPE MTHLY_AMT EFF_DATE 1222 FX 332.21 06/23/10 3333 REAL_ESTATE 2221.03 06/23/10 1222 MERGERS 1882.43 02/03/12 3333 PORTFOLIO 721.44 01/22/15 USER FX_AMT REAL_EST_AMT MERGERS_AMT PORTFOLIO_AMT 1222 332.21 0.0 1882.43 0.0 3333 0.0 2221.03 0.0 721.44

最满意答案

这是一个基本的PIVOT查询：

SELECT USER_ID, SUM( CASE MGD_TYPE WHEN 'FX' THEN MTHLY_AMT END ) As FX_AMT, SUM( CASE MGD_TYPE WHEN 'REAL_ESTATE' THEN MTHLY_AMT END ) As REAL_EST_AMT, SUM( CASE MGD_TYPE WHEN 'MERGERS' THEN MTHLY_AMT END ) As MERGERS_AMT, SUM( CASE MGD_TYPE WHEN 'PORTFOLIO' THEN MTHLY_AMT END ) As PORTFOLIO_AMT FROM table GROUP BY USER_ID

This is a basic PIVOT query:

SELECT USER_ID, SUM( CASE MGD_TYPE WHEN 'FX' THEN MTHLY_AMT END ) As FX_AMT, SUM( CASE MGD_TYPE WHEN 'REAL_ESTATE' THEN MTHLY_AMT END ) As REAL_EST_AMT, SUM( CASE MGD_TYPE WHEN 'MERGERS' THEN MTHLY_AMT END ) As MERGERS_AMT, SUM( CASE MGD_TYPE WHEN 'PORTFOLIO' THEN MTHLY_AMT END ) As PORTFOLIO_AMT FROM table GROUP BY USER_ID