mysql - 需要帮助组合两个 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 并在第二个查询中使用它，但我确信有一种方法可以组合这两个查询来避免这种情况。任何帮助将不胜感激。

编辑:我需要通过将 TicketDate 列(在 Tickets 表中)与 effectiveDate 列进行比较来选择 EmployeeFormula Total(如第二个查询中所示)。所以结果应该是这样的:

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