如何从该 sql 查询中消除子查询？

Question

我有一个应该可以工作的查询，但似乎我是糟糕的数据库技术的受害者。我需要在 Pervasive SQL 数据库上运行下面的查询。使用 Pervasive 的产品制造商告诉我他们使用的版本是 10，应该支持子查询，但我还不能运行一个简单的子查询。所以，我想知道是否可以重写以下查询以消除子查询：

SELECT
    OuterTime.Employee,
    OuterTime.Date,        
    OuterTime.Pay_ID,        
    OuterTime.Description,        
    OuterTime.Equipment,        
    OuterTime.JC_Cost_Code,        
    OuterTime.JC_Category,        
    OuterTime.Units,  
    (   
        SELECT
            SUM(SubQueryTime.Units)
        FROM
            PRT_NEW__TIME AS SubQueryTime
        WHERE
            SubQueryTime.Employee = OuterTime.Employee
        GROUP BY
            SubQueryTime.Employee
    ) AS TotalHoursForEmp
FROM
    PRT_NEW__TIME AS OuterTime

Answer 1

在标准 SQL 中你可以做到这一点，尽管我具体不知道 PervasiveSQL...

SELECT
    OuterTime.Employee,
    OuterTime.Date,        
    OuterTime.Pay_ID,        
    OuterTime.Description,        
    OuterTime.Equipment,        
    OuterTime.JC_Cost_Code,        
    OuterTime.JC_Category,        
    OuterTime.Units,
    COALESCE(TotalHoursForEmp.TotalUnits, 0)  AS TotalUnits
FROM
    PRT_NEW__TIME AS OuterTime
LEFT JOIN
    (   
        SELECT
            Employee,
            SUM(Units) AS TotalUnits
        FROM
            PRT_NEW__TIME
        GROUP BY
            Employee
    )
    AS TotalHoursForEmp
        ON TotalHoursForEmp.Employee = OuterTime.Employee

Answer 2

不是 Pervasive SQL 专家，但这行得通吗？不确定它是否实现了您的查询目标：

SELECT
    OuterTime.Employee,
    OuterTime.Date,        
    OuterTime.Pay_ID,        
    OuterTime.Description,        
    OuterTime.Equipment,        
    OuterTime.JC_Cost_Code,        
    OuterTime.JC_Category,        
    SUM(OuterTime.Units) AS TotalHoursForEmp
    GROUP BY OuterTime.Employee, OuterTime.Date, OuterTime.Pay_ID,
             OuterTime.Description, OuterTime.Equipment, OuterTime.JC_Cost_Code,
             OuterTime.JC_Category
FROM
    PRT_NEW__TIME AS OuterTime