⼀些写ETL脚本常⽤的sql语句（⼀）
以下代码pk字段均为表的主键字段
1、在查询表的时候，往往需要left join另⼀张表，⽽有时候在left join之前⼜需要对该表先进⾏⼀层查询，再left join其结果集，例如：
A表存放⼈的收⼊信息，⼀个⼈对应多条收⼊信息。A表在left join 前需要先处理出⼈的总收⼊，故⽤（）将select id,sum(sr) from
A group by id包起来，并起别名为a，a代表select id,sum(sr) from A group by id执⾏后的结果集。
--id:⼈的ID，sr：收⼊，nl：年龄
select * from (
select id,sum(sr) from A group by id
) a left join B b on a.id = b.id
where a.nl >35
在写完ETL脚本后，经常需要查询⾃⼰⽣成的主键是否是结果集中的唯⼀值。pk唯⼀，⽅可执⾏脚本进⾏数据抽取，这种情况也可以使⽤上述⽅法查询主键重复字段：
select pk,count(pk) from (
select distinct
A表.pk+B表.pk+B表.pk as pk,
A表.⼀些字段,
B表.⼀些字段,
C表.⼀些字段
from A A表
left join B B表 on ...
left join C C表 on ...
where ...
) a
group by pk
having count(*)>1
mysql查看所有存储过程暂时没想到三层的⽤法，不过有时候可能有需要：
select aa.pk from (
select a.pk from (
select distinct
A表.pk+B表.pk+B表.pk as pk,
A表.⼀些字段,
B表.⼀些字段,
C表.⼀些字段
from A A表
left join B B表 on ...
left join C C表 on ...
where ...
) a
) aa
2、如果⼀张表中存在千万条数据，查看这张表的时候可以先查询表中前10条数据：
--oracle:
select * from A where rownum<=10
csdnvip--sqlserver:
select top 10 * from A
3、如果⼀张表有⼀百多个字段，在查看数据时，可以将⼏个重要的字段提取到前⾯显⽰，例如：将靠后的字段放到第⼀个字段位置显⽰
--oracle:
select a.最后⼀个字段,a.* from A a
--sqlserver
select 最后⼀个字段,* from A
oracle中不能像sqlserver那样直接将字段名写在最前⾯，需要给表起别名
4、如果根据⼏张表联合查询后⽣成的结果集只能采⽤主键⾃增的⽅式⽣成主键，才能进⾏数据抽取，则可以根据某个字段⾃增⽣成pk，例如：
职⼯表中有职⼯ID，职⼯名称，⼯资。需要left join 职⼯的个⼈信息表、岗位详情表等等，最后查询结果集可以⽣成⼀张新的表，假设该新⽣成的表的主键字段需要采⽤⾃增⽅式才能使得主键唯⼀：
select DISTINCT
row_number() over(order by s.id) as pk,
s.id,
s.name,
convert命令的作用s.salary,
html5模板打包
...
from staff s
left join 其他表
foreach用法crow_number() over(order by 某字段)：先将某字段排序，然后从1开始，第⼀条记录为1，第⼆条记录为2，...。该函数的执⾏时间晚于where 条件 ,group by ,order by
5、对于数据库中⼀张陌⽣的表，查看该表的有多少个字段：
--对于oracle:
--upper函数是为了将表名转换为⼤写字母
select count(*) from user_tab_columns where table_name=upper('表名')
--查看字段的名称
select a.COLUMN_NAME as 字段名称,a.* from user_tab_columns a where table_name=upper('表名')
--对于sql server，查看字段，需要在该表所在架构下打开sql查询窗⼝才能查到
select * from information_schema.COLUMNS where table_name = '表名';
6、存储过程：
假设职⼯的⼯资是⽉表，例如2019年1⽉是staff201901，该表存放2019年1⽉给哪个职⼯发放了多少⼯资，每个⽉表的表结构都是⼀样的，只有数据是不⼀样的。现在使⽤存储过程查询出2019年1到3⽉份所有发放⼯资信息（staff201901、staff201902、
staff201903三张表在本地oracle与sqlserver中都存在）：
对于sql server：
select id as 职⼯ID,salary as 发放⼯资,CREATETIME as 发放时间 from STAFF201902
union
select id as 职⼯ID,salary as 发放⼯资,CREATETIME as 发放时间 from STAFF201903
--使⽤存储过程
--定义变量tablename⽤于存放表名后⾯变化的年⽉，变量sql⽤于存放sql语句，rflag⽤于存放⽉份，lflag⽤于存放年份declare @tablename varchar(20), @SQL varchar(8000),@rflag int,@lflag int
--初始化⽉份为0，表的年⽉为空，sql语句为空，lflag存放2019
set @rflag=0;
set @tablename='';
set @lflag=2019;
set @SQL='';
--当⽉份⼩于3时
while(@rflag<3)
begin
--⽉份+1
set @rflag = @rflag+1;
--对于1-9的数字，处理成01-09
if(@rflag<10)
begin
--将年份⽉份拼接起来存放到tablename变量
set @tablename = 'staff'+CONVERT(varchar,@lflag)+'0'+convert(varchar,@rflag);
end
else
begin
--将年份⽉份拼接起来存放到tablename变量
set @tablename = 'staff'+CONVERT(varchar,@lflag)+convert(varchar,@rflag);
end
--拼接sql语句
set @SQL = @SQL + 'select id as 职⼯ID,salary as 发放⼯资,createtime as 发放时间 from '+convert(varchar,@tablename); if(@rflag<3)
begin
set @sql = @sql+'
union
';
end;
苹果电脑ps怎么切图END
--打印sql变量
print @sql
--把sql变量的内容作为sql语句执⾏
EXEC (@sql)
对于oracle：
select id as 职⼯ID,salary as 发放⼯资,CREATETIME as 发放时间 from STAFF201902
union
select id as 职⼯ID,salary as 发放⼯资,CREATETIME as 发放时间 from STAFF201903
--使⽤存储过程
declare
--定义变量tablename⽤于存放表名后⾯变化的年⽉，变量mysql⽤于存放sql语句，rflag⽤于存放⽉份，lflag⽤于存放年份tablename varchar(20);
mysql varchar(8000);
rflag int;
lflag int;
begin
--初始化⽉份为0，表的年⽉为空，sql语句为空，lflag存放2019
rflag:=1;
tablename:='';
lflag:=2019;
mysql:='';
--循环开始
loop
--当满⾜条件rflag>3时，退出循环
exit when rflag>3;
--对于1-9的数字，处理成01-09
if rflag<10
----将年份⽉份拼接起来存放到tablename变量
then tablename:=lflag||'0'||rflag;
else tablename:=lflag||rflag;
end if;
mysql := mysql||'select id,salary,createtime from staff'||tablename;
if rflag<3
then
tablename:='0'||rflag;
mysql:=mysql||' union ';
--请注意elsif 不是elseif
elsif rflag=3
then
tablename:='0'||rflag;
end if;
--⽉份+1
rflag := rflag+1;
-
-结束循环
end loop;
--打印mysql变量的内容
dbms_output.put_line(mysql);
--动态执⾏sql语句
EXECUTE IMMEDIATE mysql;
end;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论