我有一个这样的表(事务(
ID | Name | Unit |Quantity_in|Quantity_out|transaction_date|
-----|-------| --------|-----------|------------|----------------|
100 | aaaa | packet | 100 | 0 | 02/07/2017 |
99 | cccc | packet | 70 | 0 | 20/06/2017 |
99 | cccc | packet | 0 | 20 | 22/07/2017 |
100 | aaaa | strip | 0 | 30 | 05/07/2017 |
102 | bbbb | packet | 50 | 0 | 29/06/2017 |
我想进行查询以获取两天之间的交易和当前余额，同时在 WHERE 条件中获取日期之前的先前余额，因此我进行以下查询
选择"d.ID"、"d.Name"、"d.单位"(选择"总和(tr.Quantity_in(- 总和(tr.Quantity_out( 来自交易 tr，其中 tr.transaction_date <01/07/2017 和 d.ID=tr.ID( 作为 Prev_bal，sum(d.Quantity_in(， sum(d.Quantity_out(， Prev_bal +(sum(d.Quantity_in(- sum(d.Quantity_out(( 作为交易 d 的current_balance其中 d.transaction_date 01/07/2017 和 30/07/2017 分组按 d.ID， d.Name， d.Unit
但查询的以下结果不包括 ID 102，因为 Where 条件中的日期之间没有事务
ID | Name | Unit | Prev_bal | Quantity_in | Quantity_out |Current_balance|
----|------| ------ |----------|-------------|---------------|---------------|
100 |aaaa |packet | 0 | 100 | 30 | 70
----|----- |--------|----------|-------------|---------------|---------------|
99 |cccc |packet | 70 | 0 | 20 | 50 |
任何人都可以帮助我进行查询，即使 WHERE 条件中的日期之间没有交易(如表中的 ID 102(，也能获得所有有余额的 ID 的结果。 我需要获取当前余额 (Current_balance(，其中 2 个日期与上一个余额 (Prev_bal( 之间的transaction_date，用于具有余额的 ID，其中 transaction_date
身份证 |姓名 |单位 |Prev_bal |Quantity_in | Quantity_out |Current_balance|
100 |aaaa |数据包 | 0 | 100 | 30 | 70
99 |中交 |包 | 70 | 0 | 20 | 50 |
102 |BBBB |数据包 | 50 | 0 | 0 | 50 |
所以，这是我的想法。
无论您提供的日期范围如何，您仍然希望每个 ID 都包含在结果集中。因此，无需过滤掉行。所以现在的问题只是找出一天结束时剩余的余额。因此，对于日期范围内的项目，余额是当前余额，对于日期范围之前的项目，它是之前的余额(基本上是上次当前余额(。 下面是使用 Oracle 中的分析函数的查询。
WITH dat
AS (SELECT 100 AS id,
'aaaa' AS name,
'packet' AS unit,
100 AS quantity_in,
0 AS quantity_out,
TO_DATE ('02/07/2017', 'DD/MM/YYYY') AS transaction_date
FROM DUAL
UNION
SELECT 99 AS id,
'cccc' AS name,
'packet' AS unit,
70 AS quantity_in,
0 AS quantity_out,
TO_DATE ('20/06/2017', 'DD/MM/YYYY') AS transaction_date
FROM DUAL
UNION
SELECT 99 AS id,
'cccc' AS name,
'packet' AS unit,
0 AS quantity_in,
20 AS quantity_out,
TO_DATE ('22/07/2017', 'DD/MM/YYYY') AS transaction_date
FROM DUAL
UNION
SELECT 100 AS id,
'aaaa' AS name,
'strip' AS unit,
0 AS quantity_in,
30 AS quantity_out,
TO_DATE ('05/07/2017', 'DD/MM/YYYY') AS transaction_date
FROM DUAL
UNION
SELECT 102 AS id,
'bbbb' AS name,
'packet' AS unit,
50 AS quantity_in,
0 AS quantity_out,
TO_DATE ('29/06/2017', 'DD/MM/YYYY') AS transaction_date
FROM DUAL)
SELECT id,
name,
unit,
quantity_in,
quantity_out,
transaction_date,
remaining_balance
FROM (SELECT id,
name,
unit,
quantity_in,
quantity_out,
balance,
transaction_date,
SUM (balance)
OVER (PARTITION BY id ORDER BY transaction_date ROWS UNBOUNDED PRECEDING)
AS remaining_balance,
RANK () OVER (PARTITION BY id ORDER BY transaction_date DESC) AS rn
FROM (SELECT id,
name,
unit,
quantity_in,
quantity_out,
quantity_in
- quantity_out
AS balance,
transaction_date
FROM dat))
WHERE rn = 1;
它产生此结果。
ID |NAME|UNIT | QUANTITY_IN|QUANTITY_OUT|TRANSACTION_DATE|REMAINING_BALANCE
99 |cccc|packet | 0| 20| 7/22/2017| 50
100|aaaa|strip | 0| 30| 7/5/2017| 70
102|bbbb|packet | 50| 0| 6/29/2017| 50