21,887
社区成员
发帖
与我相关
我的任务
分享
mysql_connect();
mysql_select_db('test');
mysql_query('DROP TABLE IF EXISTS tset');
mysql_query('
CREATE TABLE IF NOT EXISTS test (
id int(11) NOT NULL AUTO_INCREMENT,
l1 varchar(2) NULL,
l2 varchar(2) NULL,
l3 varchar(2) NULL,
l4 varchar(2) NULL,
l5 varchar(2) NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM
');
mysql_query('TRUNCATE TABLE test');
mysql_query("insert into test (l1, l2, l3, l4, l5) values
('1', '2', '3', '4', '5'),
('1', '3', '8', '14', '16'),
('16', '18', '22', '24', '26'),
('15', '16', ' 23', '28', '3'),
('26', '28', '30', '31', '33')
");
$arr = array(1,8,14,20,21,24,26,29,30,31,32,33);
$s = "select '" .join("' as ch union all select '", $arr) . "'";
$sql = "select count(*) as cnt, a.* from
(select id, concat(l1,',',l2,',',l3,',',l4,',',l5) as p from test) a
left join
($s) b
on find_in_set(b.ch, a.p) group by a.id HAVING cnt<3";
$rs = mysql_query($sql) or die(mysql_error());
while($r = mysql_fetch_assoc($rs)) {
echo join(', ', $r), PHP_EOL;
}
1, 1, 1,2,3,4,5
SELECT * FROM(SELECT *,(if(find_in_set(a, '1,8,14,20,22,24,26,29,30,31,32,33'), 1, 0) + if(find_in_set(b, '1,8,14,20,22,24,26,29,30,31,32,33'), 1, 0) + if(find_in_set(c, '1,8,14,20,22,24,26,29,30,31,32,33'), 1, 0) + if(find_in_set(d, '1,8,14,20,22,24,26,29,30,31,32,33'), 1, 0) + if(find_in_set(e, '1,8,14,20,22,24,26,29,30,31,32,33'), 1, 0) ) cnt FROM (SELECT 1 a, 2 b, 3 c, 4 d, 5 e union all
SELECT 1, 3, 8, 14, 16 union all
SELECT 16, 18, 22, 24, 26 union all
SELECT 15, 14, 23, 28, 30 union all
SELECT 26, 28, 30, 31, 33) t) s WHERE cnt<=2