查询两个关系断开表或合并表的问题

Question

我有以下两个表，即使在下面的查询中开始查询也很难。这是我尝试过的，但我现在正在学习SQL。

尝试1

Select Employee.First_Name, Transactions.Transaction_Type, Transactions.Refund
From (Select Transaction_ID, Employee_ID, Transaction_Amount AS Refund, Transaction_Date, Reference_Transaction_ID
      From Employee INNER JOIN Transactions ON Employee.Employee_ID=Transactions.Employee_ID 
      Where Transaction_Type="Refund";) N,
      Employee INNER JOIN Transactions ON Employee.Employee_ID=Transactions.Employee_ID
WHERE Transaction_Type = "Sale";

尝试2

SELECT First_Name
FROM (
SELECT Employee.Employee_ID,Employee.First_Name, Transactions.Transaction_ID, Transactions.Transaction_Amount, Transactions.Transaction_Type, Transactions.Reference_Transaction_ID
FROM Employee INNER JOIN Transactions ON Employee.Employee_ID=Transactions.Employee_ID
    WHERE Transaction_Type = 'Sale'
UNION
SELECT Employee.Employee_ID,Employee.First_Name, Transactions.Transaction_ID, Transactions.Transaction_Amount, Transactions.Transaction_Type As Refund, Transactions.Reference_Transaction_ID
FROM Employee INNER JOIN Transactions ON Employee.Employee_ID=Transactions.Employee_ID
    WHERE Transaction_Type = 'Refund'; 

查询问题中的查询提供销售数据，以及在2017年1月1日或之后进行交易的销售额净销售额（销售 - 退款）。输出应包括：

员工名称，销售量，退款量和净销售额。

create table Employee
(Employee_ID int(45) primary key,
First_Name varchar(45),
Last_Name varchar(45),
Department_ID int(45),
Supervisor_ID int(45),
Salary int,
Hire_Date datetime,
End_Date datetime,
foreign key(Department_ID) references Department(Department_ID));
create table Transactions
(Transaction_ID int(45) primary key,
Employee_ID int(45),
Transaction_Amount float(45),
Transaction_Type varchar(45),
Transaction_Date datetime,
Reference_Transaction_ID int(45),
foreign key(Employee_ID) references Employee(Employee_ID));
INSERT INTO Employee
  ( Employee_ID, First_Name, Last_Name, Department_ID, Supervisor_ID, Salary, Hire_Date, End_Date )
VALUES
  (1, 'Mary', 'Brown', 100, 7, 80000, 01/01/2014, 12/13/2015), 
  (2, 'John','Smail', 200, 3, 75000, 08/15/2013, null), 
  (3, 'Mike', 'Ray', 200, 10, 125000, 03/01/2016, null),
  (4, 'Steve', 'Ansky', 300, 5, 100000, 11/01/2013, null),
  (5, 'Jennifer','Ross', 300, 10, 130000, 04/03/2012, null), 
  (6, 'Jacob', 'Jones', 100, 7, 70000, 04/15/2011, null), 
  (7, 'Marisa','Sikes', 100, 10, 135000, 12/15/2014, null ),
  (8, 'Kate', 'OBrien', 200, 3, 70000, 02/12/2013, null),
  (9, 'Don', 'Chapman',300,5,80000,09/08/2011,06/13/2013), 
  (10, 'Lori','Reese',400, null, 250000, 06/01/2010, null);

INSERT INTO Transactions
  ( Transaction_ID, Employee_ID, Transaction_Amount, Transaction_Type, Transaction_Date, Reference_Transaction_ID )
VALUES
  (1, 2, 1125.24, 'Sale', 03/01/2017, null), 
  (2, 6,425.24, 'Refund', 03/03/2017, 1), 
  (3, 3, 123.21, 'Sale', 03/03/2017, null),
  (4, 3, 900.50, 'Sale', 03/04/2017, null),
  (5, 6,450.50, 'Refund', 03/06/2017, 4), 
  (6, 2, 823.43, 'Sale', 03/06/2017, null), 
  (7, 2,567.99, 'Sale', 03/07/2017, null ),
  (8, 8, 1003.21, 'Sale', 03/08/2017, null),
  (9, 8, 754.33, 'Sale', 03/08/2017, null), 
  (10, 7,553.21,'Refund', 03/10/2017, 8);

Answer 1

如果我正确理解这一点，这应该有效：

select E.Employee_name, 
  sum(case when T.Transaction_Type = 'Sale' then T.Transaction_Amount end) as Sales_Volume,
  sum(case when T.Transaction_Type = 'Refund' then T.Transaction_Amount end) as Refund_Volume,
  sum(case when T.Transaction_Type = 'Sale' then T.Transaction_Amount end) -
  sum(case when T.Transaction_Type = 'Refund' then T.Transaction_Amount end) as Net_Sales
  from Employee E
  inner join
  Transactions T
  on E.Employee_ID = T.Employee_ID
  where T.Transaction_Date >= '2017-01-01'
  group by E.Employee_name

看来您需要从我能告诉的，类似的内容中使用Reference_transaction_id：

With A as (
    select E.Employee_name, T.Transaction_ID, T.Reference_Transaction_Id,
      sum(case when T.Transaction_Type = 'Sale' then T.Transaction_Amount end) as Sales_Volume,
      sum(case when T.Transaction_Type = 'Sale' then T.Transaction_Amount end) -
      sum(case when T.Transaction_Type = 'Refund' then T.Transaction_Amount end) as Net_Sales
      from Employee E
      inner join
      Transactions T
      on E.Employee_ID = T.Employee_ID
      where T.Transaction_Date >= '2017-01-01'
      group by E.Employee_name, T.Transaction_ID, T.Reference_Transaction_Id
      ),
    B as (
    Select E.Employee_name, T.Transaction_ID, T.Reference_Transaction_Id,
      sum(case when T.Transaction_Type = 'Refund' then T.Transaction_Amount end) as Refund_Volume
      from Employee E
      inner join
      Transactions T
      on E.Employee_ID = T.Employee_ID
      where T.Transaction_Date >= '2017-01-01'
      group by E.Employee_name, T.Transaction_ID, T.Reference_Transaction_Id)
    Select A.Employee_name, A.Transaction_ID, A.Sales_Volume, A.Net_Sales, B.Refund_Volume
    from A left outer join B
    on A.Transaction_ID = B.Reference_Transaction_Id