PHP MySQL 统计具有特定值的 SELECT COUNT 记录并排序

Question

I have two tables: departments (20 departments) and tickets (lets say 1000 tickets). Each ticket is assigned to one department. I wanted to know how many tickets are assigned to each department.

[已解决]感谢frz3993的善意提示

for the sake of completing the thread with the working result, at bottom you find my new script

我通过这两个查询成功实现了这一点。

前者加载部门。

对于后者，我使用 SELECT COUNT 来确定当前部门的票数。

PHP

<?php
$mysqli = new mysqli("localhost", "root", "*****", "tickets");
$openticket = null;

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

$query = "SELECT id, name FROM dept ORDER BY id ASC"; // loads all the departmentes and their id

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

while ($row = $result->fetch_assoc()) {
    //echo $row["id"] . " " . $row["name"] . "<br>"; // test point 
    $sqlcounttickets = "SELECT COUNT(dept_id) FROM ticket WHERE (dept_id=" . $row["id"] . " AND status!=1)"; // count how manytickets for that department id , IF status 1, skip, since ticket is closed
    //echo $sqlcounttickets; // test point

    $result2 = $mysqli->query($sqlcounttickets); //execute second query and get the result of the SELECT COUNT

    //if ($mysqli->error) { //test point
    //    die($mysqli->error);
    //} // no errors

    $rowdue = $result2->fetch_row();
    if ($rowdue[0] > 0){
        echo "DeptLabelNum: " . $row["id"] . " - DeptName: " . $row["name"] . " " . $rowdue[0] ."<br>";
    }
    $openticket=$openticket+$rowdue[0];
}

/* free result set */
$result->free();
}
echo "<br>" . "Open Tickets: " . $openticket;

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

输出显然未排序，因为部门的门票金额是随机的

DeptLabelNum: 0 - DeptName: Global (All Departments) 1
DeptLabelNum: 1 - DeptName: LCD 1
DeptLabelNum: 2 - DeptName: Smartphones 6
DeptLabelNum: 4 - DeptName: Pendrive 4
DeptLabelNum: 6 - DeptName: Plasma 7
DeptLabelNum: 22 - DeptName: HDD 1
DeptLabelNum: 23 - DeptName: Notebook 8
DeptLabelNum: 24 - DeptName: Tablet 12


Open Tickets: 40

你可以打赌:-)，我想按降序对输出进行排序

所以平板电脑应该是第一个有 12 张票的第二个笔记本，有 8 张票第三等 ionic 体

等等

您建议将循环的输出加载到 MySQL 临时表中吗？

或者你会使用 PHP 数组吗？

或者可以通过更有效的查询来完成？

感谢您的帮助和建议，因为我对这三个中的任何一个感到困惑

R。

P.S. SOLUTION - the new script with one query only this is the new script which encloses in an html table the result.

PHP

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

echo '<table>'."\xA";

$query = "
SELECT COUNT( ticket.id ) AS ticket_count, dept.id, dept.name
FROM ticket
LEFT JOIN dept ON ticket.dept_id = dept.id
WHERE ticket.status !=1
GROUP BY dept.id
ORDER BY ticket_count DESC";

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

    while ($row = $result->fetch_assoc()) {

        echo "\t" . "<tr><th>" . $row["name"] . "</th><th>" . $row["ticket_count"] . "</th></tr>". "\xA";

        $openticket=$openticket+$row["ticket_count"];
    }

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

echo "\t" . "<tr><th></th><th></th></tr>". "\xA";

echo "\t" . "<tr><th>" . "Open Tickets: " . "</th><th>" . $openticket . "</th></tr>". "\xA";
echo "</table>". "\xA";

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

Answer 1

您可以通过一个查询来完成。另外，为了确保您获得所有部门的列表，即使没有门票:

SELECT d.*,tmp.ticket_count FROM departments d
LEFT JOIN (SELECT count(*) AS ticket_count, department_id FROM tickets GROUP by department_id) tmp
ON d.id = tmp.department_id