删除查询结果中分组行的最后一行 [英] Removing last row of a grouped row in a query result

问题描述

大家好，

我想从具有以下要求的表中返回结果查询.
(a)它必须具有多于1列的记录:EmployeeId
(b)如果它的记录多于1，则具有最低BeginDate记录的记录不应出现在结果查询中.

仅举一个例子；
我的记录:

3

公司	EmployeeID	结束日期
1	01/08/1995	ComA	01/09/2013	31/03/2014
1	> 01/01/2100
1	01/09/2008	17/02/2009
1	> 2014年3月3日
ComC	1	2014年3月19日	01/01/2100
ComA	2	14/01/2006	24/06/2009
ComA	2	2009年6月25日	01/01/2100
ComB	2	30/11/2010	2011/12/13
ComB	2	2011年12月14日	01/01/2100
ComC	2	25/07/2001	01/05/2006
ComC	3	2010年7月7日	11/12/2012
ComC	2012/12/12
公司	EmployeeID	结束日期
1	01/09/2013	2014年3月31日
ComA	1	01/04/2014	01/01/2100
ComC	1	19/03/2014	01/01/2100
ComA	2	25/06/2009	01/01/2100
ComB	2	2011/12/12	01/01/2100
ComC	2012/12/12	01/01/2100

我已经通过以下查询满足了条件(a)，但是我陷入了条件(b)中
有人可以帮我先谢谢.

选择P1.*
来自员工P1
内联接
(选择EmployeeID
来自员工
GROUP BY EmployeeID
拥有COUNT(EmployeeID)> 1
ORDER BY EmployeeID)P2
开启P1.EmployeeID = P2.EmployeeID
ORDER BY P1.EmployeeID，P1.BeginDate DESC;

Hello all,

I want to return a resulting query from a table with the following requirements.
(a) It has to have records more than 1 of the column : EmployeeId
(b) If it has records more than 1 then the record with the lowest BeginDate record shouldn''t be on the resulting query.

Just to give you an example;
My Records:

Company	EmployeeID	StartDate	EndDate
ComA	1	01/08/1995	31/08/2013
ComA	1	01/09/2013	31/03/2014
ComA	1	01/04/2014	01/01/2100
ComB	1	01/09/2008	17/02/2009
ComC	1	22/05/1995	18/03/2014
ComC	1	19/03/2014	01/01/2100
ComA	2	14/01/2006	24/06/2009
ComA	2	25/06/2009	01/01/2100
ComB	2	30/11/2010	13/12/2011
ComB	2	14/12/2011	01/01/2100
ComC	2	25/07/2001	01/05/2006
ComC	3	07/07/2010	11/12/2012
ComC	3	12/12/2012	01/01/2100

The final should be

Company	EmployeeID	StartDate	EndDate
ComA	1	01/09/2013	31/03/2014
ComA	1	01/04/2014	01/01/2100
ComC	1	19/03/2014	01/01/2100
ComA	2	25/06/2009	01/01/2100
ComB	2	14/12/2011	01/01/2100
ComC	3	12/12/2012	01/01/2100

I have accomplished the requirement (a) by the following query but I am stuck at requirement (b)
Can someone help me thanks in advance.

SELECT P1.*
FROM Employee P1
INNER JOIN
(SELECT EmployeeID
FROM Employee
GROUP BY EmployeeID
HAVING COUNT(EmployeeID) > 1
ORDER BY EmployeeID) P2
ON P1.EmployeeID = P2.EmployeeID
ORDER BY P1.EmployeeID, P1.BeginDate DESC;

推荐答案

基本上，每当SQL查询启动新组时，它都应该开始编号到该组的每一行，即EmployeeID并使用ROW_NUMBER函数来分配数字.有关更多文档，请检查: ROW_NUMBER [

Basically whenever SQL query starts new group it should start numbering to each row of that group which is EmployeeID and use the ROW_NUMBER function to assign the numbers. For more documentation check: ROW_NUMBER[^]

SELECT T.Company, T.EmployeeID, T.StartDate, T.EndDate, T.GROUPEDROWNUM
FROM
(
  SELECT P1.Company, P1.EmployeeID, P1.StartDate, P1.EndDate, ROW_NUMBER() OVER (PARTITION BY P1.EmployeeID ORDER BY P1.StartDate) AS GROUPEDROWNUM
  FROM Employee P1
  INNER JOIN 
  (SELECT EmployeeID
   FROM Employee
   GROUP BY EmployeeID
   HAVING COUNT(EmployeeID) > 1   
  ) P2
  ON P1.EmployeeID = P2.EmployeeID  
) T
WHERE T.GROUPEDROWNUM > 1
ORDER BY T.Company, T.EmployeeID, T.StartDate ASC

祝你好运，
OI

Good luck,
OI

这可能不是最漂亮的代码，并且可能需要进行一些优化.我使用SELECT *是因为您这样做了，但是我主张使用字段列表来避免模式数据库查询在运行主查询之前由DBMS运行:

SELECT P3.* FROM(SELECT P1.*，MIN(StartDate)MinDate
来自员工P1
内联接
(选择EmployeeID
来自员工
GROUP BY EmployeeID
拥有COUNT(EmployeeID)> 1
ORDER BY EmployeeID)P2
开启P1.EmployeeID = P2.EmployeeID)P3
MinDate<> StartDate
ORDER BY P3.EmployeeID，P3.BeginDate DESC;

This may not be the prettiest code and there may be some optimizations that you could do. I used SELECT * because you did, but I''d advocate using field lists to avoid schema queries being run by the DBMS before running the main query:

SELECT P3.* FROM( SELECT P1.*, MIN(StartDate) MinDate
FROM Employee P1
INNER JOIN
(SELECT EmployeeID
FROM Employee
GROUP BY EmployeeID
HAVING COUNT(EmployeeID) > 1
ORDER BY EmployeeID) P2
ON P1.EmployeeID = P2.EmployeeID) P3
WHERE MinDate <> StartDate
ORDER BY P3.EmployeeID, P3.BeginDate DESC;

这篇关于删除查询结果中分组行的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！