MYSQL适配DM的语法问题
为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:xx
【操作系统】：xx
【CPU】:xx
【问题描述】*：
MYSQL语句如下：
SELECT
DATE_FORMAT( t1.HOUR, ‘%H:00-%H:59’ ) HOUR,
COUNT( t2.id ) num
FROM
(
SELECT
@cdate := DATE_ADD( @cdate, INTERVAL + 1 HOUR ) HOUR
FROM
(
SELECT
@cdate := DATE_ADD( DATE_FORMAT( CURDATE() - 1, ‘%Y-%m-%d %H:00’ ), INTERVAL - 1 HOUR )
FROM
access_statistics
) t0
LIMIT 24
) t1
LEFT JOIN (
SELECT
*,
DATE_FORMAT( create_date, “%Y-%m-%d %H:00” ) HOUR
FROM
(
SELECT
*
FROM
access_statistics
WHERE
TO_DAYS( NOW( ) ) - TO_DAYS( create_date ) = 1
GROUP BY
IP
) ip
) t2 ON t2.HOUR = t1.HOUR
GROUP BY
t1.HOUR;
实现的效果:
请问下达梦方面如何进行SQL修改来达到同样的效果
用这个看看
SELECT
DATE_FORMAT( t1.HOUR, ‘%H’)
||‘:00-’
||DATE_FORMAT( t1.HOUR, ‘%H’)
||‘:59’ HOUR
– COUNT( t2.id ) num
FROM
(
SELECT
DATE_FORMAT(cdate+rownum/24, ‘%Y-%m-%d %H’ ) HOUR
FROM
(
SELECT
DATE_ADD( DATE_FORMAT( CURDATE() - 1, ‘%Y-%m-%d %H’), INTERVAL -‘1’ HOUR ) cdate
FROM
ACCESS_STATISTICS
)
t0 LIMIT 24
)
t1
LEFT JOIN
(
SELECT
*,
DATE_FORMAT( create_date, ‘%Y-%m-%d %H’ ) HOUR
FROM
(
SELECT
*
FROM
ACCESS_STATISTICS
WHERE
TO_DAYS( NOW( ) ) - TO_DAYS( create_date ) = 1
GROUP BY
IP
)
ip
)
t2
ON
t2.HOUR = t1.HOUR
GROUP BY
t1.HOUR;
推荐一款专用于mysql迁移达梦的中间件：https://blog.csdn.net/xiaojia1001/article/details/132516102
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
SELECT to_char(‘2022-07-22 11:03:00’, ‘HH24:MI:SS’ ) ;
DATE_FORMAT函数需要替换参考安装目录下dmdbms/doc/DM8_SQL语言使用手册