需要帮助组合两个 MySQL 查询

Question

我在SQL方面并不出色，也不知道该怎么做。这是我目前的两个疑问。

SELECT TicketID_PK, SubProjectID_FK, ProjectID_FK, CustomerID_FK, TicketDate, TicketNumber, 
SignOff, WorkOrder, ClientPO, TicketType, Description, Processed 
FROM Tickets 
INNER JOIN CustomersSubProjects ON Tickets.SubProjectID_FK = CustomersSubProjects.SubProjectID_PK 
INNER JOIN CustomersProjects ON CustomersSubProjects.ProjectID_FK = CustomersProjects.ProjectID_PK 
WHERE TicketID_PK = 1
SELECT ROUND(WCB+Vacation+Stat+UIC+CPP+Overhead,2) AS Total 
FROM EmployeeFormula 
WHERE EffectiveDate <= $TicketDate
ORDER BY EffectiveDate DESC LIMIT 1

我将 TicketDate 保存为 $TicketDate 并在第二个查询中使用它，但我确信有一种方法可以将这两个查询结合起来以避免这种情况。任何帮助将不胜感激。

编辑：我需要通过将"工单日期"列（

在"工单"表中）与"生效日期"列进行比较来选择"员工公式总计"（如第二个查询所示）。所以结果应该看起来像这样：

TicketID_PK     SubProjectID_FK     ProjectID_FK    CustomerID_FK   TicketDate  TicketNumber    Total
1   1   1   1   2014-01-05  1-0501-00   30.78

Answer 1

像子查询一样使用。

SELECT TicketID_PK, SubProjectID_FK, ProjectID_FK, CustomerID_FK, TicketDate, TicketNumber, 
SignOff, WorkOrder, ClientPO, TicketType, Description, Processed, (SELECT ROUND(WCB+Vacation+Stat+UIC+CPP+Overhead,2) FROM EmployeeFormula WHERE EffectiveDate <= TicketDate ORDER BY EffectiveDate DESC LIMIT 1) AS Total
FROM Tickets 
INNER JOIN CustomersSubProjects ON Tickets.SubProjectID_FK = CustomersSubProjects.SubProjectID_PK 
INNER JOIN CustomersProjects ON CustomersSubProjects.ProjectID_FK = CustomersProjects.ProjectID_PK 
WHERE TicketID_PK = 1