忽略从数据库获取的冗余值 [英] Ignore redundant values fetched from database

问题描述

以下是SQL查询的示例o / p -

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

在我的SQL o / p中，有两行，其中一行是多余的。 3726 和已链接错误ID 45326 和错误ID 45326 和已链接错误ID 3726 在o / p中出现了两次，其中我们只需要一行，行（具有在错误ID 列或已链接错误ID 列中重复的值），而不影响包含不同值的o / p。 p>

目前，我可以使用以下查询来识别此类重复行，但我只需要这些重复行中的一行。

  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; 
 

以下是我在我的代码中用于获取所有行的SQL查询行）

  SELECT BUG_ID，LINKED_BUG_ID FROM MY_BUG_LINKS; 
 

如何避免在数据库级别本身或在我的java代码中获取多余的重复行？ p>


解决方案

如果这仅仅是将（B，A） （A，B），你不特别关心返回的行是（A，B）还是（B，A），您可以这样做：

  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 ，则查询会交换这两个ID，否则将不更改地返回值。因此，（A，B）和（B，A）总是产生重复的行c $ c>（A，B）或（B，A））和 DISTINCT 确保在最终结果中没有。

Following is the sample o/p of the SQL query -

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

In my SQL o/p, there are two rows out of which one row is redundant. e.g Bug Id 3726 and Linked Bug Id 45326 and Bug Id 45326 and Linked Bug Id 3726 are present twice in the o/p, out of which we need only one row and ignore such kind of duplicate rows (having either of a value repeated in Bug Id column or Linked Bug Id column), without affecting the o/p containing the distinct value.

Currently I can identify such duplicate rows using following query, but I need only one single row out of such 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;

Following is the SQL query that I use in my code for fetching all the rows (which includes even duplicate rows)

SELECT BUG_ID, LINKED_BUG_ID FROM MY_BUG_LINKS;

How can I avoid fetching redundant duplicate rows at the database level itself or in my java code?

解决方案

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;

That is, if BUG_ID has a greater value than LINKED_BIG_ID, the query swaps the two IDs, otherwise the values are returned unchanged. Therefore, (A, B) and (B, A) always produce duplicate rows (both would be either (A, B) or (B, A)), and DISTINCT makes sure there's none in the final result.

这篇关于忽略从数据库获取的冗余值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！