欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

求在一组数据内至多包含2个数据的记录个数,谢谢

程序员文章站 2022-06-14 22:59:00
...
求在一组数据内至多包含2个数据的记录个数,多谢
原数据库table存储类似于下面的信息有好多记录
通过select l1,l2,l3,l4,l5 from table 可以得到如下数据

1 2 3 4 5
1 3 8 14 16
16 18 22 24 26
15 14 23 28 30
26 28 30 31 33

现在希望从这5条记录中按如下限定输出,即$arr=array(1,8,14,20,22,24,26,29,30,31,32,33),求在$arr中至多出现2个数字的记录是什么,当然可以选择从上面得出的记录中选,如果能在select的where条件中限定就更好了。先行谢过啦!~~~

------解决方案--------------------
中间的from之后一串union all替换为你的table

SQL code
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 allSELECT 1, 3, 8, 14, 16 union allSELECT 16, 18, 22, 24, 26 union allSELECT 15, 14, 23, 28, 30 union allSELECT 26, 28, 30, 31, 33) t) s WHERE cnt------解决方案--------------------
PHP code
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