对称对| hackerrank | MSSQL
#教程 #database #mssql #hackerrank

问题

给定一个表格,功能,包含两个列:x和y。

x and y columns table

如果x1 = y2和x2 = y1,则说两对(x1,y1)和(x2,y2)是对称对的。任务是编写一个SQL查询,该查询以x的值输出所有此类对称对,同时确保x1€y1。

解释

考虑以下输入:

x and y sample input table

预期的输出将是:

20 20
20 21
22 23

在这里,(20,20),(20,21),其对称对(21,20)和(22,23)及其对称对(23,22)是表中的对称对,分类按X。

按顺序排列

解决方案

这是解决该问题的两个SQL解决方案,每个解决方案都强调了一种不同的方法及其各自的优势,劣势和结构。

直接加入row_number()

第一种方法使用通用表表达式(CTE)和直接联接:

WITH CTE AS (
    SELECT
        X,
        Y,
        ROW_NUMBER() OVER(ORDER BY X) [rn]
    FROM Functions
)
SELECT DISTINCT
    f1.X,
    f1.Y
FROM CTE f1 JOIN CTE f2 ON f1.X = f2.Y AND f1.Y = f2.X
WHERE f1.X <= f1.Y
    AND f1.rn != f2.rn
ORDER BY f1.X

在此方法中,首先创建一个CTE,并且表中的每一行使用ROW_NUMBER()函数分配了一个唯一的数字。然后,在CTE上执行一个自连接,以匹配X和Y对称的行。 f1.rn != f2.rn条件可确保同一行与自身匹配,这将导致假阳性结果。

使用row_number()存在

第二种方法还使用CTE和ROW_NUMBER()函数,但是它使用EXISTS运算符,而不是直接加入:

WITH CTE AS (
    SELECT
        X,
        Y,
        ROW_NUMBER() OVER(ORDER BY X, Y) [rn]
    FROM Functions
)
SELECT DISTINCT
    f1.X,
    f1.Y
FROM CTE f1
WHERE EXISTS (
    SELECT 1
    FROM CTE f2
    WHERE f1.X = f2.Y AND f1.Y = f2.X AND f1.rn <> f2.rn
)
    AND f1.X <= f1.Y
ORDER BY f1.X

在这里,WHERE子句中使用了一个子查询,以检查CTE中存在对称对的存在。如果子查询返回至少一行,则EXISTS运算符将返回true,在这种情况下,这将表明存在对称对。

结论

这两种方法都有效地解决了问题,但是它们的性能可能会根据数据中数据的大小和分布而变化。通常,如果子查询返回几行,则使用EXISTS的方法可以更快,因为它在找到匹配项后就停止执行。但是,如果子查询通常返回许多行,则直接联接方法可能会更有效。

性能也可能因使用的实际rdbms以及可用的系统资源(包括内存和CPU。

)而有所不同。

您可以在HackerRank中找到原始问题。

有关更有见地的解决方案和与技术相关的内容,请随时在Beacons page上与我联系。

👉 Check out all the links on my beacons.ai page 👈