如何用含UNION、SUM、count(*)的PHP SQL单查询实现多表楼层统计？

Here's a single SQL query that pulls all the required metrics across your three tables, along with PHP code to implement it:

The SQL Query #

SELECT
    COALESCE(c.floor, a.floor, i.floor) AS Floor,
    COUNT(DISTINCT a.assigned_user) AS `Head Count`,
    COUNT(c.workstation_number) AS `Workstations`,
    COUNT(a.machine_type) AS `Machines Deployed`,
    COUNT(DISTINCT CASE WHEN c.workstation_name = 'Production' THEN a.assigned_user END) AS `Production Head Count`,
    COUNT(DISTINCT CASE WHEN c.workstation_name = 'Non-Production' THEN a.assigned_user END) AS `Non-Production Head Count`
FROM
    -- Get all unique floors across all three tables to avoid missing any
    (SELECT floor FROM config_location_workstation UNION DISTINCT
     SELECT floor FROM asset_workstation UNION DISTINCT
     SELECT floor FROM asset_inventory) AS all_floors
LEFT JOIN config_location_workstation c ON all_floors.floor = c.floor
LEFT JOIN asset_workstation a ON all_floors.floor = a.floor
GROUP BY all_floors.floor
ORDER BY all_floors.floor;

Breakdown of Each Metric #

Let's walk through how each stat is calculated:

• Floor: Uses COALESCE to grab the floor value from whichever table has it, ensuring we never get a NULL floor entry.
• Head Count: Counts unique assigned_user values from asset_workstation to avoid double-counting users who might have multiple devices.
• Workstations: Counts total entries from config_location_workstation since this table defines all officially configured workstations.
• Machines Deployed: Counts total entries from asset_workstation—each row represents one deployed device (laptop/desktop).
• Production/Non-Production Head Count: Uses conditional CASE statements to filter users by their workstation type, then counts unique users for each category.

PHP Implementation #

Here's how to integrate this query into your PHP code (similar to your existing implementation):

// Assume $conn is your established MySQL connection
$query = mysqli_query($conn, "
    SELECT
        COALESCE(c.floor, a.floor, i.floor) AS Floor,
        COUNT(DISTINCT a.assigned_user) AS `Head Count`,
        COUNT(c.workstation_number) AS `Workstations`,
        COUNT(a.machine_type) AS `Machines Deployed`,
        COUNT(DISTINCT CASE WHEN c.workstation_name = 'Production' THEN a.assigned_user END) AS `Production Head Count`,
        COUNT(DISTINCT CASE WHEN c.workstation_name = 'Non-Production' THEN a.assigned_user END) AS `Non-Production Head Count`
    FROM
        (SELECT floor FROM config_location_workstation UNION DISTINCT
         SELECT floor FROM asset_workstation UNION DISTINCT
         SELECT floor FROM asset_inventory) AS all_floors
    LEFT JOIN config_location_workstation c ON all_floors.floor = c.floor
    LEFT JOIN asset_workstation a ON all_floors.floor = a.floor
    GROUP BY all_floors.floor
    ORDER BY all_floors.floor;
");

if ($query) {
    // Render the results in a table matching your desired format
    echo "<table border='1' cellpadding='8'>";
    echo "<tr>
            <th>Floor</th>
            <th>Head Count</th>
            <th>Workstations</th>
            <th>Machines Deployed</th>
            <th>Production Head Count</th>
            <th>Non-Production Head Count</th>
          </tr>";
    while ($row = mysqli_fetch_assoc($query)) {
        echo "<tr>";
        foreach ($row as $value) {
            // Use htmlspecialchars to prevent XSS issues, default to 0 for NULL values
            echo "<td>" . htmlspecialchars($value ?: 0) . "</td>";
        }
        echo "</tr>";
    }
    echo "</table>";
} else {
    // Handle query errors
    echo "Query failed: " . mysqli_error($conn);
}

Notes #

• If you don't need the breakdown by workstation type, simply remove the two COUNT(DISTINCT CASE...) columns from the query.
• If your definition of "total workstations" includes all entries across both config_location_workstation and asset_workstation, replace COUNT(c.workstation_number) with COUNT(DISTINCT COALESCE(c.workstation_number, a.workstation_number)) to avoid duplicate workstation IDs.

内容的提问来源于stack exchange，提问作者jackflick