Question

下面是QQ.的 o样本——

  BUG_ID   |  LINKED_BUG_ID
-----------|-----------------
3726       |  45236
45236      |  3726
3726       |  45254
45254      |  3726
3726       |  45402
45402      |  3726
3726       |  1182
1182       |  55745

我的 o/p有两行,其中一行是多余的。 e.g <>Bug Id<>>和Linked Bug Id45326和 Bug Id<>>>45326和Linked Bug Id>>>>> /p. 3726 > ,其中我们只需要一排,忽视了这种。 (具有Bug Id栏或Linked Bug Id栏中重复的数值,不影响含有不同价值的o/p。)

目前,我可以通过下列查询确定 /duplicate rows,但我只需要从这种重复增长中排出一个。

SELECT BUG_ID, LINKED_BUG_ID, CASE WHEN BUG_ID IN (select LINKED_BUG_ID FROM MY_BUG_LINKS) AND LINKED_ISSUE_ID IN (SELECT BUG_ID FROM MY_BUG_LINKS) THEN true ELSE false END AS EQUAL FROM MY_BUG_LINKS;

下面是我用在法典中抽取所有浏览量(甚至包括重复浏览)的疑问。

SELECT BUG_ID, LINKED_BUG_ID FROM MY_BUG_LINKS;

我怎么能避免在数据库一级或我的java代码上打上多余的重复浏览?

Answer 1

If this is merely about treating (B, A) as a duplicate of (A, B) and you do not particularly care whether the row returned will be (A, B) or (B, A), you could do something like this:

SELECT DISTINCT
  CASE WHEN BUG_ID > LINKED_BUG_ID THEN LINKED_BUG_ID ELSE BUG_ID AS BUG_ID,
  CASE WHEN BUG_ID > LINKED_BUG_ID THEN BUG_ID ELSE LINKED_BUG_ID AS LINKED_BUG_ID
FROM MY_BUG_LINKS;

如果BUG_ID的数值大于LINKED_BIG_ID,则盘点将两个IDs转换为,否则价值不变。因此,(A,B)和(B,A)总是产生重复浏览(两者均为<代码>(A,B)或(B,A))和,确保最终结果无。

Answer 2

您可以尝试类似的做法:

select 
distinct bug_id from
(
    select bug_id as bug_id from TABLE
 union
    select linked_bug_id as bug_id from TABLE
)

友情链接