我正在使用SQLite,并具有3个表,项目,发票和订单。我有一个基于项目表的select语句,该语句会选择存储在该表中的字段,但是我还希望特定项目的发票总和和特定项目订单的总和。我知道如何单独获取所有值,但是我还没有弄清楚如何将订单/发票的总和值添加到项目数据行中。是我需要搜索的嵌套选择,还是其他概念?
根据项目表选择
SELECT project.projectID, project.project_title, project.end_date, project.project_manager, project_status.project_status
FROM project
LEFT JOIN project_status
ON project.project_statusID=project_status.project_statusID
WHERE project.project_statusID BETWEEN 1 AND 12
根据发票表进行选择,其中x将是第一次选择的项目ID
SELECT sum(invoice_net)
FROM invoice
WHERE projectID= x
根据订单表进行选择,其中x是第一次选择时的项目ID
SELECT sum(total_order)
FROM order
WHERE projectID = x
您没有提供示例数据,甚至没有提供足够的数据库模式来回答提供所需的确切SQL的答案。
您没有描述发票或订单表。我必须假设它们包含一个ProjectID属性。
您可以使用子选择:
http://www.techrepublic.com/article/use-sql-subselects-to-consolidate-queries/1045787/ https://msdn.microsoft.com/en-us/library/ff487138.aspx
您选择的可能看起来像(未经测试):
SELECT
project.projectID,
project.project_title,
project.end_date,
project.project_manager,
project_status.project_status,
(SELECT sum(invoice.invoice_net) FROM invoice WHERE invoice.projectID = project.projectID),
(SELECT sum(order.total_order) FROM order WHERE order.projectID = project.projectID)
FROM project
LEFT JOIN project_status
ON project.project_statusID=project_status.project_statusID
WHERE project.project_statusID BETWEEN 1 AND 12
或加入分组依据。看:
您必须将所有未汇总的分组(通常是您从项目表中选择的所有字段),然后将汇总函数(求和)应用于明细属性(invoice_net和total_order)。
像这样(未经测试):
SELECT
project.projectID,
project.project_title,
project.end_date,
project.project_manager,
project_status.project_status,
sum(invoice.invoice_net),
sum(order.total_order)
FROM project
LEFT JOIN project_status
ON project.project_statusID=project_status.project_statusID
LEFT JOIN invoice
ON project.projectID = invoice.projectID
LEFT JOIN order
ON project.projectID = order.projectID
WHERE project.project_statusID BETWEEN 1 AND 12
GROUP BY project.projectID, project.project_title, project.end_date,
project.project_manager, project_status.project_status
选择哪一个?性能应该是可比的,但是查询优化器中的弱点可能会导致一个偏爱另一个。如果这很重要,请测试性能。
对于一次性的情况,如果联接表的视图尚不存在,我可能会使用subselect。但是实际上,确实存在或应该存在一个视图,在这种情况下,使用GROUP BY来使用该视图(或根据需要创建视图)是很自然的。
因此,真正的答案变为(未试用):
CREATE VIEW project_order_invoice AS
SELECT
project.projectID,
project.project_title,
project.end_date,
project.project_manager,
project_status.project_status,
invoice.invoice_net,
order.total_order
FROM project
LEFT JOIN project_status
ON project.project_statusID=project_status.project_statusID
LEFT JOIN invoice
ON project.projectID = invoice.projectID
LEFT JOIN order
ON project.projectID = order.projectID
SELECT
projectID,
project_title,
end_date,
project_manager,
project_status,
sum(invoice_net),
sum(total_order)
FROM project_order_invoice
WHERE project_statusID BETWEEN 1 AND 12
GROUP BY projectID, project_title, end_date,
project_manager, project_status
对于该视图,您可能希望包括所有表中的所有属性,但仅ID属性的一个副本除外。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句