在mysql查询中创建一个视图，以查找客户的信用和借记以及分组的余额 [英] create a view in mysql query for finding balance of credit and debit and grouping by customer

问题描述

我遇到了查询，因为我想将值clv_cuota分组。

这些是我的数据：

I am stuck with a query because i want to grouping the value "clv_cuota".
These are my data:

CREATE TABLE cuotas (
id_cuota int(11) NOT NULL,
clv_cuota int(11) DEFAULT NULL,
debe decimal(10,2) DEFAULT '0.00',
haber decimal(10,2) DEFAULT '0.00',
PRIMARY KEY (id_cuota)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE VIEW Balances AS
SELECT m.`id_cuota`, m.`clv_cuota`, m.`debe`, m.`haber`,
(select sum(debe) - sum(haber) FROM cuotas m2
where m2.id_cuota <= m.id_cuota 
) as balance
FROM `cuotas` m
GROUP BY m.clv_cuota,  m.`id_cuota` DESC;

INSERT INTO `cuotas` (`id_cuota`, `clv_cuota`, `debe`, `haber`) VALUES
('18', '115', '100.00', '0.00'),
('19', '116', '100.00', '0.00'),
('20', '115', '50.00', '0.00'),
('21', '115', '0.00', '150.00'),
('23', '116', '50.00', '0.00'),
('24', '116', '20.00', '0.00');

查询的结果是：

the result of the query is:

id_cuota	clv_cuota	debe	haber	balance
21      	115	         0	    150	     100
20       	115	        50	     0	     250
18         	115	       100     	0	     100
24      	116     	20   	0	     170
23	        116	        50	     0	     150
19      	116	       100	     0	     200

分组很好，但余额的结果不正确

我需要一个这样的结果

It is grouped well but the result of the balance is not correct
I need a result like this

id_cuota	clv_cuota	debe	haber	balance
21      	115	         0	    150	       0
20       	115	        50	     0	     150
18         	115	       100     	0	     100
24      	116     	20   	0	     170
23	        116	        50	     0	     150
19      	116	       100	     0	     100

提前感谢。

thanks in advance.

推荐答案

参考这些：

SQL中的滚动总和 - 一个实际示例 [ ^ ]

计算与事务一起存储的运行余额 [ ^ ]

< a href =http://stackoverflow.com/questions/21741631/calculate-running-balance-in-mysql-select-query-and-view>计算MySQL中的运行总数 [ ^ ]

如何使用mysql计算银行账户的运行余额[关闭] [ ^ ]

计算mysql选择查询和视图中的运行平衡 [ ^ ]

Refer these:
Rolling Sums in SQL - A Practical Example[^]
Calculating a running balance to store with transactions[^]
Calculate a running total in MySQL[^]
How to calculate running balance of banking table using mysql [closed][^]
Calculate running balance in mysql select query and view[^]

这是我想要的查询。

thx。

This is the query I wanted.
thx.

SELECT m.`id_cuota`, m.`clv_cuota`, m.`debe`, m.`haber`,
(select sum(debe) - sum(haber) FROM cuotas m2
where m2.id_cuota <= m.id_cuota and
    m2.clv_cuota = m.clv_cuota
) as balance
FROM `cuotas` m
GROUP BY m.clv_cuota,  m.`id_cuota` DESC;

这篇关于在mysql查询中创建一个视图，以查找客户的信用和借记以及分组的余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！