with new_balance_transaction as (select bt.owner_id, bt.owner_type,bt.payment_type,
bt.balance_amount,bt.commision_amount, bt.COMMISION_RATE, bt.status,bt.transaction_type,bt.create_time,
(case when bt.status=0 then bt.balance_amount else 0 end) invlid_total_balance_amount,
(case when bt.status=1 then bt.balance_amount else 0 end) vlid_total_balance_amount,
(case when bt.status=0 then bt.commision_amount else 0 end) invlid_total_commision_amount,
(case when bt.status=1 then bt.commision_amount else 0 end) vlid_total_commision_amount
from balance_transactions bt where bt.owner_id is Not null
and bt.create_time >= to_timestamp('2014-09-01 00:00:00.000000','yyyy-mm-dd hh24:mi:ssxff')
and bt.create_time <= to_timestamp('2014-09-30 23:59:59.999999','yyyy-mm-dd hh24:mi:ssxff') ),
balance_transaction_report as (
select o.login_name as owner, nbt.owner_type, nbt.payment_type,
nbt.balance_amount,nbt.commision_amount,nbt.COMMISION_RATE,
nbt.status,nbt.transaction_type,nbt.create_time,
nbt.invlid_total_balance_amount,nbt.vlid_total_balance_amount,
nbt.invlid_total_commision_amount,nbt.vlid_total_commision_amount
from new_balance_transaction nbt, operator o
where nbt.owner_id = o.operator_id and nbt.owner_type =1
union all
select m.merchant_name as owner,
nbt.owner_type, nbt.payment_type,
nbt.balance_amount,nbt.commision_amount,
nbt.COMMISION_RATE, nbt.status,nbt.transaction_type,
nbt.create_time, nbt.invlid_total_balance_amount,nbt.vlid_total_balance_amount,
nbt.invlid_total_commision_amount,nbt.vlid_total_commision_amount
from new_balance_transaction nbt, merchant m where nbt.owner_id = m.merchant_id and nbt.owner_type =2 )
select brr.owner,brr.owner_type,brr.payment_type, brr.balance_amount,
brr.commision_amount,brr.COMMISION_RATE,brr.status,brr.transaction_type,brr.create_time,
brr.invlid_total_balance_amount,brr.vlid_total_balance_amount,
brr.invlid_total_commision_amount, brr.vlid_total_commision_amount from balance_transaction_report brr
order by brr.create_time DESC
分享到：
相关推荐
详细介绍oracle数据库中新出的with_as语法以及相关使用
### Oracle数据库中的START WITH 和 CONNECT BY 用法详解 在Oracle数据库中，处理层次结构数据时，`START WITH` 和 `CONNECT BY` 是非常有用的两个关键字。这些关键字可以帮助我们在查询时构建出树形或者层级结构的...
本博客简单介绍一下oracle分组函数之rollup的用法，rollup函数常用于分组统计，也是属于oracle分析函数的一种 环境准备 create table dept as select * from scott.dept; create table emp as select * from ...
Oracle 下 WITH CHECK OPTION 用法 WITH CHECK OPTION 是 Oracle 中的一种视图定义选项，它可以确保数据库中正在修改的数据的完整性。该选项通常用在视图定义中，以确保任何引用该视图的 INSERT 或 UPDATE 语句都...
`START WITH...CONNECT BY PRIOR`是Oracle SQL中的一个特性，用于处理具有层级关系的数据。这个子句允许我们遍历和查询具有父子关系的数据，例如部门和其下属子部门，或者员工和他们的上级经理。 1. **START WITH...
SELECT ename, REGEXP_LIKE(ename, '^[0-9]') AS starts_with_number FROM emp; ``` 这个查询将返回一个标志，表明员工名字是否以数字开始。 了解并熟练掌握Oracle分析函数和正则表达式函数，能显著提高在数据库...
SELECT extract(month FROM sysdate) AS "This Month", extract(year FROM add_months(sysdate, 36)) AS "Years" FROM dual; ``` 这将分别提取当前月和当前日期36个月后对应的年份。 以上就是Oracle中常用的日期...
代码如下:with alias_name1 as (subquery1), alias_name2 as (subQuery2), …… alias_nameN as (subQueryN) select col1,col2…… col3from alias_name1,alias_name2……,alias_nameN Oracle with语句的...
WITH sales_data AS ( SELECT product_id, TO_CHAR(sale_date, 'YYYY-MM') AS sale_month, sale_amount FROM sales ) SELECT * FROM sales_data PIVOT ( SUM(sale_amount) FOR product_id IN (DECODE(product_...
WITH ranked_data AS ( SELECT name, code, id, ROW_NUMBER() OVER (PARTITION BY name, code ORDER BY id DESC) AS rn FROM table ) SELECT name, code, id FROM ranked_data WHERE rn = 1; ``` 总的来说，`...
WITH data AS (SELECT 'apple,banana,orange' str FROM dual) SELECT regexp_substr(str, '[^,]+', 1, level) fruit FROM data CONNECT BY level (regexp_replace(str, '[^,]+')) + 1; ``` 以上就是Oracle...
INNER JOIN Sales.SalesPerson AS sp ON e.EmployeeID = sp.SalesPersonID INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.EmployeeID LIKE '2%' ORDER BY e.EmployeeID, c.LastName; GO ```...
- 掌握WITH子句的基本用法及特点。 - 了解WITH子句如何优化查询性能。 - 孶明WITH子句的常见应用场景。 #### WITH子句概述 在Oracle数据库中，WITH子句是一种非常有用的特性，它允许用户定义一个临时的结果集（通常...
例如：CREATE SEQUENCE 序列号的名称(最好是表名+序列号标记)INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE; 3. 单引号的处理 MySQL 里可以用双引号包起字符串，Oracle 里只可以用单引号包起字符串。...
WITH lcy AS ( SELECT regexp_substr('10,20,30,30,50,60,80,80,90,100','[^,]+',1,level) score FROM dual CONNECT BY level ) SELECT score, RANK() OVER (ORDER BY TO_NUMBER(score) DESC) "rank", DENSE_...
WITH t AS (SELECT 1 id FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual) SELECT CARDINALITY(ARRAY[SELECT id FROM t]) cnt FROM dual; ``` **结果**: ``` CNT ---- 3 ``` #### CAST **...
韩顺平oracle学习笔记 第0讲:如何学习oracle 一、如何学习oracle Oracle目前最流行的数据库之一，功能强大，性能卓越。学习oracle需要具备一定基础: 1.学习过一门编程语言(如:java ，c) 2.最好学习过一门别的...
CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION; CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id * 365 WITH CHECK OPTION; CREATE VIEW v2 AS SELECT prod_id, cust_id, ...