各数据库sql对比差异

数据对比测试过程中常用的一些sql语法整理。

1、Redshift

1.转数字

aaa::int as aaa,

aaa:bigint as aaa,

cast(aaa as real)  --字段类型是数字

2.转字符

aaa::varchar as aaa

3.转日期

TO_CHAR(aaa, 'YYYYMMDD') as aaa

4.转时间戳，以24h小时制显示，HH:MI:SS是12小时制

TO_CHAR(aaa, 'YYYYMMDD HH24:MI:SS') as aaa

用||拼接时间字符串：to_char(modified,'yyyy/mm/dd hh:')||extract(minute from modified) as modified

5.去掉空格

TRIM(aaa) ::varchar as aaa

6 替换字符

replace(aaa, chr(13), '')

replace(aaa,'，', ',') ::varchar as aaa

7.转成浮点数

aaa::Float as aaa

二、Oracle

1.转数字

TO_NUMBER(AAA) as AAA,

2.转字符

TO_CHAR(AAA) as AAA

3.转日期

TO_CHAR(AAA, 'YYYYMMDD') as AAA

4.转时间戳

TO_CHAR(AAA, 'YYYYMMDD HH24:MI:SS') as AAA

三、Teredata

1.转数字

cast(aaa as int) as aaa,

2.转日期

cast(aaa as DATE FORMAT 'YYYYMMDD') as aaa

3.转时间戳

cast(aaa as TIMESTAMP FORMAT 'YYYYMMDD HHMISS') as aaa

4.去掉空格

TRIM(aaa)

数据对比说明

1.两个表对比的时候，字段类型要转成一致，尤其是日期、浮点型，不然python取出来的是带有datetime的类型标志；

2.两边都是字符串类型的，可以不转直接对比；

3.表和csv文件对比：Python用pandas库读出来的csv数据，会自动识别数据类型。csv文件里的1.00，表里的1，要去掉.00后再比

一、为了更贴近生产数据测试，会在SIT开始前，将生产环境的数据通过数据共享方式拉取备份到SIT。
访问数据共享的数据方式：
select * from datashare名称.schema名称.表名

数据共享：
1)源库和目标库所属的实例在同一地域下，且已加入数据共享
2)目前仅支持表级别数据共享。
3)一个数据共享（Share）中最多可包含1000张表。
4)当源实例中有多个Share时，目标库只能订阅其中一个Share。
5)目标库无法对共享的表进行写操作。
6)共享的表无法进行DDL操作，如果需要进行DDL操作，需要取消该表的共享。

二、备份/恢复表
1.redshift恢复表：
truncate schema名称.表名; 
insert into schema名称.表名 select * from schema名称.表名_bckxxx

2.redshift备份表:
create table schema名称.表名_bckxxx
as select * from redshiftschema名称.表名

3.sql server备份表
select * into dbo.表名_bckxxx
from dbo.表名;

4.sql server恢复表，有自增键：
insert into dbo.表名
(列名1,列名2)
select 
列名1,列名2
from dbo.表名xxx

三、查询表创建信息
查表创建信息：
select * from svl_qlog where substring like 'create table schema名称.表名%' order by 字段名 desc limit 100;

select * from stl_query a, stl_querytext b where a.query = b.query and b.text ilike 'create table%xxx' 
order by a.字段名 desc limit 100; 

四、查表主键
select 字段名,count(1) from schema名称.表名
group by 字段名 having count(1)>1

五、sql中特殊字符处理
引号+空格+回车的特殊字符处理：
1.replace(replace(replace(replace(replace(replace(replace(字段名,'“','"'),'”','"'),'‘',''''),'’',''''), chr(10),'\\n'),chr(9),''), ' ','')

替换换行、回车、空格
replace(replace(trim(字段名), chr(10),'\\n'),chr(11),'\\t')

replace(replace(字段名, ' ',''),chr(9),' ')

六、首字母转小写：
upper(left(AAA,1))+lower(SUBSTRING(AAA,2,len(AAA)))

七、空转为null
case when 字段名 = '' then null else 字段名 end as 字段名

八、应用场景：大批量的表需要查行数时，用如下方法可把表名、行数通过表格形式展示出来
批量查行数：
select count(1) as num_count,'schema名称.表名'     as tablename from     schema名称.表名
union all 
select count(1) as num_count,'schema名称.表名'     as tablename from     schema名称.表名