删除所有带有相反符号的记录

Question

我正在寻找一个SQL查询(甚至更好的LINQ查询)来删除已经取消休假的人，即删除所有具有相同名称和相同START和END的记录，DAYS_TAKEN值仅在符号中不同。

如何从中得到

NAME    |DAYS_TAKEN |START      |END        |UNIQUE_LEAVE_ID    
--------|-----------|-----------|-----------|-----------
Alice   |  2        | 1 June    | 3 June    | 1 --remove because cancelled
Alice   | -2        | 1 June    | 3 June    | 2 --cancelled
Alice   |  3        | 5 June    | 8 June    | 3 --keep
Bob     | 10        | 4 June    | 14 June   | 4 --keep
Charles | 12        | 2 June    | 14 June   | 5 --remove because cancelled
Charles | -12       | 2 June    | 14 June   | 6 --cancelled
David   | 5         | 3 June    | 8 June    | 7 --keep

?

NAME    |DAYS_TAKEN |START      |END        |UNIQUE_LEAVE_ID    
--------|-----------|-----------|-----------|-----------
Alice   |  3        | 5 June    | 8 June    | 3 --keep
Bob     | 10        | 4 June    | 14 June   | 4 --keep
David   | 5         | 3 June    | 8 June    | 7 --keep

What I've try

Query1查找所有已取消的记录(不确定这是否正确)

SELECT L1.UNIQUE_LEAVE_ID 
FROM LEAVE L1 
INNER JOIN LEAVE L2 ON L2.DAYS_TAKEN > 0 AND ABS(L1.DAYS_TAKEN) = L2.DAYS_TAKEN AND L1.NAME= L2.NAME AND L1.START = L2.START AND L1.END = L2.END
WHERE L1.DAYS_TAKEN < 0

然后在内部选择中使用两次Query1，如下

SELECT L.* FROM LEAVE L WHERE 
L.UNIQUE_LEAVE_ID NOT IN (Query1)
AND L.UNIQUE_LEAVE_ID NOT IN (Query1)

是否有一种方法可以只使用一次内部查询?

