从当前周的订单表中提取记录 [英] Pull records from orders table for the current week

问题描述

我有一个表，包含以下信息...

  Id | orderNumber | orderDate | customerId 
  

orderDate 是MySQL datetime字段

因此，我的问题是我如何提取订单列表本周的某一天。我的目标是使用Google图表API为本周销售制作图表：

  var data = google.visualization.arrayToDataTable（[
 ['Day'，'Sales']，
 ['Mon'，1]，
 ['Tue' 0]，
 ['Wed'，4]，
 ['Thursday'，3]，
 ['Fri'，0]，
] 
  

解决方案

让我们做你想要的：

  SELECT 
 WEEKDAY（`datetime_field`）AS`week_day`，
 COUNT（*）AS`sale_count`
 FROM` orders` 
 WHERE YEARWEEK（`datetime_field`）= YEARWEEK（NOW（））
 GROUP BY`week_day` 
 ORDER BY`week_day` ASC; 
  

这会返回一组记录 week_day 和 sale_count 。 了解详情。如果您使用GMT，请使用 NOW（）（如果您使用本地日期时间或使用 UTC_TIMESTAMP（）

请记住，我不知道您的数据库名称或字段的名称。

$ b

p $ p> CREATE TABLE`orders`（
`OrderID` int（11）NOT NULL AUTO_INCREMENT，
`OrderDate` datetime NOT NULL，
`OrderValue` decimal （7,2）unsigned NOT NULL，
PRIMARY KEY（`OrderID`）
）;

INSERT INTO`orders` VALUES（'1'，'2012-10-29 14:02:19'，'100.00'）;
INSERT INTO`orders` VALUES（'2'，'2012-10-30 14:02:19'，'123.00'）;
INSERT INTO`orders` VALUES（'3'，'2012-10-31 14:02:19'，'103.00'）;
INSERT INTO`orders` VALUES（'4'，'2012-11-01 14:02:19'，'232.00'）;
INSERT INTO`orders` VALUES（'5'，'2012-11-02 14:02:19'，'321.00'）;
INSERT INTO`orders` VALUES（'6'，'2012-11-03 14:02:19'，'154.00'）;
INSERT INTO`orders` VALUES（'7'，'2012-11-04 14:02:19'，'112.00'）;
INSERT INTO`orders` VALUES（'8'，'2012-10-29 14:02:19'，'100.00'）;

SELECT
WEEKDAY（`OrderDate`）AS`week_day`，
COUNT（*）AS`sales_count`，
SUM（`OrderValue`）AS`sales_value `
FROM` orders`
WHERE YEARWEEK（`OrderDate`）= YEARWEEK（NOW（））
GROUP BY`week_day`
ORDER BY`

这是用于创建表的SQL，本周每天添加1个订单，周一添加2个订单。

与此处 SQLFIDDLE.COM SAMPLE 。

I have a table with the following info...

Id | orderNumber | orderDate | customerId

orderDate is a MySQL datetime field and the current day, month, year and time is inserted into the database at the time the record is written.

So my question is how would I pull a list of orders for a certain day of this current week. My goal is to use the google charts API to make a chart for "This Week's Sales" that says something like:

 var data = google.visualization.arrayToDataTable([
                      ['Day', 'Sales'],
                      ['Mon',  1],
                      ['Tue',  0],
                      ['Wed',  4],
                      ['Thurs',  3],
                      ['Fri',  0],
                    ]);

解决方案

Let's do exactly what you want:

SELECT
    WEEKDAY(`datetime_field`) AS `week_day`,
    COUNT(*) AS `sale_count`
FROM `orders`
WHERE YEARWEEK(`datetime_field`) = YEARWEEK(NOW())
GROUP BY `week_day`
ORDER BY `week_day` ASC;

This returns a set of records with week_day and sale_count. Learn more here. Use NOW() if you use local datetime or use UTC_TIMESTAMP() if you play by GMT.

Do keep in mind I don't know your database name or the fields' names. You need to fill those in.

WORKING EXAMPLE:

CREATE TABLE `orders` (
  `OrderID` int(11) NOT NULL AUTO_INCREMENT,
  `OrderDate` datetime NOT NULL,
  `OrderValue` decimal(7,2) unsigned NOT NULL,
  PRIMARY KEY (`OrderID`)
);

INSERT INTO `orders` VALUES ('1', '2012-10-29 14:02:19', '100.00');
INSERT INTO `orders` VALUES ('2', '2012-10-30 14:02:19', '123.00');
INSERT INTO `orders` VALUES ('3', '2012-10-31 14:02:19', '103.00');
INSERT INTO `orders` VALUES ('4', '2012-11-01 14:02:19', '232.00');
INSERT INTO `orders` VALUES ('5', '2012-11-02 14:02:19', '321.00');
INSERT INTO `orders` VALUES ('6', '2012-11-03 14:02:19', '154.00');
INSERT INTO `orders` VALUES ('7', '2012-11-04 14:02:19', '112.00');
INSERT INTO `orders` VALUES ('8', '2012-10-29 14:02:19', '100.00');

SELECT
    WEEKDAY(`OrderDate`) AS `week_day`,
    COUNT(*) AS `sales_count`,
    SUM(`OrderValue`) AS `sales_value`
FROM `orders`
WHERE YEARWEEK(`OrderDate`) = YEARWEEK(NOW())
GROUP BY `week_day`
ORDER BY `week_day` ASC;

This is SQL to create a table, add 1 order per day for this week but 2 on Monday. And the query to fetch the report.

AND HERE'S SQLFIDDLE.COM SAMPLE.

这篇关于从当前周的订单表中提取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！