如何在sql中获得以下输出。请帮我写一个查询来获得预期的输出 [英] How do I get the below output in sql. Please help me write a query to get the expected output

问题描述

x y

20 20

20 20

21 22

22 21

23 24

24 25

25 24



i上面有2列.i需要从2列过滤出类似的数据集并显示为一组。简要说明我的输出应该如下：



x y

20 20

21 22

24 25



基本上我想要类似设置的记录，例如21,22和22,21被认为是相似的集合，因此我想只显示一个记录为21,22。而23,24在表格中没有类似的集合（24,23）所以我想要消除该记录。 br $> b $ b

我尝试过的事情：



i尝试过团队，但它不是'工作

解决方案

此查询删除重复项，如果您想要多个相同的对，它将无效。

  WITH  r  as  （
  SELECT  X 
，Y 
，ROW_NUMBER（） OVER  （ ORDER   BY  X，Y）rn 
  FROM  t 
）
  SELECT   DISTINCT  x，y 
  FROM  r t1 
  WHERE   EXISTS （
  SELECT  x，y 
  FROM  r t2 
  WHERE  t1.x = t2.y 
  AND  t1.y = t2.x 
  AND  t1.rn< t2.rn）

SQL小提琴 [ ^ ]

从yourtable table1中选择table1.id，table1.x，table1.y 
 
 where exists（选择x，y 
来自yourtable table2 
 where table1.x = table2.y and table1.y = table2.x）
 Union 
从yourtable t1 $ b $中选择t1.x，t1.y 
 b存在（从yourtable t2 
中选择x，y 
，其中t1.x = t2.y和t1.x = t2.y）
和t1.x< t1.y 

请不要直接复制粘贴代码。我相信有一些语法问题。首先解决这个问题，然后主逻辑将与和或运营商。

我已经在sql fiddle中联合后测试了第二个查询的这个解决方案。请投票确实有助于社区。



SQL Fiddle [ ^ ]

x y
20 20
20 20
21 22
22 21
23 24
24 25
25 24

i have a above table with 2 columns.i need to filter out similar set of data from 2 columns and display as one set.in brief my output should be as below :

x y
20 20
21 22
24 25

basically i want records with similar set e.g 21,22 and 22,21 are considered as similar set and hence i want to display only one record as 21,22.Whereas 23,24 doesn't have similar set(24,23) in the table so i want to eliminate that record.

What I have tried:

i tried group by but it isn't working

解决方案

This query removes duplicates, if you want multiple identical pairs it won't work.

WITH r as (
    SELECT  X
           ,Y
           ,ROW_NUMBER() OVER (ORDER BY X,Y) rn
    FROM    t
    )
SELECT  DISTINCT x,y
FROM    r t1
WHERE EXISTS (
    SELECT  x, y
    FROM    r t2
    WHERE   t1.x=t2.y
        AND t1.y=t2.x
        AND t1.rn<t2.rn)

SQL Fiddle[^]

select table1.id, table1.x, table1.y
from yourtable table1
where exists (select x, y
from yourtable table2
where table1.x=table2.y and table1.y=table2.x)
Union
select t1.x, t1.y
from yourtable t1
where exists (select x, y
from yourtable t2
where t1.x=t2.y and t1.x=t2.y)
and t1.x<t1.y

Please don't copy paste the code directly.I believe there is some syntactical issue.First resolve that and then the main logic will be with"and" "or" operators.
I have tested this solution for the second query after union in sql fiddle.Please vote it will indeed help the community.

SQL Fiddle[^]

这篇关于如何在sql中获得以下输出。请帮我写一个查询来获得预期的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！