php - 在mysql中返回单列总和等于“16”的行

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

例如

只有一张桌子

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

最佳答案

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

/* ===== 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

关于php - 在mysql中返回单列总和等于“16”的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44127469/