上周处理过 Oracle、Sql Server 数据库相关数据,发现其实它们的 SQL 查询语句有些是不太一样的,比如行列转置和将查询结果插入新表。本人还是比较愿意写 SQL 语句的,互联网的技术日新月异,前端后端各种框架、新技术每搁几年就得更新一遍,根本学不过来,而 SQL 语句这些多却少有变化,值得好好学习。
此篇博客将会根据后续的工作持续更新。
一、Sql Server
1、查询表数据
select * from hzwater
2、截取字段
截取字段的前 6 位
select SUBSTRING(t.cbmonth,0,7) as cbmonths FROM hzwater as t
3、增加几列(行列倒置)
原表字段 mainid、user_name、user_addr、ysxz、cbmonth、biaodi、meter_no、pianqu
首先将 cbmonth 字段截取前 6 位改为 cbmonths 字段,然后再增加几行,增加几列其实是按月份增加几个字段,如需要增加 201601、201602、201603、201604、201605、201606、201607、201608、201609、201610......201812
使用 CASE......WHEN......语句实现:
select meter_no as meter_no,mainid as mainid,user_name as user_name,user_addr as user_addr,ysxz as ysxz,pianqu as pianqu,
MAX(CASE cbmonths WHEN '201601' THEN cbsl ELSE 0 END) AS '201601',
MAX(CASE cbmonths WHEN '201602' THEN cbsl ELSE 0 END) AS '201602',
MAX(CASE cbmonths WHEN '201603' THEN cbsl ELSE 0 END) AS '201603',
MAX(CASE cbmonths WHEN '201604' THEN cbsl ELSE 0 END) AS '201604',
MAX(CASE cbmonths WHEN '201605' THEN cbsl ELSE 0 END) AS '201605',
MAX(CASE cbmonths WHEN '201606' THEN cbsl ELSE 0 END) AS '201606',
MAX(CASE cbmonths WHEN '201607' THEN cbsl ELSE 0 END) AS '201607'
from [hzwater_guotu].[dbo].middle
GROUP BY meter_no,mainid,user_name,user_addr,ysxz,pianqu
order by meter_no,user_name desc
注意:GROUP BY 后面的字段要跟前面 select 字段一样(除了 MAX(CASE...WHEN)里面的字段),否则容易出错;
得到的结果是:
4、将查询结果放入创建的新表
使用
select * into NewTablename from ...
如:
select * into middle from
(select t.mainid,t.user_name,t.user_addr,t.ysxz,SUBSTRING(t.cbmonth,0,7)
as cbmonths,t.cbsl,t.biaodi,t.meter_no,t.pianqu from hzcbdata as t)b
二、Oracle
1、查询数据
select * from Temp
2、增加几列(行列倒置)
表里面原有字段为:用户类别、用户名称、用电地址、计费电量、DFNY
处理完之后表的字段为:用电类别、用户名称、用电地址、201701、201702、201703、201704、201705、201706、201708......201812
使用 decode 语句实现:
select * from (select 用电类别,用户名称,用电地址,
sum (decode(dfny, '201701' , 计费电量, 0 )) "201701",
sum (decode(dfny, '201702' , 计费电量, 0 )) "201702",
sum (decode(dfny, '201703' , 计费电量, 0 )) "201703",
sum (decode(dfny, '201704' , 计费电量, 0 )) "201704",
sum (decode(dfny, '201705' , 计费电量, 0 )) "201705",
sum (decode(dfny, '201706' , 计费电量, 0 )) "201706",
sum (decode(dfny, '201707' , 计费电量, 0 )) "201707",
sum (decode(dfny, '201708' , 计费电量, 0 )) "201708",
sum (decode(dfny, '201709' , 计费电量, 0 )) "201709",
sum (decode(dfny, '201710' , 计费电量, 0 )) "201710",
sum (decode(dfny, '201711' , 计费电量, 0 )) "201711",
sum (decode(dfny, '201712' , 计费电量, 0 )) "201712",
sum (decode(dfny, '201801' , 计费电量, 0 )) "201801",
sum (decode(dfny, '201802' , 计费电量, 0 )) "201802",
sum (decode(dfny, '201803' , 计费电量, 0 )) "201803",
sum (decode(dfny, '201804' , 计费电量, 0 )) "201804",
sum (decode(dfny, '201805' , 计费电量, 0 )) "201805",
sum (decode(dfny, '201806' , 计费电量, 0 )) "201806",
sum (decode(dfny, '201807' , 计费电量, 0 )) "201807",
sum (decode(dfny, '201808' , 计费电量, 0 )) "201808",
sum (decode(dfny, '201809' , 计费电量, 0 )) "201809",
sum (decode(dfny, '201810' , 计费电量, 0 )) "201810",
sum (decode(dfny, '201811' , 计费电量, 0 )) "201811",
sum (decode(dfny, '201812' , 计费电量, 0 )) "201812"
from TEMPDLT20191203 group by 用电类别,用户名称,用电地址)t
处理完之后的结果为:
3、将表转置并查询
select * from (select 用户名称,用电地址,sum (decode(dfny, '201701' , 计费电量, 0 )) "201701",
sum (decode(dfny, '201702' , 计费电量, 0 )) "201702",
sum (decode(dfny, '201703' , 计费电量, 0 )) "201703",
sum (decode(dfny, '201704' , 计费电量, 0 )) "201704",
sum (decode(dfny, '201705' , 计费电量, 0 )) "201705"
from TEMPDLT20191203 group by 用户名称,用电地址)t where t.用户名称='向阳';
4、将查询结果放入创建的新表
create table NewTablename as select * from b;