表結構如下
+---------------+---------+---------+
| customer_name | date | balance |
+---------------+---------+---------+
| 123 | june 14 | 20 |
| 123 | june 15 | 30 |
| 1234 | june 14 | 30 |
| 12345 | june 16 | 50 |
+---------------+---------+---------+我想加入同一張桌子,将原始資料保留為2014年,我想分析趨勢,看看哪些客戶的平衡值不會從2014年開始變化。
例如我想展示下面
+-----------+-----------+-----------+
| custmomer | june14bal | june15bal |
+-----------+-----------+-----------+
| 1234 | 30 | null |
| 123 | 20 | 30 |
+-----------+-----------+-----------+我有三個左連接配接,但似乎無法正常工作。最重要的是從2014年起開始使用記錄樣本。
目前腳本
with TABLE_DATA as
(
select Customer ,DATE, Balance
from table
where dATE in ('30-JUN-2014','30-juN-2015')
)
SELECT
sum(inv1.balance) as year1bal,
suminv2.balance) as year2bal,
customer,
date
from table_datA inv1
left join TABLE_DATA inv2
on inv1.customer= inv2.customer and inv2.as_of_Date = '30-June-2015'
group by date, customer