无法在SQL的子查询中打印最近6个月的数据

Question

我试图从一组数据中获得过去6个月的结果，但是当我运行我的查询时，它没有显示任何内容。我正在研究的问题是:

。使用子查询，打印名称、成本和运输的最大距离，如果运输已被分配(在这里使用分配的开始日期)从今天算起的最近6个月内(这里的Today表示查询运行的日期。不能硬编码日期)

这是我的查询:

SELECT TransportCost, TransportName, TransportMaxDist
FROM Service
WHERE FromDate IN
(SELECT FromDate
FROM Service
WHERE MONTH(FromDate) = 6); 

当我运行这个查询时，它显示了正确的列名，但是这些列中没有数据:

+---------------+---------------+------------------+
| TransportCost | TransportName | TransportMaxDist |
+---------------+---------------+------------------+
|               |               |                  |
+---------------+---------------+------------------+

以下是CREATE TABLE和示例数据语法:

CREATE TABLE Allocation (
TruckVINNum CHAR (4),
TransportID CHAR (2),
FromDate CHAR (16),
ToDate CHAR (16),
TruckAllocation CHAR (8),
PRIMARY KEY (TruckVINNum, TransportID)
);
INSERT INTO Allocation VALUES
('VO23','T1','2020-12-05','2020-12-08','3 Days'),
('VO24','T2','2020-12-03','2020-12-07','4 Days'),
('VO25','T3','2020-12-07','2020-12-13','6 Days'),
('VO26','T4','2021-09-01','2021-09-10','9 Days'),
('VO27','T5','2021-07-16','2021-07-21','5 Days');
CREATE TABLE Service(
TransportID CHAR(2) PRIMARY KEY,
TransportName VARCHAR(11),
TransportCost DECIMAL(6,2),
TransportMaxDist DECIMAL(6,2),
FromDate CHAR(16)
);
INSERT INTO Service VALUES
('T1','Removalist',5000.50, 1000.56,'2020-12-05'),
('T2','Carpenter',1500.00, 550.18, '2020-12-03'),
('T3','Electrician',1750.50, 988.00,'2020-12-07'),
('T4','Scaffolder',2400.70, 340.76,'2021-09-10'),
('T5','Plumber',2100.00, 668.90,'2021-07-16');

Answer 1

首先，您需要知道您的查询在做什么，特别是子查询。让我们在这里检查您的查询:

SELECT TransportCost, TransportName, TransportMaxDist
FROM Service
WHERE FromDate IN
/*the query inside the parentheses is called as subquery*/
(SELECT FromDate
FROM Service
WHERE MONTH(FromDate) = 6); 

当您单独运行该子查询时，您正在执行WHERE MONTH(FromDate) = 6，这意味着您只需要FromDate列中的月份值为6或June的数据;无论年月日。这并没有真正满足"…

"6个月内"条件，您需要使用MySQL日期操作函数，如date INTERVAL和CURDATE()或CURRENT_DATE()函数。因此，像这样的查询:

SELECT TransportCost, TransportName, TransportMaxDist, FromDate
FROM Service
WHERE FromDate >= DATE_SUB(CURDATE(),INTERVAL 6 MONTH);

可以看到，操作DATE_SUB(CURDATE(),INTERVAL 6 MONTH)取CURDATE()，即今天，用DATE_SUB()减去6个月的间隔。也可以这样写:

SELECT TransportCost, TransportName, TransportMaxDist, FromDate
FROM Service
WHERE FromDate >= (CURDATE() - INTERVAL 6 MONTH);

我想当你运行SELECT CURDATE() AS 'Today', DATE_SUB(CURDATE(),INTERVAL 6 MONTH) AS '6 Months Ago from Today'时，你会更了解这个函数的作用。

@KenWhite在评论中提到的一个特别的细节是关于FromDate(在两个表中)和ToDate列数据类型。在你的例子中是CHAR而不是DATE。这样做可能存在的问题是，在使用日期操作函数时可能会得到不正确/不一致的结果。有一种方法可以将该数据转换为可识别的日期格式，但这会使查询比必要的时间更长。因此，如果要存储日期，那么请为列使用适当的DATE数据类型。

这是一个演示小提琴。