(这是一个Oracle数据库，从。net/c#调用)

Answer 1

您可以使用如下查询:

SELECT NAME, START, END  
FROM LEAVE
GROUP BY NAME, START, END
HAVING SUM(DAYS_TAKEN) = 0

，以获得已取消的NAME, START, END组(假设取消记录的DAYS_TAKEN与初始记录的天数相反)。

输出:

NAME    |START      |END        
--------|-----------|----------
Alice   | 1 June    | 3 June
Charles | 2 June    | 14 June

使用上面的查询作为派生表，您可以获得与'取消'组无关的记录:

SELECT L1.NAME, L1.DAYS_TAKEN, L1.START, L1.END, L1.UNIQUE_LEAVE_ID  
FROM LEAVE L1
LEFT JOIN (
  SELECT NAME, START, END  
  FROM LEAVE
  GROUP BY NAME, START, END
  HAVING SUM(DAYS_TAKEN) = 0
) L2 ON L1.NAME = L2.NAME AND L1.START = L2.START AND L1.END = L2.END
WHERE L2.NAME IS NULL

输出:

NAME    |DAYS_TAKEN |START      |END        |UNIQUE_LEAVE_ID    
--------|-----------|-----------|-----------|-----------
Alice   | 3         | 5 June    | 8 June    | 3 
Bob     | 10        | 4 June    | 14 June   | 4 
David   | 5         | 3 June    | 8 June    | 7 

Answer 2

您可以使用not exists:

select l.*
from leave l
where not exists (select 1
                  from leave l2
                  where l2.name = l.name and l2.start = l.start and
                        l2.end = l.name and l2.days_taken = - l.days_taken
                 );

这个查询可以利用leave(name, start, end, days_taken)上的索引。

Answer 3

SUM() OVER:

SELECT x.*
  FROM (SELECT l.*, SUM (days_taken) OVER (PARTITION BY name, "START", "END", ABS (days_taken) ORDER BY NULL) s
          FROM leave l) x
 WHERE s <> 0

如果你有Oracle 12，这给你取消:

SELECT l.*
  FROM leave l,
       LATERAL (SELECT days_taken
                  FROM leave l2
                 WHERE l2.name = l.name 
                   AND l2."START" = l."START" 
                   AND l2."END" = l."END" 
                   AND l2.days_taken = -l.days_taken) x

，这是应该保留的:

SELECT l.*
  FROM leave l
       OUTER APPLY (SELECT days_taken
                      FROM leave l2
                     WHERE l2.name = l.name 
                       AND l2."START" = l."START" 
                       AND l2."END" = l."END" 
                       AND l2.days_taken = -l.days_taken) x
 WHERE x.days_taken IS NULL

关于列名。在Oracle SQL中不建议使用保留字，但如果必须使用保留字，请使用'"。

Answer 4

我用Giorgos的答案想出了这个Linq解决方案。该解决方案还考虑了多次取消/申请休假的人。见下面的爱丽丝和埃德加。

样本数据

  int id = 0;
  List<Leave> allLeave = new List<Leave>()
  {
    new Leave() { UniqueLeaveID=id++, Name="Alice", Start=new DateTime(2016,6,1), End=new DateTime(2016,6,3), Taken=-2 },
    new Leave() { UniqueLeaveID=id++,Name="Alice", Start=new DateTime(2016,6,1), End=new DateTime(2016,6,3), Taken=2 },
    new Leave() { UniqueLeaveID=id++, Name="Alice", Start=new DateTime(2016,6,1), End=new DateTime(2016,6,3), Taken=2 },
    new Leave() { UniqueLeaveID=id++,Name="Alice", Start=new DateTime(2016,6,3), End=new DateTime(2016,6,5), Taken=3 },
    new Leave() { UniqueLeaveID=id++,Name="Bob", Start=new DateTime(2016,6,4), End=new DateTime(2016,6,14), Taken=10 },
    new Leave() { UniqueLeaveID=id++,Name="Charles", Start=new DateTime(2016,6,2), End=new DateTime(2016,6,14), Taken=12 },
    new Leave() { UniqueLeaveID=id++,Name="Charles", Start=new DateTime(2016,6,2), End=new DateTime(2016,6,14), Taken=-12 },
    new Leave() { UniqueLeaveID=id++,Name="David", Start=new DateTime(2016,6,3), End=new DateTime(2016,6,8), Taken=5 },
            new Leave() { UniqueLeaveID=id++,Name="Edgar", Start=new DateTime(2016,6,3), End=new DateTime(2016,6,8), Taken=5 },
    new Leave() { UniqueLeaveID=id++,Name="Edgar", Start=new DateTime(2016,6,3), End=new DateTime(2016,6,8), Taken=5 },
    new Leave() { UniqueLeaveID=id++,Name="Edgar", Start=new DateTime(2016,6,3), End=new DateTime(2016,6,8), Taken=5 },
    new Leave() { UniqueLeaveID=id++,Name="Edgar", Start=new DateTime(2016,6,3), End=new DateTime(2016,6,8), Taken=5 }
  };

Linq Query(注意Oracle版本11 vs 12)

 var filteredLeave = allLeave
 .GroupBy(a => new { a.Name, a.Start, a.End })
 .Select(a => new { Group = a.OrderByDescending(b=>b.Taken), Count = a.Count() })
 .Where(a => a.Count % 2 != 0)
 .Select(a => a.Group.First());

" orderbydescent "确保只返回正的天数。

Oracle SQL

SELECT
*
FROM
(
    SELECT 
    L1.NAME, L1.START, L1.END, MAX(TAKEN) AS TAKEN, COUNT(*) AS CNT
    FROM LEAVE L1
    GROUP BY L1.NAME, L1.START, L1.END
) L2
WHERE MOD(L2.CNT,2)<>0 -- replace MOD with % for Microsoft SQL

条件"WHERE MOD(L2.CNT,2)<>0"(或在Linq中"a.Count % 2 != 0")只返回申请过一次或奇数次的人(例如apply - cancel - apply)。但是申请-取消-申请-取消的人会被过滤掉。