求在一组数据内至多包含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
原数据库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