日期:2014-05-17 浏览次数:20603 次
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
------解决方案--------------------
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; }