MySQL中三个相同的,在mysql中找到三个以上具有相等值的列

I have multiple columns have equal value. I want to find the row id which have more than 3 columns have equal value.

@each column is text/blob

Table structure is like -

id col1 col2 col3 col4 col5 col6 col7 col8 col9

解决方案

Unpivot the data and check for equality, Try this trick.

SELECT DISTINCT id

FROM (SELECT id,col1 AS col from Yourtable

UNION ALL

SELECT id,col2 from Yourtable

UNION ALL

SELECT id,col3 from Yourtable

UNION ALL

SELECT id,col4 from Yourtable

Union ALL

.......) A

GROUP BY id,col

HAVING Count(1) > 3