每个员工每周佣金的Oracle SQL

Here is the sample data in an Oracle 11g database I am trying to work with to come up with a SQL query to return commission earned by each employee per week. If an employee did not earn a commission in a week, then it should display 0 as in the commission_earned column. (Assuming the week start on Monday)

以下是Oracle 11g数据库中的示例数据，我正在尝试使用SQL查询来返回每位员工每周赚取的佣金。如果员工在一周内没有获得佣金，那么它应该在Commission_earned列中显示0。 （假设星期一开始）

SQL> select * from SALES;

SQL> select * from SALES;

EMPLID PRODUCT_TYPE          PRODUCTID SALE_AMOUNT COMMISSION_EARNED SALE_DATE

---

  1001 Desktop                55355251         750                45 02-MAY-16
  1002 Desktop                 2332134         600                30 02-MAY-16
  1001 Laptop                   773643        1200                65 02-MAY-16
  1003 Camera                  5546232         450                25 03-MAY-16
  1002 Printer                  445321         150                15 04-MAY-16
  1001 Printer                  445321         150                15 10-MAY-16
  1003 Camera                  5546232         450                25 10-MAY-16

I am trying to come up with a sql that would return the total commission earned by each employee per week. I would appreciate any help or pointers.

我想提出一个sql，它将返回每个员工每周赚取的总佣金。我将不胜感激任何帮助或指示。

WEEKOF EMPLID COMMISSION_EARNED 02-MAY-16 1001 110 02-MAY-16 1002 45 02-MAY-16 1003 25 09-MAY-16 1001 15 09-MAY-16 1002 0 09-MAY-16 1003 25

WEEKOF EMPLID COMMISSION_EARNED 02-MAY-16 1001 110 02-MAY-16 1002 45 02-MAY-16 1003 25 09-MAY-16 1001 15 09-MAY-16 1002 0 09-MAY-16 1003 25

I came up with the below sql but it does not display a row for emplid 1002 with commision_earned as 0 for the week starting with 09-MAY-16

我想出了下面的sql，但它没有为emplid 1002显示一行，commision_earned为0，从09-MAY-16开始的一周

SQL> select trunc(sale_date,'IW') WEEKOF,emplid,sum(COMMISSION_EARNED) COMMISSION_EARNED from sales group by trunc(sale_date,'IW'),emplid order by trunc(sale_date,'IW'),emplid;

SQL> select trunc（sale_date，'IW'）WEEKOF，emplid，sum（COMMISSION_EARNED）COMMISSION_EARNED来自销售组的截断（sale_date，'IW'），emplid order by trunc（sale_date，'IW'），emplid;

WEEKOF EMPLID COMMISSION_EARNED 02-MAY-16 1001 110 02-MAY-16 1002 45 02-MAY-16 1003 25 09-MAY-16 1001 15 09-MAY-16 1003 25

WEEKOF EMPLID COMMISSION_EARNED 02-MAY-16 1001 110 02-MAY-16 1002 45 02-MAY-16 1003 25 09-MAY-16 1001 15 09-MAY-16 1003 25

1 个解决方案

#1

You can generate all the rows with a cross join and then use left join to get the information from the table:

您可以使用交叉连接生成所有行，然后使用左连接从表中获取信息：

select w.wk, e.emplid,
       coalesce(sum(t.commission_earned), 0) as commission_earned
from (select distinct trunc(weekof, 'IW') as wk from test) w cross join
     (select distinct emplid from test) e left join
     test t
     on t.emplid = e.emplid and trunc(t.weekof, 'IW') = w.wk
group by w.wk, e.emplid
order by w.wk, e.emplid;