Oracle+Sql Server相关查询语句

上周处理过 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;