1. Oracle 中将一个表中数据导入到另外一个表的方法
insert into scd_data_201007 select * from analog_data_201007 ;
2. 纵表转换为横表
纵表格式
pid dtag ttag dqf pval
1001-1-1001 100 1 0 10.10
横表格式：
pid h1 h2 h3
Select --Pid,Substr(Pid, 8, 4) As Dev_Type,
Substr(Pid, 0, 6) As Dev_Type,
To_Char(To_Date('', 'yyyymmdd') + Dtag, 'YYYY-MM-DD') || ' ' ||
Trunc(Ttag / 60) || ':00:00' As Datetime,
-- Dtag, Ttag,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy1, Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy2,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy3,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy4,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy5,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy6,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy7,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy8,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As C2h2,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As C2h6,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy11,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy12,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy13,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy14,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy15,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy16,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy17,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy18,
Sum(Case Substr(Pid, 8, 4)
When '' Then
Pval
Else
0
End) As Cy19 From Analog_Data_200701
--Where Dtag = '2558' And Ttag = '240' And Substr(Pid, 0, 6) = '1001-4'
Group By Substr(Pid, 0, 6), Dtag, Ttag --, Pval
Order By Dtag, Ttag, Substr(Pid, 0, 6)
3. Oracle 从已知表同步数据
merge into sm_classes sc
using (select * from ceibs_uv_embaclass) c
on(sc.id = c.id)
when matched then update
set sc.classname=c.classname,sc.groupname=c.group_name
when not matched then
insert (sc.id,sc.classname,sc.groupname)values (c.id,c.classname,c.group_name)
参考文章