Oracle表操作 (未完待续)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Select --Pid,Substr(Pid, 8, 4) As Dev_Type, Substr(Pid, 0, 6) As Dev_Type, To_Char(To_Date('20000101', 'yyyymmdd') + Dtag, 'YYYY-MM-DD') || ' ' || Trunc(Ttag / 60) || ':00:00' As Datetime, -- Dtag, Ttag, Sum(Case Substr(Pid, 8, 4) When '1001' Then Pval Else 0 End) As Cy1, Sum(Case Substr(Pid, 8, 4) When '1002' Then Pval Else 0 End) As Cy2, Sum(Case Substr(Pid, 8, 4) When '1003' Then Pval Else 0 End) As Cy3, Sum(Case Substr(Pid, 8, 4) When '1004' Then Pval Else 0 End) As Cy4, Sum(Case Substr(Pid, 8, 4) When '1005' Then Pval Else 0 End) As Cy5, Sum(Case Substr(Pid, 8, 4) When '1006' Then Pval Else 0 End) As Cy6, Sum(Case Substr(Pid, 8, 4) When '1007' Then Pval Else 0 End) As Cy7, Sum(Case Substr(Pid, 8, 4) When '1008' Then Pval Else 0 End) As Cy8, Sum(Case Substr(Pid, 8, 4) When '1009' Then Pval Else 0 End) As C2h2, Sum(Case Substr(Pid, 8, 4) When '1010' Then Pval Else 0 End) As C2h6, Sum(Case Substr(Pid, 8, 4) When '1011' Then Pval Else 0 End) As Cy11, Sum(Case Substr(Pid, 8, 4) When '1012' Then Pval Else 0 End) As Cy12, Sum(Case Substr(Pid, 8, 4) When '1013' Then Pval Else 0 End) As Cy13, Sum(Case Substr(Pid, 8, 4) When '1014' Then Pval Else 0 End) As Cy14, Sum(Case Substr(Pid, 8, 4) When '1015' Then Pval Else 0 End) As Cy15, Sum(Case Substr(Pid, 8, 4) When '1016' Then Pval Else 0 End) As Cy16, Sum(Case Substr(Pid, 8, 4) When '1017' Then Pval Else 0 End) As Cy17, Sum(Case Substr(Pid, 8, 4) When '1018' Then Pval Else 0 End) As Cy18, Sum(Case Substr(Pid, 8, 4) When '1019' 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)
参考文章
你们的评论、反馈,及对你们有所用,是我整理材料和博文写作的最大的鼓励和唯一动力。欢迎讨论和关注!
没有整理与归纳的知识,一文不值!高度概括与梳理的知识,才是自己真正的知识与技能。 永远不要让自己的自由、好奇、充满创造力的想法被现实的框架所束缚,让创造力自由成长吧! 多花时间,关心他(她)人,正如别人所关心你的。理想的腾飞与实现,没有别人的支持与帮助,是万万不能的。
没有整理与归纳的知识,一文不值!高度概括与梳理的知识,才是自己真正的知识与技能。 永远不要让自己的自由、好奇、充满创造力的想法被现实的框架所束缚,让创造力自由成长吧! 多花时间,关心他(她)人,正如别人所关心你的。理想的腾飞与实现,没有别人的支持与帮助,是万万不能的。