很难理解如何从一个模式中获得一年中一个月按国家的总订单

Question

create table orders (id integer, country text, customer_id integer,
restaurant_id INTEGER, date date, order_value integer);
create table customers (id integer, name text, country text);
create table restaurants (id integer, name text, country text);
INSERT INTO orders (
id,
country,
customer_id,
restaurant_id,
date,
order_value)
VALUES
(1, 'Pakistan', 1, 1, '2021-01-01', 400),
(2, 'Pakistan', 2, 1, '2021-01-01', 500),
(3, 'Pakistan', 4, 2, '2021-01-01', 300),
(4, 'Pakistan', 4, 3, '2021-01-05', 200),
(5, 'Pakistan', 5, 4, '2021-01-01', 250),
(6, 'Pakistan', 4, 1, '2021-01-09', 266),
(7, 'Pakistan', 3, 2, '2021-01-07', 322),
(1, 'Holland', 1, 1, '2021-01-01', 378),
(8, 'Pakistan', 1, 3, '2021-06-01', 289),
(2, 'Holland', 1, 1, '2021-08-01', 480),
(9, 'Pakistan', 1, 1, '2021-03-01', 580),
(10, 'Pakistan', 3, 2, '2021-07-01', 360),
(3, 'Holland', 1, 1, '2021-09-01', 550),
(11, 'Pakistan', 4, 3, '2021-04-01', 991),
(12, 'Pakistan', 5, 1, '2021-04-01', 875),
(4, 'Holland', 1, 1, '2021-03-02', 250),
(13, 'Pakistan', 1, 1, '2021-08-01', 150),
(14, 'Pakistan', 1, 2, '2021-09-01', 290),
(5, 'Holland', 1, 1, '2021-07-01', 240),
(15, 'Pakistan', 1, 3, '2021-03-01', 780),
(16, 'Pakistan', 1, 4, '2021-06-01', 987),
(6, 'Holland', 1, 1, '2021-05-03', 457),
(17, 'Pakistan', 1, 4, '2021-05-04', 258);

任务是提取2021年1月每个国家的订单总数。如果我没有订单栏，我应该如何获得每个国家的总订单？

Answer 1

首先在WHERE子句中排除所有不在所选年份和月份的数据。

其余为GROUP BY国家

所以你得到

SELECT
country,
MIN(DATE_FORMAT(`date`,'%Y-%m')) Month_Year,
COUNT(*) Total_orders
FROM 
orders
WHERE YEAR(`date`)= 2021 AND MONTH(`date`)= 1
GROUP BY country

country|Month_Year|Total_orders：-------------------------------------||：-------------------------------------||巴基斯坦|2021-01|7荷兰|2021-01|1

<blockquote\

SELECT
country,
COUNT(*) Total_orders
FROM 
orders
WHERE YEAR(`date`)= 2021 AND MONTH(`date`)= 1
GROUP BY country

>

国家/地区|订单总数：-------|---------：巴基斯坦|7荷兰|1

db<gt；小提琴这里