在SQL表中删除重复的一个表 [英] Remove duplicates in SQL Result set of ONE table

问题描述

下午/晚上全部，

我正在寻找下面的查询的最终结果。我需要删除特定行中的列的重复出现次数。目前使用以下SQL：

  SELECT CBNEW。* 
 FROM CallbackNewID CBNEW 
 INNER JOIN（SELECT IDNEW ，MAX（CallbackDate）AS MaxDate 
 FROM CallbackNewID 
 GROUP BY IDNEW）AS groupingCBNEW 
 ON（CBNEW.CallbackDate = groupingCBNEW.MaxDate）AND（CBNEW.IDNEW = groupingCBNEW.IDNEW）; 
  

我的结果集看起来像下面

  ID RecID Comp Rem Date_ IDNEW IDOLD CB？ CallbackDate 
 138618 83209 1 0 2012-03-16 12:40:00 83209 83209 2 16-Mar-12 
 138619 83209 1 0 2012-03-16 12:40:00 83209 83209 2 16- Mar-12 
 110470 83799 1 0 2011-07-27 11:46:00 83799 83799 10 27-Jul-11 
 110471 83799 1 0 2011-07-27 11:46:00 83799 83799 10 27-Jul-11 
  

然而，这在CallBackDate和IDNEW列中给出了重复的值，因为在表中有一些不同的主键具有相同的IDNEW和CallbackDate值。

如果我将此结果转储到Excel中，我可以使用第一个ID列上的删除重复项，问题解决了。

但是我想做的是确保我的结果只包含ID列的FIRST实例，其中IDNEW和CallbackDate被复制。

我确信我只需要附加一小段SQL，但是如果我能找到答案，我就被困住了。
$ b

您的帮助非常感谢。

解决方案

这是一个相当强力的方法。它只需要您的原始查询的结果，并在[ID]上 Min（）， Max（） Comp]和[Rem]和 GROUP BY 在其他所有内容：

  SELECT 
最小（t.ID）AS MinOfID，
 t.RecID，
最大（t.Comp）AS MaxOfComp，
最大（t .Rem）AS MaxOfRem，
 t.Date_，
 t.IDNEW，
 t.IDOLD，
 t。[CB？]，
 t.CallbackDate 
 FROM 
（
 SELECT CBNEW。* 
 FROM 
 CallbackNewID CBNEW 
 INNER JOIN 
（
 SELECT IDNEW，MAX（CallbackDate）AS MaxDate 
 FROM CallbackNewID 
 GROUP BY IDNEW 
）AS groupingCBNEW 
 ON（CBNEW.CallbackDate = groupingCBNEW.MaxDate）
 AND（CBNEW.IDNEW = groupingCBNEW.IDNEW）
）t 
 GROUP BY 
 t.RecID，
 t.Date_，
 t.IDNEW，
 t.IDOLD，
 t。[CB？]，
 t.CallbackDate; 
  

它可能不是非常优雅，但如果它工作....

Afternoon/Evening all,

I'm looking for the final touches to the below query. I need to remove the duplicate occurrences of a column in a particular row. Currently using the below SQL:

SELECT CBNEW.*
FROM CallbackNewID CBNEW 
INNER JOIN (SELECT IDNEW, MAX(CallbackDate) AS MaxDate 
FROM CallbackNewID 
GROUP BY IDNEW) AS groupedCBNEW 
ON (CBNEW.CallbackDate = groupedCBNEW.MaxDate) AND (CBNEW.IDNEW = groupedCBNEW.IDNEW);

My result set looks like the below

ID     RecID  Comp  Rem Date_               IDNEW   IDOLD   CB? CallbackDate
138618  83209   1   0   2012-03-16 12:40:00 83209   83209   2   16-Mar-12
138619  83209   1   0   2012-03-16 12:40:00 83209   83209   2   16-Mar-12
110470  83799   1   0   2011-07-27 11:46:00 83799   83799   10  27-Jul-11
110471  83799   1   0   2011-07-27 11:46:00 83799   83799   10  27-Jul-11

This however gives me duplicate values in the CallBackDate and IDNEW Column because in the table there are some different Primary Keys with the same IDNEW and CallbackDate values.

If I dump this result into Excel, I can just use remove duplicates on the first ID column, and the problem's solved.

But what I want to do is make sure my result only includes the FIRST instance of the ID column, where IDNEW and CallbackDate are duplicated.

I'm sure I just need to append a tiny piece of SQL, but I'm stuck if I can find the answer so far.

Your help is very much appreciated.

解决方案

Here is a rather "brute force" approach. It just takes the results of your original query and does Min() on [ID], Max() on [Comp] and [Rem], and GROUP BY on everything else:

SELECT 
    Min(t.ID) AS MinOfID, 
    t.RecID, 
    Max(t.Comp) AS MaxOfComp, 
    Max(t.Rem) AS MaxOfRem, 
    t.Date_, 
    t.IDNEW, 
    t.IDOLD, 
    t.[CB?], 
    t.CallbackDate
FROM 
    (
        SELECT CBNEW.*
        FROM 
            CallbackNewID CBNEW 
            INNER JOIN 
            (
                SELECT IDNEW, MAX(CallbackDate) AS MaxDate 
                FROM CallbackNewID 
                GROUP BY IDNEW
            ) AS groupedCBNEW 
                ON (CBNEW.CallbackDate = groupedCBNEW.MaxDate) 
                AND (CBNEW.IDNEW = groupedCBNEW.IDNEW)
    ) t
GROUP BY 
    t.RecID, 
    t.Date_, 
    t.IDNEW, 
    t.IDOLD, 
    t.[CB?], 
    t.CallbackDate;

It might not be terribly elegant, but if it works....

这篇关于在SQL表中删除重复的一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！