高级查询计数日期

发布于 2025-01-11 22:10:51 字数 1635 浏览 0 评论 0原文

我正在尝试编写一个查询，该查询将返回事件表中每个事件的销售日期与非销售日期下的订单数量。

订单表

OrderID (PK, INT)	OrderDate (DATE)	EventID (INT,FK)
1	2022-01-01	1
2	2022-01-12	1
3	2022-01-01	1
4	2022-03-10	2
5	2022-03 -15	2

事件表

EventID (PK, FK, INT)	NameEvent (VARCHAR)	OnSalesDate (DATE)
1	The Rolling Stones	2022-01-01
2	The Who Live in Concert	2022-03-10

输出表应该类似于

Name_Event	BoughtOnSalesDate	BoughtAfter
The Rolling Stones	2	1
The Who Live in Concert	1	1

My到目前为止我的当前查询是：

SELECT OrderDate , EventName

FROM Orders

Left JOIN Event on Orders.EventID = Event.EventID

WHERE Orders.OrderDate = Event.OnSaleDate

ORDER BY OrderDate;

Which Outputs:

OrderDate	EventName
2022-01-01	滚石乐队
2022-01-01	滚石乐队
2022-03-10	The Who Live in 演唱会

原文

I am trying to write a query that will return the number of orders placed during on sales-date vs on non-on sales date for each event in my Event Table.

Orders Table

OrderID (PK, INT)	OrderDate (DATE)	EventID (INT,FK)
1	2022-01-01	1
2	2022-01-12	1
3	2022-01-01	1
4	2022-03-10	2
5	2022-03-15	2

Event Table

EventID (PK, FK, INT)	NameEvent (VARCHAR)	OnSalesDate (DATE)
1	The Rolling Stones	2022-01-01
2	The Who Live in Concert	2022-03-10

The Ouput Table should be Something like

Name_Event	BoughtOnSalesDate	BoughtAfter
The Rolling Stones	2	1
The Who Live in Concert	1	1

My current Query that I have so far is:

SELECT OrderDate , EventName

FROM Orders

Left JOIN Event on Orders.EventID = Event.EventID

WHERE Orders.OrderDate = Event.OnSaleDate

ORDER BY OrderDate;

Which Outputs:

OrderDate	EventName
2022-01-01	The Rolling Stones
2022-01-01	The Rolling Stones
2022-03-10	The Who Live in Concert

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

染火枫林 2025-01-18 22:10:52

您可以按事件数据分组以获得您想要的结果

创建表订单 (
  `订单ID` 整数，
  `订单日期` VARCHAR(10),
  `事件ID` 整数
）；

插入订单 
  (`订单ID`、`订单日期`、`事件ID`)
价值观
  ('1', '2022-01-01', '1'),
  ('2', '2022-01-12', '1'),
  ('3', '2022-01-01', '1'),
  ('4', '2022-03-10', '2'),
  ('5', '2022-03-15', '2');

创建表事件（
  `EventID` 整数，
  `NameEvent` VARCHAR(23),
  `OnSalesDate` VARCHAR(10)
）；

插入事件 
  (`EventID`、`NameEvent`、`OnSalesDate`) 
价值观
  ('1', '滚石乐队', '2022-01-01'),
  ('2', '演唱会现场的人', '2022-03-10');

SELECT `NameEvent`,`OnSalesDate`
, SUM(`OnSalesDate` = `OrderDate`) BoughtOnSalesDate
, SUM(`OnSalesDate` <`OrderDate`) BoughtAfter
来自订单
Orders.EventID = Event.EventID 上的左 JOIN 事件
按订单分组。`EventID`、`NameEvent`、`OnSalesDate`
按“OnSalesDate”订购；

名称事件 |销售日期 |购买销售日期 |后买
:---------------------- | :---------- | ----------------: | ----------：
滚石乐队 | 2022年1月1日 | 2 | 1
演唱会现场 | 2022年3月10日 | 1 | 1

db<>fiddle 此处

You can Group BY the data of event to get your wanted result

CREATE TABLE Orders  (
  `OrderID` INTEGER,
  `OrderDate` VARCHAR(10),
  `EventID` INTEGER
);

INSERT INTO Orders 
  (`OrderID`, `OrderDate`, `EventID`)
VALUES
  ('1', '2022-01-01', '1'),
  ('2', '2022-01-12', '1'),
  ('3', '2022-01-01', '1'),
  ('4', '2022-03-10', '2'),
  ('5', '2022-03-15', '2');

CREATE TABLE Event  (
  `EventID` INTEGER,
  `NameEvent` VARCHAR(23),
  `OnSalesDate` VARCHAR(10)
);

INSERT INTO Event 
  (`EventID`, `NameEvent`, `OnSalesDate`) 
VALUES
  ('1', 'The Rolling Stones', '2022-01-01'),
  ('2', 'The Who Live in Concert', '2022-03-10');

SELECT `NameEvent`,`OnSalesDate`
, SUM(`OnSalesDate` = `OrderDate`) BoughtOnSalesDate
, SUM(`OnSalesDate` < `OrderDate`)  BoughtAfter
FROM Orders
Left JOIN Event on Orders.EventID = Event.EventID
GROUP  BY Orders.`EventID`, `NameEvent`,`OnSalesDate`
ORDER BY `OnSalesDate`;

NameEvent               | OnSalesDate | BoughtOnSalesDate | BoughtAfter
:---------------------- | :---------- | ----------------: | ----------:
The Rolling Stones      | 2022-01-01  |                 2 |           1
The Who Live in Concert | 2022-03-10  |                 1 |           1

db<>fiddle here

回复收藏 0 原文

~没有更多了~