<think>
我们正在将PostgreSQL 15.13的SQL查询转换为SQL Server 2019格式。需要注意两个数据库系统之间的语法差异。
原查询主要部分：
1. 使用CASE表达式处理日期字符串转换，将不同格式的字符串转换为DATE类型。
2. 使用LEFT JOIN和子查询。
3. 使用COALESCE函数。
4. 使用子查询选择最大f_id的记录。
在SQL Server中，我们需要注意：
- 字符串模式匹配：PostgreSQL使用LIKE和正则表达式，而SQL Server的LIKE不支持正则表达式。但是，在SQL Server中我们可以使用PATINDEX函数来模拟正则表达式匹配，但这里我们可能需要改变逻辑，因为原查询使用了正则表达式模式。
- 日期转换：SQL Server的CONVERT函数与PostgreSQL的CAST和CONVERT语法不同。
- 正则表达式：SQL Server本身不支持正则表达式（除非使用CLR函数），因此我们需要用其他方式实现类似功能。
原查询中CASE表达式有三个条件：
1. 匹配格式 '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$' -> 转换为DATE（在SQL Server中，我们可以使用TRY_CONVERT或CONVERT，但要注意格式）
2. 匹配格式 '\d{1,2}-\d{1,2}月 -\d{2}' -> 使用REPLACE进行转换（这里用到了正则表达式替换，但SQL Server没有内置的正则表达式，所以我们需要用CHARINDEX、SUBSTRING等函数来提取）
3. 匹配格式 '^\d{2}-[A-Z]{3}-\d{2}$' -> 使用CONVERT和格式106（dd-mon-yy）
由于SQL Server不支持正则表达式，我们需要改变处理方式。我们可以使用SQL Server的字符串函数（如PATINDEX，但注意PATINDEX支持一些通配符，但不支持完整的正则表达式）或尝试使用TRY_CONVERT、TRY_CAST并捕获异常，但SQL Server没有像TRY_CAST那样在转换失败时返回NULL而不报错的功能吗？实际上，SQL Server有TRY_CONVERT和TRY_CAST函数，它们可以在转换失败时返回NULL。
因此，我们可以尝试使用TRY_CONVERT来转换，并按照格式尝试。但是，原查询中使用了正则表达式来判断格式，然后进行转换。在SQL Server中，我们可以尝试按顺序转换，如果一种转换成功则返回，否则尝试下一种。
然而，原查询中的格式是互斥的，我们可以在SQL Server中按照顺序尝试转换，使用TRY_CONVERT，如果转换成功则使用，否则尝试下一个。
具体转换思路：
1. 第一种格式：'YYYY-MM-DD HH:MI:SS'，在SQL Server中对应的格式是120或121。我们可以用TRY_CONVERT(date, a.f_createdt, 120)或直接TRY_CAST(a.f_createdt AS date)（因为SQL Server的TRY_CAST可以隐式转换这种标准格式）。
- 注意：TRY_CAST对于这种标准格式的字符串是可以转换的，所以我们可以先尝试直接转换。
2. 第二种格式：如'1-1月-23'（原格式是\d{1,2}-\d{1,2}月 -\d{2}，但注意原字符串中有一个空格，例如'1-1月 -23'），我们需要将其转换为'01-01-2023'这样的格式，然后使用105格式（dd-mm-yyyy）转换。但是，在SQL Server中，我们无法直接使用正则表达式，所以需要手动处理字符串。
我们可以使用CHARINDEX找到'月'的位置，然后分别提取日、月、年（两位数的年）部分，然后组合成'dd-mm-yyyy'格式（注意：原格式中月份部分可能是1或2位，日也是1或2位，年只有两位数，我们需要补全为20xx）。然后使用CONVERT(date, 处理后的字符串, 105)（105格式是dd-mm-yyyy）。
但是，由于TRY_CONVERT在转换失败时返回NULL，我们可以尝试用字符串函数处理，然后尝试转换，如果转换失败则返回NULL。
3. 第三种格式：'dd-mon-yy'（例如'01-Jan-23'），使用106格式（dd mon yyyy）在SQL Server中不支持两位数的年，但106格式要求年份是四位？实际上，106格式是dd mon yyyy，所以我们需要将两位数的年转换为四位？但是原字符串是两位数的年，所以我们需要先扩展年份？不过，CONVERT函数在处理两位数的年时，会根据配置进行解释（比如大于等于50就是19xx，小于50就是20xx）。但是，原查询中使用了106格式，这个格式要求月份是三个字母的缩写，并且年份是四位？实际上，106格式可以接受两位或四位的年份吗？我们需要测试。
但是，由于原查询使用了正则表达式来判断，而SQL Server不支持，我们可能需要改变策略：使用多个TRY_CONVERT，并指定不同的格式，按顺序尝试，直到有一个成功。
因此，我们可以这样写：
CASE
WHEN TRY_CONVERT(datetime, a.f_createdt, 120) IS NOT NULL THEN TRY_CONVERT(date, a.f_createdt, 120) -- 第一种格式
WHEN ... THEN ... -- 第二种格式，我们需要先处理字符串再转换
WHEN TRY_CONVERT(datetime, a.f_createdt, 106) IS NOT NULL THEN TRY_CONVERT(date, a.f_createdt, 106) -- 第三种格式
ELSE NULL
END
但是，第二种格式的字符串需要预处理，所以我们不能直接在TRY_CONVERT中使用原始字符串。因此，我们可以写一个用户定义的函数来处理第二种格式，但这可能超出转换范围。或者，我们可以使用CASE表达式结合字符串函数。
我们尝试在SQL Server中处理第二种格式：
假设字符串格式为：数字-数字月 -数字（例如：'1-1月 -23'），我们想转换为日期。
步骤：
1. 去掉空格（原查询中格式有空格，但实际数据可能有变化？）我们按原查询逻辑，先REPLACE掉空格？但原查询中使用了REPLACE两次：一次替换为正则表达式的分组引用，一次替换掉'月'。实际上，原查询中：
REPLACE(REPLACE(a.f_createdt, '(\d{1,2})-(\d{1,2})月 -(\d{2})', '$1-$2-20$3'), '月', '')
这个在SQL Server中无法直接实现。所以我们需要用字符串函数分解。
我们可以这样做：
- 查找第一个'-'的位置，获取日部分（从开始到第一个'-'之前）
- 查找'月'的位置，获取月部分（从第一个'-'后面到'月'之前）
- 然后剩余部分（字符串末尾的两位年）
但注意，原字符串中在'月'后面有一个空格和'-'，然后是两个数字（年）。所以，我们也可以这样：
1. 替换掉'月'和空格，变成'1-1-23'，然后使用CONVERT(date, 字符串, 105)（105格式是dd-mm-yy，它会将两位数的年解释为20yy？实际上，SQL Server的日期转换中，两位数的年是由系统设置决定的，通常有一个截止年份（默认是2049年之前为20yy，2050-2099为20yy？但实际上是2049年之前解释为20yy，2050-2099解释为19yy？不，SQL Server的两位数年份截止点是可以配置的，默认是2049年之前为2000-2049，2050-2099为1950-1999？这显然不适合我们的情况（我们期望是20yy）。所以，为了避免歧义，我们应该将两位数的年明确转换为四位数（加上'20'前缀）。
因此，对于第二种格式，我们做：
- 去掉空格：REPLACE(a.f_createdt, ' ', '') -> 变成 '1-1月-23'
- 去掉'月'：REPLACE(上一步结果, '月', '') -> 变成 '1-1-23'
- 然后拆分字符串：日、月、年（两位）
- 组合成 '01-01-2023' 格式（注意补零）或者直接使用 '01/01/2023' 然后用110格式（mm-dd-yyyy）？不对，105格式是dd-mm-yyyy，所以组合成dd-mm-yyyy。
但是，拆分字符串在SQL Server中比较麻烦。我们可以使用CHARINDEX和SUBSTRING。
假设处理后的字符串（去掉空格和'月'）是像'1-1-23'这样的字符串，我们可以用：
DECLARE @str VARCHAR(20) = '1-1-23'
SELECT
SUBSTRING(@str, 1, CHARINDEX('-', @str)-1) AS day_part,
SUBSTRING(@str, CHARINDEX('-', @str)+1, CHARINDEX('-', @str, CHARINDEX('-', @str)+1) - CHARINDEX('-', @str)-1) AS month_part,
SUBSTRING(@str, CHARINDEX('-', @str, CHARINDEX('-', @str)+1)+1, LEN(@str)) AS year_part
然后，将day_part和month_part补零（如果长度为1，则在前面加0），year_part前面加'20'。
然后组合成新的字符串：day_part + '-' + month_part + '-' + '20' + year_part
然后使用TRY_CONVERT(date, 新字符串, 105)
但是，这个处理过程比较长，我们可以写在一个CASE表达式中，但会很长。考虑到查询性能，如果数据量很大，可能不理想。
另一种思路：由于原查询中只有三种格式，我们可以尝试按顺序转换，如果第一种转换失败（返回NULL）则尝试第二种，依此类推。
但是，在SQL Server中，我们可以使用嵌套的CASE表达式，先尝试第一种，如果成功则用第一种，否则尝试第二种（需要先处理字符串再转换），如果第二种转换成功则用第二种，否则尝试第三种，再否则NULL。
但是，注意：TRY_CONVERT在转换失败时返回NULL，不会报错。
所以，我们可以这样写：
CASE
WHEN TRY_CAST(a.f_createdt AS datetime) IS NOT NULL THEN TRY_CAST(a.f_createdt AS date) -- 这种方法可以转换第一种格式（标准格式）和第三种格式（如果第三种格式符合datetime的隐式转换规则）吗？
...
END
但是，标准格式（第一种）是能被TRY_CAST识别的。而第三种格式（'dd-mon-yy'）也是能被TRY_CAST识别的，因为SQL Server支持这种格式。那么第二种格式呢？经过处理后的字符串（dd-mm-yyyy）可以用105格式转换。
所以，我们可能不需要正则表达式匹配，而是直接尝试转换。但是，第二种格式原始字符串不能被TRY_CAST识别，所以我们需要先处理字符串再转换。
因此，我们这样处理：
CASE
WHEN TRY_CAST(a.f_createdt AS date) IS NOT NULL THEN TRY_CAST(a.f_createdt AS date) -- 这样会匹配第一种和第三种格式（以及任何SQL Server能识别的格式）
ELSE
-- 处理第二种格式：先去掉空格和'月'
TRY_CONVERT(date,
STUFF(STUFF(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''),
CHARINDEX('-', REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''), CHARINDEX('-', REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''))+1)+1,
0, '20'),
LEN(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''))-CHARINDEX('-', REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''), CHARINDEX('-', REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''))+1)+1,
0, '20') -- 这个STUFF是在最后一个数字前插入'20'，但这样写太复杂了，实际上我们只需要在最后两个字符前加'20'？不对，我们处理后的字符串是'dd-mm-yy'，我们想在最后两个字符前加'20'，也就是将yy变成20yy。
, 105)
END
这样写非常复杂，而且可能出错。
考虑到实际数据可能只有这三种格式，我们可以分情况：
1. 如果字符串包含'月'，则按第二种格式处理。
2. 否则，尝试用TRY_CAST转换，如果成功则用。
3. 否则，再尝试用格式106转换（但TRY_CAST已经尝试过，所以可能不需要了）。
因此，我们可以这样：
CASE
WHEN a.f_createdt LIKE '%月%' THEN
-- 处理第二种格式
-- 去掉空格和'月'
TRY_CONVERT(date,
-- 先处理字符串：去掉空格和'月'
-- 然后，将字符串按'-'分割，取三部分，并将最后两位年变成四位（加20）
-- 我们可以用：将最后两个字符用'20'+最后两个字符替换
STUFF(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''),
LEN(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''))-1,
0, '20'),
105)
ELSE TRY_CAST(a.f_createdt AS date) -- 尝试转换其他格式
END
但是，这样写可能不严谨，因为第三种格式也可能被TRY_CAST转换成功。而且，如果还有其他包含'月'的字符串但不是第二种格式，就会出错。
因此，我们按照原查询的正则表达式逻辑，用多个WHEN条件，并用字符串函数模拟正则匹配（虽然不完美，但可以接受）。
我们可以在WHEN条件中使用LIKE加上通配符来模拟部分正则：
1. 第一种格式：'____-__-__ __:__:__' （14个下划线），但注意原正则要求是4位年-2位月-2位日 2位时:2位分:2位秒。我们可以用LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]'，但这样写很长，而且SQL Server的LIKE不支持字符类[0-9]？实际上，SQL Server支持[0-9]。
2. 第二种格式：LIKE '%[0-9]%-%[0-9]%月 -[0-9][0-9]'，但这样不够精确。
3. 第三种格式：LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]-[0-9][0-9]'，但要求是两位日、三位月份缩写、两位年。
但这样写LIKE条件可能效率不高，而且仍然不够精确。
权衡之后，我打算这样转换：
由于原查询中使用了正则表达式，而SQL Server不支持，我们假设数据是规范的，即只有这三种格式。我们可以按顺序尝试转换，并利用TRY_CONVERT：
SELECT
...,
COALESCE(
TRY_CONVERT(date, a.f_createdt, 120), -- 尝试第一种格式（120对应YYYY-MM-DD HH:MI:SS）
TRY_CONVERT(date,
-- 先处理第二种格式的字符串：去掉空格和'月'，然后在最后两个字符前插入'20'
STUFF(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''), LEN(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''))-1, 0, '20'),
105), -- 105对应dd-mm-yyyy
TRY_CONVERT(date, a.f_createdt, 106) -- 106对应dd mon yyyy（注意：原字符串是dd-mon-yy，而106要求四位数年？所以我们需要处理？但TRY_CONVERT可能自动将两位数的年转换为四位数？根据系统设置，这可能不可靠）
) AS f_createdt
但是，对于第三种格式，原字符串是'dd-mon-yy'，而106格式要求'dd mon yyyy'，所以我们需要将两位数的年转换为四位数？因此，我们也要处理第三种格式的字符串：
我们可以这样：对于第三种格式，也先处理字符串，在最后两个字符前加'20'，然后使用106格式转换。
但这样，我们可以统一处理第二种和第三种格式：在最后两个字符前加'20'，然后分别用105和106格式转换？不行，因为格式不同。
因此，我们分步骤：
f_createdt =
COALESCE(
TRY_CONVERT(date, a.f_createdt, 120), -- 第一种格式
CASE WHEN a.f_createdt LIKE '%月%' THEN -- 第二种格式
TRY_CONVERT(date, STUFF(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''), LEN(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''))-1, 0, '20'), 105)
ELSE NULL END,
TRY_CONVERT(date, STUFF(a.f_createdt, LEN(a.f_createdt)-1, 0, '20'), 106) -- 第三种格式：在最后两个字符前插入'20'，然后按106格式转换
)
但是，这样写，如果第二种格式转换成功，就不会尝试第三种。而且，COALESCE会返回第一个非NULL的值。
注意：第三种格式的字符串中可能也包含'-'，但没有'月'字，所以我们可以这样区分第二种和第三种？不一定，因为第三种格式是英文月份缩写。
所以，我们按照原查询的CASE表达式顺序，用COALESCE和条件判断来实现。
但是，为了保险，我们按照原查询的顺序：
CASE
WHEN a.f_createdt LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]'
THEN TRY_CONVERT(date, a.f_createdt, 120)
WHEN a.f_createdt LIKE '%[0-9]%-%[0-9]%月 -[0-9][0-9]'
THEN TRY_CONVERT(date, STUFF(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''), LEN(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''))-1, 0, '20'), 105)
WHEN a.f_createdt LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]-[0-9][0-9]'
THEN TRY_CONVERT(date, STUFF(a.f_createdt, LEN(a.f_createdt)-1, 0, '20'), 106)
ELSE NULL
END
这样，我们使用LIKE模式来模拟正则表达式（虽然不完美，但可以覆盖大部分情况）。
注意：LIKE模式中，[0-9]表示一个数字，[A-Z]表示一个大写字母。另外，原查询中第二种格式的匹配是'\d{1,2}-\d{1,2}月 -\d{2}'，我们这里用'%[0-9]%-%[0-9]%月 -[0-9][0-9]'，表示中间有任意字符，所以不够精确。但为了简化，我们假设字符串中只有一个'月'字，且格式固定。
由于LIKE模式不能限定开头和结尾（除非用通配符），我们这里没有用^和$，所以可能会匹配到中间部分，但实际数据可能是干净的。
因此，转换后的CASE表达式如上。
另外，在SQL Server中，字符串函数中如果参数为NULL，则返回NULL，所以如果a.f_createdt为NULL，整个表达式返回NULL，符合预期。
现在，我们写完整的转换后查询：
SELECT
a.f_id,
a.f_site,
a.f_po,
a.f_poitem,
a.f_line,
a.f_ciscosku,
a.f_cpasku,
a.f_orderdate,
a.f_orderqty,
CASE
WHEN a.f_createdt LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]'
THEN TRY_CONVERT(date, a.f_createdt, 120)
WHEN a.f_createdt LIKE '%[0-9]%-%[0-9]%月 -[0-9][0-9]'
THEN TRY_CONVERT(date, STUFF(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''), LEN(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''))-1, 0, '20'), 105)
WHEN a.f_createdt LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]-[0-9][0-9]'
THEN TRY_CONVERT(date, STUFF(a.f_createdt, LEN(a.f_createdt)-1, 0, '20'), 106)
ELSE NULL
END AS f_createdt
FROM bts.bts_edi850 a
LEFT JOIN (
SELECT f_po, f_poitem, f_line, SUM(f_commitQty) AS qty
FROM bts.bts_edi855_detail
GROUP BY f_po, f_poitem, f_line
) b ON a.f_po = b.f_po AND a.f_poitem = b.f_poitem AND a.f_line = b.f_line
WHERE a.f_commitflag <> 'Y'
AND a.f_orderqty <> 0
AND a.f_orderqty <> COALESCE(b.qty, 0)
AND a.f_id IN (SELECT MAX(f_id) FROM bts.bts_edi850 GROUP BY f_po, f_poitem, f_line)
注意：
- 在SQL Server中，表名和字段名可以用方括号括起来，也可以不用，除非使用了保留字或特殊字符。
- 我们假设原查询中的表名和字段名在SQL Server中都是有效的。
另外，子查询中的COALESCE函数在SQL Server中也是存在的，所以保留。
最后，WHERE子句中的条件：a.f_id IN (SELECT MAX(f_id) ...) 在SQL Server中同样适用。
但是，注意SQL Server的TOP或ROW_NUMBER()常用于取最大的一条，但这里用子查询也可以，只要bts_edi850表的数据量不是特别大。
因此，我们得到了转换后的查询。
由于转换的日期部分比较复杂，实际运行前应进行测试。
我们输出完整转换后的SQL。
</think>根据您的需求，我将 PostgreSQL 15.13 的 SQL 查询转换为 SQL Server 2019 兼容格式。主要修改点包括正则表达式处理、日期转换函数调整以及语法优化：
### 转换后的 SQL Server 2019 查询
```sql
SELECT
a.f_id,
a.f_site,
a.f_po,
a.f_poitem,
a.f_line,
a.f_ciscosku,
a.f_cpasku,
a.f_orderdate,
a.f_orderqty,
CASE
WHEN a.f_createdt LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]'
THEN TRY_CONVERT(DATE, a.f_createdt, 120)
WHEN a.f_createdt LIKE '%[0-9]%-%[0-9]%月 -[0-9][0-9]'
THEN TRY_CONVERT(DATE,
STUFF(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''),
LEN(REPLACE(REPLACE(a.f_createdt, ' ', ''), '月', ''))-1, 0, '20'),
105)
WHEN a.f_createdt LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]-[0-9][0-9]'
THEN TRY_CONVERT(DATE, STUFF(a.f_createdt, LEN(a.f_createdt)-1, 0, '20'), 106)
ELSE NULL
END AS f_createdt
FROM bts.bts_edi850 a
LEFT JOIN (
SELECT
f_po,
f_poitem,
f_line,
SUM(f_commitQty) AS qty
FROM bts.bts_edi855_detail
GROUP BY f_po, f_poitem, f_line
) b ON a.f_po = b.f_po
AND a.f_poitem = b.f_poitem
AND a.f_line = b.f_line
WHERE a.f_commitflag <> 'Y'
AND a.f_orderqty <> 0
AND a.f_orderqty <> ISNULL(b.qty, 0)
AND a.f_id IN (
SELECT MAX(f_id)
FROM bts.bts_edi850
GROUP BY f_po, f_poitem, f_line
)
```
### 关键修改说明
1. **正则表达式转换**：
- 使用 `LIKE` 配合模式匹配 `[0-9]` 和 `[A-Z]` 替代 PostgreSQL 的正则表达式
- 示例：`'^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$'` → `'[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]'`
2. **日期处理优化**：
- 使用 `TRY_CONVERT(DATE, ..., style)` 替代 PostgreSQL 的 `CAST`/`CONVERT`
- 复杂格式处理：
```sql
-- 处理 '1-1月 -23' 格式
STUFF(REPLACE(REPLACE(f_createdt, ' ', ''), '月', ''),
LEN(处理后的字符串)-1, 0, '20')
```
3. **函数和语法调整**：
- `COALESCE()` → `ISNULL()`
- 显式声明 `TRY_CONVERT` 的日期格式代码（105/106/120）
- 保留 `CASE` 逻辑结构，确保三種日期格式的优先级顺序
4. **子查询兼容性**：
- 保持 `LEFT JOIN` 和子查询结构不变
- SQL Server 完全支持 `SUM()...GROUP BY` 聚合方式
- `IN (SELECT MAX()...)` 语法直接兼容
> **重要提示**：日期转换部分依赖数据质量，如果存在非常规格式，建议先用 `TRY_CONVERT` 测试单条记录[^3]。对于生产环境，推荐在数据清洗层统一日期格式。
本文介绍 PostgreSQL 数据库的实用程序，包括用户管理和系统管理工具。覆盖数据库创建、备份、用户权限设置等核心功能，并提供示例演示如何执行这些操作。
扫一扫
5726
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
