📚 达梦数据库SQL编程

与SQL Server的不同之处

在达梦数据查询中的变量定义时，区别如下：

（1）一个批次查询中，只能有一个declare。

（2）不同变量之间、语句之间必须用分号分隔

（3）不能有@符号。

（4）定义变量之后的语句执行部分，必须用Begin和End包起来，且Begin和End只能出现一次。

（5）给变量赋值时直接用等号赋值，不可使用SET

DECLARE a int ; b int ;
BEGIN
    a=5;
    b=10;
    SELECT a*b;
END

达梦数据库字符串转数字，并附带默认值

DECLARE temp varchar; res int;
BEGIN
temp = '#{id}';
SELECT cast(case when ISNUMERIC(temp) then temp else 0 end as int) into  res;
select  res from dual;
END

sqlserver转换方法,带默认值的

declare @id0 INTEGER
set @id0 = isnull(try_convert(int,'#{id0}'),2025)

时间格式化方法

#sqlserver
FORMAT ( b.entrust_time, 'yyyyMM' )
#dameng
TO_CHAR ( b.entrust_time, 'yyyyMM' )

达梦劈开字符串操作

WITH t AS (
  SELECT '1,2,3,4' AS str FROM dual
)
SELECT REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) AS split_value
FROM t
CONNECT BY REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) IS NOT NULL
ORDER BY LEVEL;
#如果引用字段
SELECT REGEXP_SUBSTR(str_column, '[^,]+', 1, LEVEL) AS split_value
FROM your_table
CONNECT BY REGEXP_SUBSTR(str_column, '[^,]+', 1, LEVEL) IS NOT NULL
AND PRIOR rowid = rowid
ORDER BY LEVEL;

SQLserver劈开字符串方法

with vtype_row as(  
  select 1 id, '当前最大值,历史最大值' name1 
) 
SELECT t.id, s.value
FROM vtype_row t
CROSS APPLY STRING_SPLIT(t.name1, ',') s

SQL server按文字更新数据库记录顺序

with vtype_row as(  
  select 1 id, '张三,李四' name1 
) ,
good as (

select ROW_NUMBER() OVER (PARTITION BY id ORDER BY t.id) AS rnt,t.*,
  s.value
FROM vtype_row t
CROSS APPLY STRING_SPLIT(t.name1, ',') s),

data as (select a.*,b.rnt from  exa_person a left join good b on a.name = b.value WHERE a.[year] = 2025 and a.department_id = 612)

--select * from data


update s set s.rank = 621 + p.rnt
from exa_person s
left join data p on s.id=p.id WHERE s.[year] = 2025 and s.department_id = 612

达梦列转行操作

    SELECT 
        sensorId, 
        meaTime, 
        value_field, 
        value
    FROM (
        SELECT 
            sensorId, 
            meaTime, 
            ResValue1, 
            ResValue2, 
            ResValue3
        FROM SensorData
        WHERE STATUS IN (2, 4)
    ) data
    UNPIVOT (
        value FOR value_field IN (ResValue1, ResValue2, ResValue3)
    ) unpvt
    WHERE value IS NOT NULL