date_sub(order_date,st)
时间: 2023-11-07 20:14:20 浏览: 18
`DATE_SUB(order_date, INTERVAL st)` is a valid MySQL function for subtracting a time interval from a date. It subtracts the specified time interval (st) from the given date (order_date). The result will be a new date.
Please note that you need to replace `order_date` and `st` with the actual column and interval values you want to use in your query.
相关问题
spark sql date_sub
`date_sub` 是 Spark SQL 中的一个日期函数,用于将指定日期减去指定的天数。其语法如下:
```sql
date_sub(date: Column, days: Int): Column
```
其中,`date` 是要进行减法运算的日期列,`days` 是要减去的天数。
例如,假设有一个名为 `orders` 的表,其中包含一个名为 `order_date` 的日期列,我们可以使用 `date_sub` 函数将 `order_date` 列中的日期减去 7 天,得到一个新的日期列 `new_date`,如下所示:
```sql
SELECT date_sub(order_date, 7) AS new_date FROM orders
```
SELECT DATE_FORMAT( dmtr.`day`,'%Y-%m-%d') as oneday, dmtr.column_num, dmtr.table_num FROM dap_metadata_table_record AS dmtr GROUP BY oneday HAVING oneday >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) UNION ALL SELECT d.`day`, 0, 0 FROM ( SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS day UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) ) AS d LEFT JOIN dap_metadata_table_record AS dmtr1 ON d.`day` = dmtr1.`day` WHERE dmtr1.`day` IS NULL ORDER BY oneday DESC; 修改sql只查询七条
你可以将原来的查询语句中的 `HAVING` 子句改为 `WHERE` 子句,并添加一个条件限制只查询最近七天的记录,修改后的 SQL 查询语句如下:
```
SELECT DATE_FORMAT(dmtr.`day`, '%Y-%m-%d') AS oneday, dmtr.column_num, dmtr.table_num
FROM dap_metadata_table_record AS dmtr
WHERE dmtr.`day` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY oneday
UNION ALL
SELECT d.`day`, 0, 0
FROM (
SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS day
UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY)
UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY)
UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY)
UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY)
UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY)
UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)
) AS d
LEFT JOIN dap_metadata_table_record AS dmtr1 ON d.`day` = dmtr1.`day`
WHERE dmtr1.`day` IS NULL
ORDER BY oneday DESC
LIMIT 7;
```
以上 SQL 查询语句中,`WHERE dmtr.`day` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)` 子句限制只查询最近七天的记录,`LIMIT 7` 子句限制只查询七条记录。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)