Mysql与Oracle日期sql
程序员文章站
2024-02-14 09:49:58
...
1.Mysql查询时间段间的每一天
select a.Date
from (
select '2020-08-30' - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2020-08-07' and '2020-08-30'
2.oracle 查询时间段的每一天
SELECT
TO_CHAR( TO_DATE( '2020-05-03', 'YYYY-MM-DD' ) + ROWNUM - 1, 'YYYY-MM-DD' ) dateStat
FROM
DUAL CONNECT BY ROWNUM <= TO_DATE( '2020-06-03', 'YYYY-MM-DD' ) - TO_DATE( '2020-05-03', 'YYYY-MM-DD' ) + 1
3.Mysql 查询某个月的中的自然周
(
SELECT DATE_FORMAT(ADDDATE(y.first, x.d - 1),'%x年-第%v周') as `week`
FROM
(SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) x,
(SELECT CONCAT('2020-08','-01') as FIRST, DAY(LAST_DAY(str_to_date('2020-08','%Y-%m'))) AS last) y
WHERE x.d <= y.last group by `week`)
上一篇: java实现插入排序以及原理解释
下一篇: TopK问题
推荐阅读
-
Mysql与Oracle日期sql
-
mysql 查询指定日期时间内sql语句实现原理与代码
-
Oracle中一个日期查找的误区_MySQL
-
MySQL与Oracle的语法区别对比
-
MySQL与oracle数据库的一些区别(转载)_MySQL
-
MySQL与oracle数据库的一些区别(转载)_MySQL
-
C++库连接Oracle, MS SQL, MySQL数据库
-
oracle10错误-求助 mysql的sql到oracle怎么变动
-
SQL Server大表数据的导出与导入命令BCP 博客分类: Oracle&MSSQL sql server
-
Mybatis JdbcType与Oracle、MySql数据类型对应列表 博客分类: sql