Oracle与SqlServer的区别大吗

目录

• 前言
• 1.数据类型区别
• 2.数据表个数查询的区别
• 3.实现查询
• 4.oracle不能再表后面使用’as’ 表字段后面可以使用‘as’,SQL Server 可以再表后面和表字段后面都可以使用‘as’
• 5.oracle区分表字段的大小写，SQL Server 不区分表字段的大小写
• 6.时间的处理
• 总结

---查询数据库中表的个数
select * from sysobjects where type='U'
--查询某个类型表的个数
select * from sysobjects
where type='U'
   and name like '%drugcost%'

--查询数据库表的个数
select * from user_tables;
--查询某个类型表的个数
select * from user_tables where table_name like '%drugcost%';

--sql server
select top 3 * from ODS_Fact_201201Incomedtl
--oracle
select * from (
SELECT row_number() over (order by "Id") as rn,
      "Id", "Date", "Sign", "PatientNo", "itemid",
      "AccountingId", "Deptid", "ExecDeptid", "Doctorid", "Amount", "Money"
FROM "ODS_Fact_201201Incomedtl" ) a
where rn <= 3;

--sql server
select YEAR(Date) as years,MONTH(Date) as months,sum(Money) as money
from ODS_Fact_201201Incomedtl a with(nolock)
left join ODS_Dim_Item b with(nolock) on a.itemid = b.id 
where b.Item like '%挂号%'
group by YEAR(Date),MONTH(Date)
--oracle
select to_char(a."Date",'yyyy') as dateyears,
       to_char(a."Date",'mm') as datemonths,
       sum("Money") as Money
from "ODS_Fact_201201Incomedtl" a
left join "ODS_Dim_Item" b on a."itemid" = b."id"
where b."Item" like '%挂号%'
group by to_char(a."Date",'yyyy'),to_char(a."Date",'mm');