如何创建与我的 php 函数执行类似工作的程序(优化加速) [英] How do I create procedure which does similar job of my php function (Optimization speedup)

问题描述

我得到了如下表

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

这篇关于如何创建与我的 php 函数执行类似工作的程序(优化加速)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！