在一定时间内从两个表中获取SUM量的数据 [英] Getting data from two tables with SUM quantity of within certain period of Time

问题描述

我在下面有2张桌子:

订单明细

OrderNumber
quantityordered
priceEach

订单

OrderNumber
orderDate

问题: orderDate如下所示:2005-01-01. 2005-02-01. 2003-04-30.....etc. 总销售额=订购数量* priceEach

Question: orderDate look like this: 2005-01-01. 2005-02-01. 2003-04-30.....etc. The total sum of sales = quantityordered * priceEach

我必须找出2005年2月的销售总和 到目前为止，我有:

I have to find out the todal SUM of sales in Feb 2005 ONLY So far I have:

SELECT orderdate FROM orders WHERE orderdate LIKE'2005-02%';

AND

SELECT sum(quantityordered * priceEach) as Total FROM orderdetails;

但是，我怎样才能将这2合为1来回答这个问题? 非常感谢您在高级方面的巨大帮助！

However, how can I combined these 2 into 1 to answer the question? Greatly appreciate for your great help in advanced!

AlexK

推荐答案

• 您可以使用日期函数，例如 Year()和 Month()分别从MySQL日期中获取年号和月号.
• 使用内部联接 >.
• 计算 Sum()在销售中，年份和月份与您的条件匹配.
• 此外，在处理多个表时使用别名是一个好习惯.联接，子查询，基于派生表的方法等.
• You can use date functions like Year() and Month() to get year and month number out of a MySQL date, respectively.
• Simply do an Inner Join on both the tables, using OrderNumber.
• Calculate Sum() on sales, where the year and month matches your conditions.
• Also, it is a good habit to use Aliasing when dealing with multiple tables in a Join, Subquery, Derived tables based approach, etc.

请尝试以下操作:

SELECT SUM(od.quantityordered * od.priceEach) 
FROM Orders AS o 
INNER JOIN Orderdetails AS od ON o.OrderNumber = od.OrderNumber 
WHERE YEAR(o.orderDate) = 2005 
  AND MONTH(o.orderDate) = 2

这篇关于在一定时间内从两个表中获取SUM量的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！