重复项删除

发布于 2024-12-04 17:37:21 字数 771 浏览 2 评论 0原文

可能的重复：
从没有主表的 SQL 表中删除重复记录关键

我有数据：

SELECT
          a
        , b
    FROM 
    (
        select a = 1, b = 30
        union all 
        select a = 2, b = 50
        union all 
        select a = 3, b = 50
        union all 
        select a = 4, b = 50
        union all 
        select a = 5, b = 60
    ) t

我必须获取输出（接下来（按 a 排序）重复记录应从结果集中排除）：

a           b
----------- -----------
1           30
2           50
3           50  -- should be excluded
4           50  -- should be excluded
5           60

原文

Possible Duplicate:
Delete duplicate records from a SQL table without a primary key

I have data:

SELECT
          a
        , b
    FROM 
    (
        select a = 1, b = 30
        union all 
        select a = 2, b = 50
        union all 
        select a = 3, b = 50
        union all 
        select a = 4, b = 50
        union all 
        select a = 5, b = 60
    ) t

I have to get output (next (order by a) dublicate records should be excluded from result set):

a           b
----------- -----------
1           30
2           50
3           50  -- should be excluded
4           50  -- should be excluded
5           60

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淡水深流 2024-12-11 17:37:21

SELECT
          min(a) as a
        , b
    FROM 
    (
        select a = 1, b = 30
        union all 
        select a = 2, b = 50
        union all 
        select a = 3, b = 50
        union all 
        select a = 4, b = 50
        union all 
        select a = 5, b = 60
    ) t
GROUP BY b    
ORDER BY a

SELECT
          min(a) as a
        , b
    FROM 
    (
        select a = 1, b = 30
        union all 
        select a = 2, b = 50
        union all 
        select a = 3, b = 50
        union all 
        select a = 4, b = 50
        union all 
        select a = 5, b = 60
    ) t
GROUP BY b    
ORDER BY a

回复收藏 0 原文

戏舞 2024-12-11 17:37:21

在Oracle中，我能够使用group by子句来做到这一点，你应该能够做类似的事情。

select min(a), b
 from (select 1 a, 30 b
        from dual
      union all
      select 2 a, 50 b
        from dual
      union all
      select 3 a, 50 b
        from dual
      union all
      select 4 a, 50 b
        from dual
      union all
      select 5 a, 60 b from dual)
group by b;

编辑：看起来像其他人提出了 MS sql 解决方案，不过我将把它留在这里供后代使用。

In oracle I was able to do this using a group by clause, you should be able to do similar.

select min(a), b
 from (select 1 a, 30 b
        from dual
      union all
      select 2 a, 50 b
        from dual
      union all
      select 3 a, 50 b
        from dual
      union all
      select 4 a, 50 b
        from dual
      union all
      select 5 a, 60 b from dual)
group by b;

edit: looks like someone else came up with a MS sql solution, I'll leave this here for posterity though.

回复收藏 0 原文

枉心 2024-12-11 17:37:21

最简单的方法是使用简单的 GROUP BY：

SELECT
      a
    , b
INTO #tmp
FROM

(
    select a = 1, b = 30
    union all 
    select a = 2, b = 50
    union all 
    select a = 3, b = 50
    union all 
    select a = 4, b = 50
    union all 
    select a = 5, b = 60
) t


SELECT DISTINCT MIN(a) AS a,b
FROM #tmp
GROUP BY b
ORDER BY a

The easiest way to do this is with a simple GROUP BY:

SELECT
      a
    , b
INTO #tmp
FROM

(
    select a = 1, b = 30
    union all 
    select a = 2, b = 50
    union all 
    select a = 3, b = 50
    union all 
    select a = 4, b = 50
    union all 
    select a = 5, b = 60
) t


SELECT DISTINCT MIN(a) AS a,b
FROM #tmp
GROUP BY b
ORDER BY a

回复收藏 0 原文

~没有更多了~