php - 返回mysql中单列之和等于 "16"的行

Question

我的项目是一个试卷生成器，每个问号可能因问题类型而异，我需要根据标记字段过滤掉一些随机问题

例如

只有一张 table

Questions | Mark
q1        | 4
q2        | 4
q3        | 8
q4        | 6
q5        | 12
q6        | 2

我想选择 SUM(mark) 等于 16 时的行

结果可能如下所示

q1   | 4
q2   | 4
q4   | 6
q5   | 2

当我们对这个结果求和时，我们将得到 16，这样我需要一个查询

我的问题是

Select question, mark 
from table 
where sum(mark) = 16

Answer 1

努力将由计算机完成，但如果您有大量数据，请同时喝杯咖啡。

/* ===== TAKEN FROM PHP.net ====== */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$query = "Select question, mark from table"; // your query

$arrayData = array();//set an empty array to populate

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {

        //for calculation purpose, we force the Mark field to be an integer
        $arrayData[$row['question']] = (int)$row['mark']; //populate array with sql results
    }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();

现在我们在 $arrayData 变量中有了 SQL 结果。像这样:

Array("q1"=>4, "q2"=>4, "q3"=>8)

等等……

我们走吧

 $targetSUM = 16;
//First of all, remove any doubt and see 
//if the sum of the entire array is the same as our target

if($targetSUM === array_sum($arrayData)){
    print_r($arrayData);
    die();
}
$i=0;
$results = array();
foreach($arrayData as $questionA=>$markA){

    $thisCycle = array($questionA=>$markA);
    foreach($arrayData as $questionB=>$markB){
        if ($questionA === $questionB) continue; // avoid sum SUM itself

        $thisCycle[$questionB] = $markB;

        $thisCycleSUM = array_sum($thisCycle);

        switch(true){
            case $thisCycleSUM < $targetSUM: //if results not reach our target, go on
                continue;
            break;
            case $thisCycleSUM === $targetSUM: //if results match, store and go on
               $results[$i] = $thisCycle;
               $thisCycle = array($questionA=>$markA);
            break;
            case $thisCycleSUM > $targetSUM: //if results exceeds, remove last element and go on
                unset($thisCycle[$questionB]);
            break;
        }

    }


        // Sorting procedure to avoid same combinations and no-combination
    if(isset($results[$i])){ 
        ksort($results[$i]); 
        $results[implode(array_keys($results[$i]))] = $results[$i];
        unset($results[$i]);
        $i++;
    }

}
    $results = array_values($results);
   print_r($results);

DEMO HERE