SQL：需要删除查询中的重复行

发布于 2024-12-15 13:00:47 字数 390 浏览 2 评论 0原文

我需要帮助来通过使用 SQL 查询或过程来获取此结果。下面给出了我的示例表结构。

所需结果：

docid    status
24       Waiver Requested
26       Waiver Requested
27       Rejected

表 A：

docid
----------
24
26
27

表 b：

docid     Status
24     Waiver Requested
26     Rejected
26     Waiver Requested
27     Rejected
27     Rejected

原文

I need help in getting this result either by using SQL query or procedure. My sample table structure is given below.

Required Result :

docid    status
24       Waiver Requested
26       Waiver Requested
27       Rejected

Table A:

docid
----------
24
26
27

Table b:

docid     Status
24     Waiver Requested
26     Rejected
26     Waiver Requested
27     Rejected
27     Rejected

分享到QQ

分享到微博

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

发布评论

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

忆梦 2024-12-22 13:00:47

除了您提供的规则之外，不知道您的所有业务规则，这里有一个更通用的解决方案。

创建另一个表（或者您的数据模型可能已经有一个表），其中包含可能的状态：

CREATE TABLE status_rank (
   status   VARCHAR2(100) NOT NULL,
   rank     NUMBER NOT NULL,
   PRIMARY KEY (status_name)
);

该表用于按优先顺序对状态进行排名。换句话说，如果在重复的情况下应选择“请求放弃”而不是“拒绝”，则放弃的优先级为 1，拒绝的优先级为 2。

因此，现在查询可以利用这个附加的排名表：

SELECT b.docid, r.status
  FROM b,
       status_rank r,
       (SELECT b.id, min(r.rank)
          FROM b, status_rank r
         WHERE b.status = r.status
         GROUP BY id) s
 WHERE b.docid = s.docid
   AND r.rank = s.rank
   AND b.status = r.status;

实际执行查询的方法有很多种，但这应该向您展示总体思路。

Not knowing all of your business rules other than the one you gave, here is a more general solution.

Create another table (or perhaps your data model already has one) with the possible statuses in them:

CREATE TABLE status_rank (
   status   VARCHAR2(100) NOT NULL,
   rank     NUMBER NOT NULL,
   PRIMARY KEY (status_name)
);

This table is used to rank the statuses in order of precedence. In other words, if "Waiver Requested" should be selected over "Rejected" in the case of duplicates, then use a precedence of 1 for waivers and 2 for rejects.

So now the query can make use of this additional ranking table:

SELECT b.docid, r.status
  FROM b,
       status_rank r,
       (SELECT b.id, min(r.rank)
          FROM b, status_rank r
         WHERE b.status = r.status
         GROUP BY id) s
 WHERE b.docid = s.docid
   AND r.rank = s.rank
   AND b.status = r.status;

There are numerous ways to actually do the query, but this should show you the general idea.

回复收藏 0 原文