資料結構
網路
RDBMS
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理
化學
生物
數學
英語
經濟學
心理學
社會科學
時尚研究
法律研究在 MySQL 中，在表中設定的價格最大值和最小值之間按日期條件選擇？
您需要使用 CASE 語句在日期之間有條件地進行選擇，以找到最低和最高價格。使用聚合函式 MIN() 和 MAX() 包裝 CASE 語句。語法如下所示
SELECT MIN(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) -> ); Query OK, 0 rows affected (0.69 sec)
使用 insert 命令向表中插入一些記錄。查詢如下所示
mysql> insert into ConditionalSelect(StartDate,EndDate,LowerPrice,HigherPrice) values('2019-01-02','2019-04-02',5,10);
Query OK, 1 row affected (0.12 sec)
mysql> insert into ConditionalSelect(StartDate,EndDate,LowerPrice,HigherPrice) values('2019-04-02','2019-04-20',0,20);
Query OK, 1 row affected (0.17 sec)
mysql> insert into ConditionalSelect(StartDate,EndDate,LowerPrice,HigherPrice) values('2019-04-03','2019-04-21',0,30);
Query OK, 1 row affected (0.17 sec)使用 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)
更新於:30-Jul-2019
578 次瀏覽
廣告