我要最近三个月的订单

Question

我想要最近3个月的订单，我有两张桌子

1. 订单表

CREATE TABLE `Orders` (
  `id` int NOT NULL,
  `OrderID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `shopId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `customerId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `itemCount` int NOT NULL,
  `totalPrice` decimal(13,2) NOT NULL,
  `customerEmail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `totalWeightInGrams` decimal(13,4) NOT NULL,
  `shopDomain` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1. OrderItems表

 CREATE TABLE `OrderItems` (
  `id` int NOT NULL,
  `orderId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `variantId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `quantity` int NOT NULL,
  `sku` varchar(128) NOT NULL,
  `shopDomain` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `price` decimal(13,4) NOT NULL,
  `weightInGrams` int DEFAULT NULL,
  `discount` decimal(13,2) DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `requiresShipping` tinyint(1) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

我想得到最近三个月的订单。实际上有两种类型的订单。我想区分订单的标题，会有一个产品将添加在订单中，我需要检查与该产品的订单和没有产品。插入演示数据。

    INSERT INTO `Orders` (`id`, `OrderID`, `shopId`, `customerId`, `itemCount`, `totalPrice`, `customerEmail`, `totalWeightInGrams`, `shopDomain`, `createdAt`, `updatedAt`) VALUES
(1, '4029121691694', '1', '5376382468142', 2, '44.36', 'shiv@test.com', '2000.0000', 'test.com', '2021-12-01 14:02:24', '2021-12-01 14:02:24'),
(7, '4029152722990', '1', '5376382468142', 2, '44.36', 'shiv@one.com', '2000.0000', 'test2.com',  '2021-12-01 15:01:16', '2021-12-01 15:01:16'),



INSERT INTO `OrderItems` (`id`, `orderId`, `variantId`, `title`, `quantity`, `sku`, `shopDomain`, `price`, `weightInGrams`, `discount`, `currency`, `requiresShipping`, `createdAt`, `updatedAt`) VALUES
(5, '4029121691694', '32790648258606', ' product test1 ', 1, 'ST-0001', 'test.com', '19.9900', 2000, '0.00', 'INR', 1, '2021-12-01 14:02:25', '2021-12-01 14:02:25'),
(6, '4029121691694', '39639081451566', 'Static product', 1, 'STO2020', 'test.com', '0.6500', 0, '0.00', 'INR', 0, '2021-11-17 14:02:25', '2021-12-01 14:02:25'),
(7, '4029152722990', '39639081451566', 'Static product', 1, 'STO2020', 'test.com', '0.6500', 0, '0.00', 'INR', 0, '2021-12-01 15:01:17', '2021-12-01 15:01:17');

我是添加静态产品的订单，所以我需要检查所有的订单，其中有“静态产品”标题，没有静态产品与最近3个月的订单。

输出将类似于这样，它并不完全是该数据查询的输出，但根据数据可以是类似的输出。

"orders": {
"2021-11-01": {
  "total": 1,
  "staticProductOrder": 1
},
"2021-12-01": {
  "total": 3,
  "staticProductOrder": 2
}

}

我已经执行了这个查询

SELECT * 
FROM Orders 
INNER JOIN OrderItems on Orders.OrderId = OrderItems.orderId 
where Orders.shopDomain = '${Domain}'

我已经接到了所有的命令。

有人能帮我吗？如果您有任何问题，请告诉我。

Answer 1

试试这个

SELECT YEAR(a.createdAt), MONTH(a.createdAt), 
  COUNT(DISTINCT a.OrderID) total_orders, 
  COUNT(DISTINCT b.orderId) static_product_orders
FROM Orders a
LEFT JOIN OrderItems b ON a.OrderID = b.orderId AND b.title = 'Static product'
WHERE a.createdAt >= CURDATE() - INTERVAL 3 MONTH
AND a.shopDomain = 'test.com'
GROUP BY YEAR(a.createdAt), MONTH(a.createdAt)

顺便说一句，您的表没有按照表脚本的任何索引。

演示

Answer 2

SELECT MONTH(createdAt), year(createdAt),count(*) as total
FROM Orders 
INNER JOIN OrderItems on Orders.OrderId = OrderItems.orderId 
where Orders.shopDomain = '${Domain}' AND createdAt>= DATEADD(MONTH, -3, 
GETDATE())  group by MONTH(createdAt),year(createdAt);