sql - 用 SQL 匹配不替换一到二

Question

Name        Interest
----------------------
Carol       Books
Carol       Dancing
Carol       Sports  
Carol       Movies   
Carol       Music    
Sue         Books
Sue         Dancing
Sue         Sports    
Sue         Music 
Sue        Painting    
SAM         Books
SAM         Dancing
SAM         Sports    
SAM         Music 
SAM        Painting    

我有一个非常大的数据集。我想为每个名字找到两个不同的兴趣。但是两个人不应该有相同的兴趣。

我希望结果表看起来像这样

Name        Interest1   Interest 2  
-----------------------------------
Carol       Books       Sports
Sue         Music       Painting        
SAM         Dancing      NAN

标签： sql

Answer 1

我们可以使用行号来处理这个要求：

WITH names AS (
    SELECT DISTINCT Name, ROW_NUMBER() OVER (ORDER BY Name) rn
    FROM (SELECT DISTINCT Name FROM yourTable) t
),
interests AS (
    SELECT Interest,
        ROW_NUMBER() OVER (ORDER BY Interest) rn1,
        FLOOR((ROW_NUMBER() OVER (ORDER BY Interest) + 1) / 2) rn2
    FROM (SELECT DISTINCT Interest FROM yourTable) t
)

SELECT
    n.Name,
    MAX(CASE WHEN i.rn1 % 2 = 1 THEN i.Interest END) AS Interest1,
    MAX(CASE WHEN i.rn1 % 2 = 0 THEN i.Interest END) AS Interest2
FROM names n
LEFT JOIN interests i
    ON n.rn = rn2
GROUP BY
    n.Name;

演示

注意：我使用的语法和演示是针对 SQL Server 的，但是上面应该可以在任何支持的 SQL 数据库上运行ROW_NUMBER，只需稍加修改。