php - 连接 2 个 MYSQL 查询

Question

下面有两个查询，第一个是针对我的 DataTables 的查询，第二个是针对常规 HTML 表格的查询。

第一个查询是:

SELECT a.*, b.personnel_name 
FROM `work_order` as a 
LEFT JOIN `personnel_master_data` as b ON a.`reg_no` = b.`reg_no` 
WHERE a.`status` = 'Continue' 
GROUP BY `id` 
$sOrder 
$sLimit

我在我的 php 文件中执行了查询，DataTables 运行良好，现在是第二个查询:

SELECT d.`wo_number`, SUM(d.`crew_est` * d.`manhour_est`) AS 'crew_kali_manhours', 
    CONCAT(((SUM(d.`crew_est` * d.`manhour_est`) / 
            (SELECT SUM(c.`crew_est` * c.`manhour_est`) 
             FROM `work_sheet_machine` AS c 
             WHERE c.`wo_number` = '$wo_number')) * 100)) AS `progress` 
FROM `work_sheet_machine` AS d 
WHERE d.`status` = 'FINISH' AND d.`wo_number` = '$wo_number'

第二个查询似乎在带有 GET 参数的常规 HTML 表上运行良好。我需要将第二个查询与第一个查询连接起来，并在 DataTables 服务器端处理文件上使用该查询。我在将第二个查询中的 progress 字段加入到第一个查询中时遇到问题。

Answer 1

将第二个查询作为子查询放入第一个查询中。

SELECT a.*, b.personnel_name, e.crew_cali_manhours, e.progress
FROM `work_order` as a 
LEFT JOIN `personnel_master_data` as b ON a.`reg_no` = b.`reg_no`
LEFT JOIN (
    SELECT d.`wo_number`, SUM(d.`crew_est` * d.`manhour_est`) AS 'crew_kali_manhours', 
        CONCAT(((SUM(d.`crew_est` * d.`manhour_est`) / 
                (SELECT SUM(c.`crew_est` * c.`manhour_est`) 
                 FROM `work_sheet_machine` AS c 
                 WHERE c.`wo_number` = d.wo_number)) * 100)) AS `progress` 
    FROM `work_sheet_machine` AS d 
    WHERE d.`status` = 'FINISH'
    GROUP BY wo_number
) AS e ON e.wo_number = a.wo_number
WHERE a.`status` = 'Continue' 
GROUP BY `id` 
$sOrder 
$sLimit

如果 work_sheet_machine 始终具有与 work_order 中的行匹配的行，则使用 INNER JOIN 代替 LEFT JOIN。