具有相同值的行之间的差异

SELECT a.*, b.*
FROM my_table a
JOIN my_table b
ON a.col1 = b.col1 AND a.col2 = b.col2 AND a.col3 <> b.col3;

SELECT col1, col2, COUNT(DISTINCT col3) AS distinct_col3_count
FROM my_table
GROUP BY col1, col2
HAVING COUNT(DISTINCT col3) > 1;

import pandas as pd

# 假设 df 是你的 DataFrame
df = pd.DataFrame({
    'col1': [1, 1, 2, 2],
    'col2': ['A', 'A', 'B', 'B'],
    'col3': ['X', 'Y', 'X', 'Z']
})

# 找出在 col1 和 col2 上值相同的行，但在 col3 上有差异的行
duplicates = df[df.duplicated(subset=['col1', 'col2'], keep=False)]
diffs = duplicates[duplicates.groupby(['col1', 'col2'])['col3'].transform('nunique') > 1]

print(diffs)

WITH CTE AS (
    SELECT col1, col2, col3,
           ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3) AS rn
    FROM my_table
)
SELECT a.*, b.*
FROM CTE a
JOIN CTE b
ON a.col1 = b.col1 AND a.col2 = b.col2 AND a.col3 <> b.col3 AND a.rn <> b.rn;