применительно к вашей таблице ничего сделать нельзя
а вот в такой
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE `tbl` (
`r` int(11) NOT NULL,
`c` int(11) NOT NULL,
`v` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`r`,`c`),
KEY `idx_tbl_v` (`v`)
)
ваши данные перенес
1.
2.
3.
4.
5.
INSERT INTO `tbl` VALUES (1,1,1),(4,1,1),(1,2,2),(3,1,2),(1,3,3),(4,2,3),
(5,1,3),(6,1,3),(1,4,4),(4,3,4),(5,2,4),(6,2,4),(1,5,5),(3,2,5),(1,6,6),(4,4,6),
(5,3,6),(6,3,6),(2,1,7),(2,2,8),(3,3,8),(2,3,9),(2,4,10),
(3,4,10),(5,4,10),(2,5,11),(2,6,12),(5,5,12),(4,5,14),(6,4,15),(3,5,16),(4,6,16),
(3,6,17),(5,6,18),(6,5,20),(6,6,21);
вуаля
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT COUNT(DISTINCT tt1.r) cnt,
n1,n2,n3
FROM (SELECT DISTINCT tbl.v as n1 FROM tbl) t1
JOIN (SELECT DISTINCT tbl.v as n2 FROM tbl) t2 ON 1
JOIN (SELECT DISTINCT tbl.v as n3 FROM tbl) t3 ON 1
JOIN tbl tt1 ON tt1.v=n1
JOIN tbl tt2 ON tt2.v=n2 AND tt1.r=tt2.r AND tt1.c<tt2.c
JOIN tbl tt3 ON tt3.v=n3 AND tt2.r=tt3.r AND tt2.c<tt3.c
WHERE n1 NOT IN(n2,n3) AND n2 NOT IN(n3)
GROUP BY n1,n2,n3
ORDER BY cnt DESC
|
