在MySQL中的日期之间有条件地选择表中设置的价格的最大值和最小值?
您需要使用CASE语句有条件地在日期之间进行选择,以找到最低和最高价格。用聚合函数MIN()
和结束CASE语句MAX()
。语法如下:
SELECTMIN(CASE WHEN CURDATE() BETWEEN yourStartDateColumnName AND yourEndDateColumnName THEN yourLowPriceColumnName ELSE yourHighPriceColumnName END) AS anyVariableName,
MAX(CASE WHEN CURDATE() BETWEEN yourStartDateColumnName AND yourEndDateColumnName THEN yourLowPriceColumnName ELSE yourHighPriceColumnName END) AS anyVariableName FROM yourTableName;
为了理解上述语法,让我们创建一个表。创建表的查询如下:
mysql> create table ConditionalSelect-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> StartDate datetime,
-> EndDate datetime,
-> LowerPrice int,
-> HigherPrice int,
-> PRIMARY KEY(Id)
-> );
使用insert命令在表中插入一些记录。查询如下:
mysql> insert into ConditionalSelect(StartDate,EndDate,LowerPrice,HigherPrice) values('2019-01-02','2019-04-02',5,10);mysql> insert into ConditionalSelect(StartDate,EndDate,LowerPrice,HigherPrice) values('2019-04-02','2019-04-20',0,20);
mysql> insert into ConditionalSelect(StartDate,EndDate,LowerPrice,HigherPrice) values('2019-04-03','2019-04-21',0,30);
使用select语句显示表中的所有记录。查询如下:
mysql> select *from ConditionalSelect;
以下是输出:
+----+---------------------+---------------------+------------+-------------+| Id | StartDate | EndDate | LowerPrice | HigherPrice |
+----+---------------------+---------------------+------------+-------------+
| 1 | 2019-01-02 00:00:00 | 2019-04-02 00:00:00 | 5 | 10 |
| 2 | 2019-04-02 00:00:00 | 2019-04-20 00:00:00 | 0 | 20 |
| 3 | 2019-04-03 00:00:00 | 2019-04-21 00:00:00 | 0 | 30 |
+----+---------------------+---------------------+------------+-------------+
3 rows in set (0.00 sec)
这是在日期之间选择最低和最高价格的查询:
mysql> SELECT-> MIN(CASE WHEN CURDATE() BETWEEN StartDate AND EndDate THEN LowerPrice ELSE HigherPrice END) AS MinimumValue,
-> MAX(CASE WHEN CURDATE() BETWEEN StartDate AND EndDate THEN LowerPrice ELSE HigherPrice END) AS MaximumValue
-> from ConditionalSelect;
以下是输出:
+--------------+--------------+| MinimumValue | MaximumValue |
+--------------+--------------+
| 5 | 30 |
+--------------+--------------+
1 row in set (0.00 sec)
以上是 在MySQL中的日期之间有条件地选择表中设置的价格的最大值和最小值? 的全部内容, 来源链接: utcz.com/z/331484.html