Mysql查询查找多列中常见的记录

Question

Table

   | a  | b  | C  | d  | e  |
   |----+----+----+----+----|
   | 1  | 2  | 3  | 2  | 9  |
   | 2  | 3  | 2  | 3  | 5  |
   | 3  | 8  | 4  | 4  | 2  |
   | 4  | 9  | 8  | 5  | 3  |
   | 5  | 1  | 9  | 7  | 1  |
   | 6  | 12 | 10 | 9  | 10 |

I want in result:

2,3

Because 2,3 are common in all columns.

Answer 1

SELECT DISTINCT c1.a FROM  `in_all_cols` c1 
JOIN  `in_all_cols` c2 ON ( c1.a = c2.b )  
JOIN  `in_all_cols` c3 ON ( c2.b = c3.c )  
JOIN  `in_all_cols` c4 ON ( c3.c = c4.d )  
JOIN  `in_all_cols` c5 ON ( c4.d = c5.e )
LIMIT 0 , 30

Worked for me with this layout:

CREATE TABLE IF NOT EXISTS `in_all_cols` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  `e` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `in_all_cols` (`a`, `b`, `c`, `d`, `e`) VALUES
(1, 2, 3, 2, 9),
(2, 3, 2, 3, 5),
(3, 8, 4, 4, 2),
(4, 9, 8, 5, 3),
(5, 1, 9, 7, 1),
(6, 12, 10, 9, 10);