Oracle与sqlserver 某些函数汇总

要求：截取此字符串 HMH7493 第三位到结尾的字符串内容，结果：H7493,

以下是两个数据的写法：

sqlserver 函数：Right(orderId,LEN(orderId)-2) as orderId

Oracle 写法 ，使用substr函数： substr(orderId,3) as orderId

sqlserver的 isnull(b.PC_RECV_NO, '')=''  对应 Oracle的 nvl(b.PC_RECV_NO,'''')=''''

要求：统计一单的总数量（amount是varchar转成int类型  11.0转成了11）

select distinct orderId,sum(cast(amount as decimal)) as QTY ,order_date 
    from  order
     group by orderId,order_date
     order by order_date desc

要求：去重后统计总条数 两种实现方式

--分组后统计总条数
select COUNT(1) as total from(
    select orderId
    from order
    group by  orderId ) as temp

--去重后统计总条数
        SELECT
            COUNT(distinct orderId) as total
        FROM
            order

要求：实现sqlserver跨库查询Oracle数据

select  d.*  from openquery(SYNC,'select
 id,name,age
  from stu a   join teacher b on   a.id=b.id
  where a.name> ''小烈''
   ') d