Sql 如何用客户名称计算sum(dr)和sum(cr)

发布于12月13日

SELECT   id,   Customer,  SUM( DEBIT ) OVER (PARTITION BY ID) as Debit,  abs(SUM( Payment ) OVER (PARTITION BY ID)) as PAYMENT  FROM
(
SELECT
      Cust_id as id,
      cust_name as customer,      
      OPENING_BLNC as Debit,
      0 as PAYMENT    FROM Customer
    union all
    select 
    c.Cust_id as id,
      c.cust_name as customer,      
      i.total_amount as Debit,
     0 as PAYMENT
    FROM Customer c,transaction t,invoice i where t.tran_id=i.inv_tran_id and c.cust_id=t.cust_id 
    union all 
    select 
    c.Cust_id as id,
      c.cust_name as customer,      
      0 as Debit,
     a.cr as PAYMENT 
    FROM Customer c,accounts a where  c.cust_id=a.cust_id 
    )

结果:

1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000

重复获取相同的记录.需要根据客户名称和期初余额计算dr和cr

输出应为

Cus_Id  Customer        Debit           Credit
1       KARIMULLAH      68697.04        40000
2       Mr John         25020           15000

SELECT id, Customer, SUM( DEBIT ) as Debit, ABS(SUM( Payment ) as PAYMENT FROM ( SELECT Cust_id as id, cust_name as customer, OPENING_BLNC as Debit, 0 as PAYMENT FROM Customer union all select c.Cust_id as id, c.cust_name as customer, i.total_amount as Debit, 0 as PAYMENT FROM Customer c INNER JOIN transaction t ON c.cust_id=t.cust_id INNER JOIN invoice i ON t.tran_id=i.inv_tran_id union all select c.Cust_id as id, c.cust_name as customer, 0 as Debit, a.cr as PAYMENT FROM Customer c INNER JOIN accounts a ON c.cust_id=a.cust_id ) GROUP BY id, customer;

Sql 如何用客户名称计算sum(dr)和sum(cr)

推荐答案

Sql相关问答推荐

在postgresql中使用来自另一个字段的日期名称作为JSONB查询中的关键字

Microsoft Access UNION将长文本字段限制为255个字符

如何将资源密集型自连接转换为更快的查询？

SQL查询视图与连接？

MariaDB查询在逗号分隔的字符串中查找多个值

具有多个条件的SQL否定

动态组/转置

使用递归CTE在BigQuery中获取文件路径

从给定数据中查找下一个工作日期

输出连续出现两次以上的行

SQL 查询是否返回列表中仅包含某些值而不包含其他值的行？

如何根据 Google BigQuery 中的特定列值连接一列的 N 行？

清理 XML 数据

SQL的左连接在多对多关系情况下使用

将 MERGE 语句与 Oracle PL/SQL 表类型一起使用时，导致无效数据类型错误的原因是什么？

如何 Select 一列具有最小值而另一列具有给定值的记录？

如何筛选 GROUP BY 结果？ HAVING 没有产生预期的结果

SQL Server 分区和 Run Case 语句

我现在如何显示重复的汽车？ postgresql

包含多行的 SQL 查询