如何合并具有不同标志的同一个表中的数据？

Question

我正在尝试提取包含作业编号、订单编号、零件编号和说明的数据。我的表格中的某些行被标记为“L”（表示人工）和“M”（表示材料）。 L 标记的行可以按字面意思理解，hours_worked = 工作总时间，人工 = 人工成本，间接费用 = 间接费用成本。 M 标记行不同，hours_worked = 购买数量，amount_labor = 材料购买价格。如何区分这两个标志并将其打印到同一个表中？

这是我到目前为止所得到的：

SELECT 
DETAIL.JOB, 
DETAIL.SUFFIX, 
ORDER.ORDER_NO, 
ORDER.ORDER_LINE,
ORDER.PART,
DETAIL.SEQ, 
OPERATIONS.DESCRIPTION,
SUM(lbr.Hours) AS sum_hours,
SUM(lbr.Labor) AS sum_labor,
SUM(lbr.Overhead) AS sum_overhead, 
SUM(mtl.Qty) AS sum_qty,
SUM(mtl.material) AS sum_mtl
FROM 
    (SELECT 
    DETAIL.HOURS_WORKED AS Hours,
    DETAIL.AMOUNT_LABOR AS Labor,
    DETAIL.AMT_OVERHEAD AS Overhead
    FROM
    GT.DETAIL DETAIL
    WHERE
    DETAIL.LMO = 'L') AS lbr,
    (SELECT
    DETAIL.HOURS_WORKED AS Qty,
    DETAIL.AMOUNT_LABOR AS Material
    FROM
    GT.DETAIL DETAIL
    WHERE
    DETAIL.LMO = 'M') AS mtl,
GT.DETAIL DETAIL,
GT.OPERATIONS OPERATIONS, 
GT.ORDER ORDER  
WHERE 
ORDER.JOB = DETAIL.JOB AND
ORDER.SUFFIX = DETAIL.SUFFIX AND
DETAIL.JOB = OPERATIONS.JOB AND
DETAIL.SEQ = OPERATIONS.SEQ AND
DETAIL.SUFFIX = OPERATIONS.SUFFIX
GROUP BY
DETAIL.JOB, 
DETAIL.SUFFIX, 
ORDER.ORDER_NO, 
ORDER.ORDER_LINE,
ORDER.PART,
DETAIL.SEQ, 
OPERATIONS.DESCRIPTION

每次我运行上面的代码时，它都会永远运行并最终崩溃。预先感谢您的任何想法。

Answer 1

SQL 已知 LEFT/RIGHT/INNER JOIN 语法已有一段时间了。即使使用像 Pervasive 这样的数据库，您也可以使用这种 JOIN（更多信息：要踢的坏习惯：使用旧式连接）

我尝试重写您的查询，使用内部连接。

SELECT 
   DETAIL.JOB, 
   DETAIL.SUFFIX, 
   ORDER.ORDER_NO, 
   ORDER.ORDER_LINE,
   ORDER.PART,
   DETAIL.SEQ, 
   OPERATIONS.DESCRIPTION,
   SUM(CASE WHEN LMO='L' THEN DETAIL.Hours END) AS sum_hours,
   SUM(CASE WHEN LMO='L' THEN DETAIL.Labor END) AS sum_labor,
   SUM(CASE WHEN LMO='L' THEN DETAIL.Overhead END) AS sum_overhead, 
   SUM(CASE WHEN LMO='M' THEN DETAIL.Qty END) AS sum_qty,
   SUM(CASE WHEN LMO='M' THEN DETAIL.material END) AS sum_mtl
FROM
   GT.DETAIL DETAIL
INNER JOIN GT.OPERATIONS OPERATIONS ON OPERATIONS.JOB = DETAIL.JOB 
                                   AND OPERATIONS.SEQ = DETAIL.SEQ 
                                   AND OPERATIONS.SUFFIX = DETAIL.SUFFIX
INNER JOIN GT.ORDER ORDER ON ORDER.JOB = DETAIL.JOB
                         AND ORDER.SUFFIX = DETAIL.SUFFIX

WHERE
   GT.DETAIL.LMO IN ('L','M')

GROUP BY
   DETAIL.JOB, 
   DETAIL.SUFFIX, 
   ORDER.ORDER_NO, 
   ORDER.ORDER_LINE,
   ORDER.PART,
   DETAIL.SEQ, 
   OPERATIONS.DESCRIPTION

情况添加到 SUM() 中以进行正确的计算。

我不确定您是否需要WHERE GT.DETAIL.LMO IN ('L','M')。当您还需要选择 LMO 以及 L 和/或 M 的其他值时，您应该删除 WHERE 子句。