Calculate the number of records that contain up to two data records in a group of data. thanks to the table store in the original database, similar to the following information, there are many records through selectl1, l2, l3, l4, l5fromtable can get the following data: 12345 & nbsp; 1381416161822242615142328302628303133 now, we want to output the data from these five records according to the following limits, and calculate the number of records containing up to two data records in a group. thank you!
The original database table store has many records similar to the following information.
The following data can be obtained through select l1, l2, l3, l4, l5 from table:
Now we want to limit the output from these five records, namely, $ arr = array ), find out what is the record with at most two numbers in $ arr. of course, you can select from the above record. it would be better if you can limit it in the where condition of select. Thank you !~~~
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, 20,22, 24,26, 29,30, 31,32, 33'), 1, 0) + if (find_in_set (c, '1, 20,22, 24,26, 29,30, 31,32, 33'), 1, 0) + if (find_in_set (d, '1, 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, 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 <= 2
------ Solution --------------------
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<3";$rs = mysql_query($sql) or die(mysql_error());while($r = mysql_fetch_assoc($rs)) { echo join(', ', $r), PHP_EOL;}