SELECT a.BIZ_DATE,2 as db_id,A.store_code,a.district_code,
a.order_method,a.cus_type,'' as sales_type,'' as sales_channel,
COUNT(case when Order_Amount>0 then a.trans_no end) as trans_cnt,
SUM(is null(a.people_number,0)) as people_number,
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount,
SUM(IS NULL(e.amt,0)) as amt,
SUM(IS NULL(e.amt_vld,0)) as amt_vld,
SUM(IS NULL(e.amt_uvld,0)) as amt_uvld,
SUM(IS NULL(e.amt_vld,0)) as amt_all
from (
select a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no,
case when SUM(is null(A.Sales_Amount,0)) =0 then 0 else SUM(is null(a.people_number,0)) end as people_number,
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount
FROM BI005 A
where a.biz_date>='2019-08-27 00:00:00'
and a.biz_date<='2019-08-21 00:00:00'
group by a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no)a
left join
(select b.trans_no,b.store_code,b.biz_date,b.district_code,
SUM(is null(case when d.flag='1' then b.amount end,0)) amt_vld,
SUM(is null(case when d.flag='0' then b.amount end,0)) amt_uvld,
SUM(is null(b.amount,0) ) amt
from BI007 b
left join
(select * from dhbi_pccode_all) d
on b.store_code = d.store_code
and b.tender_code = d.tender_code
where b.biz_date >='2019-08-27 00:00:00'
and b.biz_date<='2019-08-21 00:00:00'
group by b.trans_no,b.store_code,b.biz_date,b.district_code
) e
on a.trans_no = e.trans_no
and a.store_code = e.store_code
and a.biz_date = e.biz_date
and a.district_code = e.district_code
GROUP BY a.BIZ_DATE,A.store_code,a.district_code,a.order_method,a.cus_type
a.order_method,a.cus_type,'' as sales_type,'' as sales_channel,
COUNT(case when Order_Amount>0 then a.trans_no end) as trans_cnt,
SUM(is null(a.people_number,0)) as people_number,
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount,
SUM(IS NULL(e.amt,0)) as amt,
SUM(IS NULL(e.amt_vld,0)) as amt_vld,
SUM(IS NULL(e.amt_uvld,0)) as amt_uvld,
SUM(IS NULL(e.amt_vld,0)) as amt_all
from (
select a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no,
case when SUM(is null(A.Sales_Amount,0)) =0 then 0 else SUM(is null(a.people_number,0)) end as people_number,
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount
FROM BI005 A
where a.biz_date>='2019-08-27 00:00:00'
and a.biz_date<='2019-08-21 00:00:00'
group by a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no)a
left join
(select b.trans_no,b.store_code,b.biz_date,b.district_code,
SUM(is null(case when d.flag='1' then b.amount end,0)) amt_vld,
SUM(is null(case when d.flag='0' then b.amount end,0)) amt_uvld,
SUM(is null(b.amount,0) ) amt
from BI007 b
left join
(select * from dhbi_pccode_all) d
on b.store_code = d.store_code
and b.tender_code = d.tender_code
where b.biz_date >='2019-08-27 00:00:00'
and b.biz_date<='2019-08-21 00:00:00'
group by b.trans_no,b.store_code,b.biz_date,b.district_code
) e
on a.trans_no = e.trans_no
and a.store_code = e.store_code
and a.biz_date = e.biz_date
and a.district_code = e.district_code
GROUP BY a.BIZ_DATE,A.store_code,a.district_code,a.order_method,a.cus_type
解决方案 »
- dmp导入导出问题!
- PK修改后,还是发生主键重复插入的问题。
- [求助]求分组排序的Sql语句
- 请推荐基本学oracle的经典书籍
- oo4o连接数据库问题,在线
- 在WIN ME下如何能起动Net8 Easy Config???
- win2002下面能装Oracle9i Database Release 2 Enterprise/Standard/Personal Edition for Windows NT/2000/XP 这个版本嘛?
- 我服务器报了java.sql.SQLException: 用尽的 Resultset,是什么意思?
- Oracle在线技术文档中,那些文档中有关于所有视图的详细说明??
- 使用SQL将Oracle中同一列的多行记录拼接成一个字符串(有相关列)
- 11G 数据库表结构含中文,如何导入
- Kettle同步数据问题
换成
SUM(NVL(a.people_number,0)) as people_number,
a.order_method,a.cus_type,'' as sales_type,'' as sales_channel,
COUNT(case when Order_Amount>0 then a.trans_no end) as trans_cnt,
SUM( nvl(a.people_number,0)) as people_number,
SUM( nvl(a.order_amount,0)) as order_amount,
SUM( nvl(A.Sales_Amount,0)) as sales_amount,
SUM( nvl(a.discount_amount,0)) as discount_amount,
SUM( nvl(e.amt,0)) as amt,
SUM( nvl(e.amt_vld,0)) as amt_vld,
SUM( nvl(e.amt_uvld,0)) as amt_uvld,
SUM( nvl(e.amt_vld,0)) as amt_all
from (
select a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no,
case when SUM( nvl(A.Sales_Amount,0)) =0 then 0 else SUM( nvl(a.people_number,0)) end as people_number,
SUM( nvl(a.order_amount,0)) as order_amount,
SUM( nvl(A.Sales_Amount,0)) as sales_amount,
SUM( nvl(a.discount_amount,0)) as discount_amount
FROM BI005 A
where a.biz_date>='2019-08-27 00:00:00'
and a.biz_date<='2019-08-21 00:00:00'
group by a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no)a
left join
(select b.trans_no,b.store_code,b.biz_date,b.district_code,
SUM( nvl(case when d.flag='1' then b.amount end,0)) amt_vld,
SUM( nvl(case when d.flag='0' then b.amount end,0)) amt_uvld,
SUM( nvl(b.amount,0) ) amt
from BI007 b
left join
(select * from dhbi_pccode_all) d
on b.store_code = d.store_code
and b.tender_code = d.tender_code
where b.biz_date >='2019-08-27 00:00:00'
and b.biz_date<='2019-08-21 00:00:00'
group by b.trans_no,b.store_code,b.biz_date,b.district_code
) e
on a.trans_no = e.trans_no
and a.store_code = e.store_code
and a.biz_date = e.biz_date
and a.district_code = e.district_code
GROUP BY a.BIZ_DATE,A.store_code,a.district_code,a.order_method,a.cus_type