sqlite返回子查询多列

Question

我有一个 sqlite 子查询，其中包含以下查询，该查询正在计算小时数和 labour_rate。我现在遇到的唯一问题是我能否从子查询中获取两列以在主查询中输出。我尝试根据一些网络教程来布局查询，但需要一点帮助才能完成任务，因为我不断收到语法错误

  SELECT c.customerID, c.customer, sum( ifnull(il.line_price, 0 ) )/10000 AS net,  
  FROM customer AS c
  LEFT JOIN invoice AS i
  ON c.customerID = i.customerID
  LEFT JOIN invoice_line AS il
  ON i.invoiceID = il.invoiceID
  (SELECT sum(( ifnull(tl.mon,0) + ifnull(tl.tues,0) + ifnull(tl.wed,0) + ifnull(tl.thurs,0) + ifnull(tl.fri,0) + ifnull(tl.sat,0) + ifnull(tl.sun,0) ) * s.cost_rate)/10000 AS labour_rate, 
  sum(( ifnull(tl.mon,0) + ifnull(tl.tues,0) + ifnull(tl.wed,0) + ifnull(tl.thurs,0) + ifnull(tl.fri,0) + ifnull(tl.sat,0) + ifnull(tl.sun,0) ))/10000 AS
  FROM timesheet_line AS tl
  LEFT JOIN timesheet AS t
  ON tl.timesheetID = t.timesheetID
  LEFT JOIN staff AS s
  ON t.staffID = s.staffID
  WHERE (c.customerID = tl.customerID) AND (t.date BETWEEN '2014-03-01' AND '2015-12-01')
  GROUP BY tl.customerID) AS time1
  WHERE (i.date BETWEEN '2014-03-01' AND '2015-12-01') AND (time1.customerID = tl.customerID)
  GROUP BY c.customerID
  ORDER BY c.customer ASC

Answer 1

这里有一些语法错误。第一个(可能也是最重要的)是你必须JOIN您的子查询到结果集的其余部分。所以之后

LEFT JOIN invoice_line AS il
  ON i.invoiceID = il.invoiceID

您需要添加另一个 JOIN 语句:

LEFT JOIN 
    (SELECT 
         SUM(...) AS labour_rate, 
         SUM(...) AS hours,
         tl.customerID
     FROM
     ...) AS time1 
 ON <your join condition>

您必须在子查询中选择某种字段，您可以将其连接回发票，即 tl.customerID 。另外，在您的子查询中，您不能引用它之外的字段，因此您有 WHERE (c.customerID = tl.customerID)在您的子查询中它将失败，因为您试图引用 c.<fieldname> 。需要将其移至 ON JOIN的一部分条款。一旦你得到你的JOIN工作正常然后你可以改变你的最外层 SELECT类似于

SELECT c.customerID, c.customer, sum(ifnull(il.line_price,0))/10000 AS net, time1.labour_rate, time1.hours

以下是我将如何做到这一点的示例:

SELECT c.customerID, c.customer, sum( ifnull(il.line_price, 0 ) )/10000 AS net, time1.labour_rate, time1.[hours]
FROM customer AS c
    LEFT JOIN invoice AS i
    ON c.customerID = i.customerID
    LEFT JOIN invoice_line AS il
    ON i.invoiceID = il.invoiceID
    LEFT JOIN
        (SELECT 
            sum(( ifnull(tl.mon,0) + ifnull(tl.tues,0) + ifnull(tl.wed,0) + ifnull(tl.thurs,0) + ifnull(tl.fri,0) + ifnull(tl.sat,0) + ifnull(tl.sun,0) ) * s.cost_rate)/10000 AS labour_rate, 
            sum(( ifnull(tl.mon,0) + ifnull(tl.tues,0) + ifnull(tl.wed,0) + ifnull(tl.thurs,0) + ifnull(tl.fri,0) + ifnull(tl.sat,0) + ifnull(tl.sun,0) ))/10000 AS [hours],
            tl.customerID
         FROM timesheet_line AS tl
            LEFT JOIN timesheet AS t
            ON tl.timesheetID = t.timesheetID
            LEFT JOIN staff AS s
            ON t.staffID = s.staffID
        WHERE (t.date BETWEEN '2014-03-01' AND '2015-12-01')
        GROUP BY tl.customerID) AS time1
    ON c.customerID = time1.customerID
WHERE (i.date BETWEEN '2014-03-01' AND '2015-12-01') 
GROUP BY c.customerID
ORDER BY c.customer ASC