如何创建与我的 php 函数执行类似工作的程序(优化加速)
我得到了如下表
mysql> select * from dts;
+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1 | pr2 | pr3 | pr4 | pr5 |
+----+------+------+--------+------+------+------+------+------+
| 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 2 |
| 2 | 1 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
| 3 | 1 | 1 | 3 | 0 | 0 | 0 | 1 | 0 |
| 4 | 1 | 1 | 4 | 1 | 0 | 1 | 1 | 3 |
| 5 | 1 | 2 | 5 | 0 | 0 | 0 | 2 | 5 |
| 6 | 1 | 2 | 6 | 0 | 0 | 0 | 0 | 1 |
| 7 | 1 | 2 | 7 | 0 | 1 | 0 | 0 | 0 |
| 8 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 2 |
| 9 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
| 10 | 3 | 2 | 3 | 0 | 0 | 0 | 0 | 0 |
| 11 | 3 | 3 | 1 | 1 | 1 | 0 | 0 | 1 |
| 12 | 3 | 3 | 5 | 0 | 0 | 1 | 1 | 0 |
+----+------+------+--------+------+------+------+------+------+
12 rows in set (0.00 sec)
我想将 key1、key2、db、table 和逗号分隔的字段列表传递给过程，并且想从这些字段中搜索非零值以获取 select 语句返回的记录，如果假设如果我得到所有非零字段只是中断循环并返回字符串.
I would like to pass key1,key2,db,table and list of fields comma separated to procedure, and would like to search for nonzero values from those fields for the records returned by the select statement, if suppose If I get all fields which are nonzero just break loop and return string back .
我尝试的是下面使用php
What I tried is below using php
function show_available($key1, $key2, $db, $table, $conn, $fields=null)
{
/* Select all fields in argument from db table where key... */
$query = "select ".$fields." FROM $db.$table where key1=$key1 and key2=$key2";
/* Query */
$result = $conn->query($query , MYSQLI_USE_RESULT);
/* Output array */
$out = array();
while($row=$result->fetch_assoc())
{
/* Loop through fields */
foreach($row as $key => $val)
{
/* If val is greater than 0*/
if($val > 0 ){
/*Ok we got field which has value greater than 0*/
$out[$key]=1;
}
}
/* If all fields are found ok in so many records where key1=x and key2=x, break loop */
if(count($out) == count($field_arr))break;
}
/* Return which all fields has value greater than 0 */
return implode(',', array_keys($out));
}
在同一个函数中，我想转换成程序来加快我的任务，并希望有如下输出，怎么可能，请帮助某人
In the same function, I would like to convert into procedure to speedup my task and want to have output like below, how it is possible, please help someone
如果我通过 someprocedure(1,1,db,table,'pr1,pr2,pr3,pr4,pr5') 我想得到输出 pr1,pr3,pr4,pr5 因为当 key1=1 和 key2=1
If I pass someprocedure(1,1,db,table,'pr1,pr2,pr3,pr4,pr5') I would like to get output pr1,pr3,pr4,pr5 because when key1=1 and key2=1
+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1 | pr2 | pr3 | pr4 | pr5 |
+----+------+------+--------+------+------+------+------+------+
| 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 2 | - Found pr3,pr5
| 2 | 1 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
| 3 | 1 | 1 | 3 | 0 | 0 | 0 | 1 | 0 | - Found pr4
| 4 | 1 | 1 | 4 | 1 | 0 | 1 | 1 | 3 | - Found pr1
同样适用于 key1=2 和 key2=2
Similarly for key1=2 and key2=2
| 8 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 2 | - Found pr1-pr5, break loop and return string
| 9 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
预期输出
# For procedure call expected o/p
key1 key2 fields_non_zero
1 1 pr1,pr3,pr4,pr5
1 2 pr2,pr4,pr5
2 2 pr1,pr2,pr3,pr4,pr5
3 2
3 3 pr1,pr2,pr3,pr4,pr5
表转储
DROP TABLE IF EXISTS `dts`;
CREATE TABLE `dts` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`key1` int(11) DEFAULT '-99',
`key2` int(11) DEFAULT '-99',
`serial` int(11) DEFAULT '-99',
`pr1` int(11) DEFAULT '-99',
`pr2` int(11) DEFAULT '-99',
`pr3` int(11) DEFAULT '-99',
`pr4` int(11) DEFAULT '-99',
`pr5` int(11) DEFAULT '-99',
PRIMARY KEY (`Id`),
KEY `main` (`key1`,`key2`,`serial`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
LOCK TABLES `dts` WRITE;
INSERT INTO `dts` VALUES (1,1,1,1,0,0,1,0,2),(2,1,1,2,0,0,0,0,0),(3,1,1,3,0,0,0,1,0),(4,1,1,4,1,0,1,1,3),(5,1,2,5,0,0,0,2,5),(6,1,2,6,0,0,0,0,1),(7,1,2,7,0,1,0,0,0),(8,2,2,1,1,1,1,1,2),(9,2,2,2,0,0,0,0,0),(10,3,2,3,0,0,0,0,0),(11,3,3,1,1,1,0,0,1),(12,3,3,5,0,0,1,1,0);
UNLOCK TABLES;
推荐答案
你需要的是聚合 max (或 sum) 和 concat_ws:
What you need is aggregation with max (or sum) with concat_ws:
select
key1, key2,
concat_ws(
',',
case when max(pr1) <> 0 then 'pr1' end,
case when max(pr2) <> 0 then 'pr2' end,
case when max(pr3) <> 0 then 'pr3' end,
case when max(pr4) <> 0 then 'pr4' end,
case when max(pr5) <> 0 then 'pr5' end
) as val
from dts
group by key1, key2;
生产:
key1 key2 val
1 1 pr1,pr3,pr4,pr5
1 2 pr2,pr4,pr5
2 2 pr1,pr2,pr3,pr4,pr5
3 2
3 3 pr1,pr2,pr3,pr4,pr5
相关文章