How to find data between two columns with Sql?-CodePudding

I'm just learning Sql. I have a data with column names, for example: nkk, name, tps. How to find same 'nkk' data but different 'tps'

CodePudding user response：

Use a self-join whose joining condition is exactly what you described: nkk is equal, but tps is not equal.

SELECT t1.nkk, t1.name AS name1, t1.tps AS tps1, t2.name AS name2, t2.tps AS tps2
FROM your_table AS t1
JOIN your_table AS t2 ON t1.nkk = t2.nkk AND t1.tps < t2.tps

I use < rather than != so we only get one of each pair. Otherwise you'll get both Aa Ac and Ac Aa.

CodePudding user response：

Try this query,


    SELECT nkk, count(DISTINCT tps) AS c 
        FROM `tblname` 
        GROUP BY nkk 
        HAVING c > 1 
        ORDER BY c DESC