如何从表中删除除日期列的最小值和最大值之外的所有行

I have a table Visitors with the following columns

我有一个表格以下列的访客

ID, Visitors, RegDate (date & time)

ID,访客,RegDate(日期和时间)

Rows: There are 4, 6, 8, 20, 11, 31, 43 Visitors with the same date (July 18) but not the same time.

行:有4个,6个,8个,20个,11个,31个,43个访问者具有相同的日期(7月18日),但不是同一时间。

Another Rows: There are 1, 5, 10, 4, 13, 15, 18, 11, 23 Visitors with the same date (July 19) but not the same time.

另一行:有1个,5个,10个,4个,13个,15个,18个,11个,23个访问者具有相同的日期(7月19日),但不是同一时间。

Something like this

像这样的东西

Id  RegDate
4   2015-07-18 11:11:00.000
6   2015-07-18 11:11:01.000
8   2015-07-18 11:11:02.000
20  2015-07-18 11:11:03.000
11  2015-07-18 11:11:04.000
31  2015-07-18 11:11:05.000
43  2015-07-18 11:11:06.000
1   2015-07-19 11:11:00.000
10  2015-07-19 11:11:01.000
4   2015-07-19 11:11:02.000
13  2015-07-19 11:11:03.000
15  2015-07-19 11:11:04.000
18  2015-07-19 11:11:05.000
11  2015-07-19 11:11:06.000
23  2015-07-19 11:11:07.000

Here is my Query that shows the Minimum and Maximum including the date.

这是我的查询,显示最小值和最大值,包括日期。

SELECT MIN(Visitors), MAX(Visitors), cast(RegDate as date) AS DATE
FROM Visitor GROUP BY cast(RegDate as date)
ORDER BY DATE

I want to delete all rows from the column Visitors except its Minimum and Maximum values by RegDate.

我希望删除“访问者”列中的所有行,除了RegDate的“最小值”和“最大值”。

2 个解决方案

#1

You can use ROW_NUMBER to find the Visitor with the earliest and latest RegDate:

您可以使用ROW_NUMBER查找具有最早和最新RegDate的访问者:

WITH Cte AS(
    SELECT *,
        RN_ASC = ROW_NUMBER() OVER(PARTITION BY CAST(RegDate AS DATE) ORDER BY RegDate ASC),
        RN_DESC = ROW_NUMBER() OVER(PARTITION BY CAST(RegDate AS DATE) ORDER BY RegDate DESC)
    FROM Visitor
)
DELETE FROM Cte WHERE RN_ASC > 1 AND RN_DESC > 1

SQLFiddle

#2

The following works for your data. It uses your GROUP BY query as a sub-query to a DELETE:

以下适用于您的数据。它使用您的GROUP BY查询作为DELETE的子查询:

DELETE V FROM  
@Visitor AS v
JOIN 
(
  SELECT
    RegDate,
    MIN(Visitors) MinVisitors,
    MAX(Visitors) MaxVisitors
  FROM
    @Visitor AS V
  GROUP BY
    RegDate
) T  ON 
  v.RegDate = T.RegDate 
  AND V.Visitors <> T.MinVisitors 
  AND V.Visitors <> T.MaxVisitors

#1