SQL:需要删除查询中的重复行 [英] SQL: Need to remove duplicate rows in query

问题描述

在使用SQL查询或过程获取此结果时，我需要帮助.我的示例表结构如下所示.

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

必填结果:

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

推荐答案

除了您提供的业务规则以外，其他都不知道您的所有业务规则，这是一个更通用的解决方案.

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)
);

此表用于按优先级对状态进行排名.换句话说，如果在重复的情况下应将请求的豁免"选择为拒绝"，则对于豁免使用优先级1，对于拒绝使用优先级2.

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.

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