1.問(wèn)題說(shuō)明

項(xiàng)目有兩個(gè)不同的平臺(tái)分別使用 Greenplum 和 MySQL 數(shù)據(jù)庫(kù)，但是這兩個(gè)數(shù)據(jù)庫(kù)的函數(shù)是不相同的，所以需要維護(hù)兩套查詢 SQL。

2.根據(jù)周幾獲取數(shù)據(jù)

2.1 原始函數(shù)結(jié)果

MySQL獲取周幾的函數(shù)為date_format( date, '%w')結(jié)果是周一到周日為：1，2，3，4，5，6，0

-- MySQL數(shù)據(jù)庫(kù) SELECT '周一' AS weekInfo, date_format( '2022-04-11 08:00:00', '%w') AS weekVal UNION ALL SELECT '周二' AS weekInfo, date_format( '2022-04-12 08:00:00', '%w') AS weekVal UNION ALL SELECT '周三' AS weekInfo, date_format( '2022-04-13 08:00:00', '%w') AS weekVal UNION ALL SELECT '周四' AS weekInfo, date_format( '2022-04-14 08:00:00', '%w') AS weekVal UNION ALL SELECT '周五' AS weekInfo, date_format( '2022-04-15 08:00:00', '%w') AS weekVal UNION ALL SELECT '周六' AS weekInfo, date_format( '2022-04-16 08:00:00', '%w') AS weekVal UNION ALL SELECT '周日' AS weekInfo, date_format( '2022-04-17 08:00:00', '%w') AS weekVal

Greenplum獲取周幾的函數(shù)為to_char( timestamp, 'd')結(jié)果是周一到周日為：2，3，4，5，6，1

-- Greenplum數(shù)據(jù)庫(kù) SELECT '周一' AS weekInfo, to_char( to_timestamp( '2022-04-11 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL SELECT '周二' AS weekInfo, to_char( to_timestamp( '2022-04-12 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL SELECT '周三' AS weekInfo, to_char( to_timestamp( '2022-04-13 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL SELECT '周四' AS weekInfo, to_char( to_timestamp( '2022-04-14 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL SELECT '周五' AS weekInfo, to_char( to_timestamp( '2022-04-15 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL SELECT '周六' AS weekInfo, to_char( to_timestamp( '2022-04-16 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL SELECT '周日' AS weekInfo, to_char( to_timestamp( '2022-04-17 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal

2.2 實(shí)現(xiàn)函數(shù)結(jié)果一致

可以看到 MySQL 的函數(shù)獲取的結(jié)果是數(shù)值，將結(jié)果+1即可實(shí)現(xiàn)與 Greenplum 數(shù)據(jù)庫(kù)函數(shù)結(jié)果一致。

-- MySQL數(shù)據(jù)庫(kù) SELECT '周一' AS weekInfo, date_format( '2022-04-11 08:00:00', '%w') +1 AS weekVal UNION ALL SELECT '周二' AS weekInfo, date_format( '2022-04-12 08:00:00', '%w') +1 AS weekVal UNION ALL SELECT '周三' AS weekInfo, date_format( '2022-04-13 08:00:00', '%w') +1 AS weekVal UNION ALL SELECT '周四' AS weekInfo, date_format( '2022-04-14 08:00:00', '%w') +1 AS weekVal UNION ALL SELECT '周五' AS weekInfo, date_format( '2022-04-15 08:00:00', '%w') +1 AS weekVal UNION ALL SELECT '周六' AS weekInfo, date_format( '2022-04-16 08:00:00', '%w') +1 AS weekVal UNION ALL SELECT '周日' AS weekInfo, date_format( '2022-04-17 08:00:00', '%w') +1 AS weekVal

Greenplum 的函數(shù)獲取的結(jié)果是字符，需要將結(jié)果轉(zhuǎn)換成數(shù)值再-1即可實(shí)現(xiàn)與 MySQL 數(shù)據(jù)庫(kù)函數(shù)結(jié)果一致。

-- Greenplum數(shù)據(jù)庫(kù) SELECT '周一' AS weekInfo, cast( to_char( to_timestamp( '2022-04-11 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL SELECT '周二' AS weekInfo, cast( to_char( to_timestamp( '2022-04-12 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL SELECT '周三' AS weekInfo, cast( to_char( to_timestamp( '2022-04-13 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL SELECT '周四' AS weekInfo, cast( to_char( to_timestamp( '2022-04-14 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL SELECT '周五' AS weekInfo, cast( to_char( to_timestamp( '2022-04-15 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL SELECT '周六' AS weekInfo, cast( to_char( to_timestamp( '2022-04-16 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL SELECT '周日' AS weekInfo, cast( to_char( to_timestamp( '2022-04-17 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal

兩種方式選擇一種即可。

總結(jié)

以上是生活随笔為你收集整理的【SQL编程】Greenplum 与 MySQL 数据库获取周几函数及函数结果保持一致的方法的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。