复杂的sql 行转列揭秘

现在的位置: 首页 > 综合 > 正文

复杂的sql 行转列揭秘

2013年10月11日 ⁄ 综合 ⁄ 共 13260字 ⁄ 字号小中大 ⁄ 评论关闭

先看数据库表

CREATE TABLE MonthStats (
  DevNo   int NOT NULL,          -- 设备编号
  Time   smalldatetime NOT NULL PRIMARY KEY (DevNo, Time), -- 数据时间 (年月)
  TotalTime  int,   -- 总运行时间（分钟）
  RegularTime  int,   -- 合格时间（分钟）
  RegularRate  float,   -- 合格率（%）
  OverHighTime int,   -- 超高时间（分钟）
  OverHighRate float,   -- 超高率（%）
  OverLowTime  int,   -- 超低时间（分钟）
  OverLowRate  float,   -- 超低率（%）
  PwDnTime  int,   -- 停电时间（分钟）
  PwDnCnt   int,   -- 停电次数
  AverU   float,   -- 平均电压（V）
  MaxU   float,   -- 最高电压（V）
  MinU   float,   -- 最低电压（V）
  AverHarm  float,   -- 平均总谐波畸变率（%）
  MaxHarm   float,   -- 最大总谐波畸变率（%）
  MinHarm   float,   -- 最小总谐波畸变率（%）
  AverFreq  float,   -- 平均频率（Hz）
  MaxVoltStamp smalldatetime, -- 最高电压出现时刻
  MinVoltStamp smalldatetime, -- 最低电压出现时刻
  MaxHarmStamp smalldatetime, -- 最大总谐波畸变率出现时刻
  MinHarmStamp smalldatetime -- 最小总谐波畸变率出现时刻
     SafeTimeHarm int,   -- 谐波合格时间（分钟）
  SafeRateHarm float,   -- 谐波合格率（%）
  WarmTimeHarm int,   -- 谐波超限时间（分钟）
  WarmRateHarm float,   -- 谐波超限率（%）

  )

测试语句:

insert into MonthStats
values(1,'2010-01-01 00:01:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 10:00:00','2010-12-03 11:00:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,90,1,0.1)
insert into MonthStats
values(2,'2010-02-02 00:02:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 10:30:00','2010-12-03 11:43:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,91,1,0.1)
insert into MonthStats
values(7,'2010-03-03 00:03:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 10:50:00','2010-12-03 11:03:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,92,1,0.1)
insert into MonthStats
values(11,'2010-04-04 00:04:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 11:34:00','2010-12-03 14:40:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,93,1,0.1)
insert into MonthStats
values(18,'2010-05-05 00:05:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 11:50:00','2010-12-03 16:04:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,94,1,0.1)
insert into MonthStats
values(1,'2010-06-06 00:06:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 13:01:00','2010-12-03 17:07:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,95,1,0.1)
insert into MonthStats
values(1,'2010-07-07 00:07:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 13:20:00','2010-12-03 18:20:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,96,1,0.1)
insert into MonthStats
values(2,'2010-08-07 00:07:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 13:20:00','2010-12-03 18:20:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,96,1,0.1)
insert into MonthStats
values(2,'2010-09-07 00:07:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 13:20:00','2010-12-03 18:20:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,96,1,0.1)
insert into MonthStats
values(7,'2010-10-07 00:07:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 13:20:00','2010-12-03 18:20:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,96,1,0.1)
insert into MonthStats
values(11,'2010-11-07 00:07:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 13:20:00','2010-12-03 18:20:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,96,1,0.1)
insert into MonthStats
values(18,'2010-12-07 00:07:00',16,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 13:20:00','2010-12-03 18:20:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,96,1,0.1)
insert into MonthStats
values(19,'2011-01-07 00:07:00',17,20,1.1,3,2.1,6,5.5,8,1,18.5,15.3,200.0,155.3,23.5,65.4,43.6,'2010-11-23 13:20:00','2010-12-03 18:20:00','2010-12-15 18:00:00','2010-12-18 19:00:00',86400,96,1,0.1)

这blog怎么不可以插入sql Code:很无语.

现在根据数据时间进行行转列，想把它显示成这种效果

思路：怎么处理了？

1，先要把time 字段截取成年月这种格式，这里我做的是动态转换 列名，假设一年中只出现只有一条记录，那么

表拆分也只有一个列，如：2011-01-01 08:01:01 变成201101月。

2，在这张表的结构中有很多类型，特别的是里面有几个字段是时间类型，这种类型搞起来很恶心。

还有一点在做合计时，要把所有类型都转换字符串，为什么这样搞？

原因是在衔接表时，如果用一个聚合函数sum()统计出来的是int或float..等，你在哪一列中在搞个时间类型，

就出出现算法溢出等异常。

3，这里我加了一些难度，

[总计]列

最高电压出现，这样对应的最高电压出现时刻（时间）就在对应的对应max(最高电压)下面

top 1是防止最高电压都是一样只取其中一条

select top 1 [MaxVoltStamp] from MonthStats where DevNo=1 and MaxU=(select max(MaxU) from MonthStats where DevNo=1)

通过这种话得到最高电压出现时间,

下面还有最低电压，最高谐波等

过程:

IF OBJECT_ID('PP')IS NOT NULL DROP PROC PP
GO
create proc pp
@devNo int,--设备号
@year varchar(4)--年份
AS

--拼接字符串
declare @strTotalTime varchar(2000),-- 总运行时间（分钟）
@strRegularTime varchar(2000),      -- 合格时间（分钟）
@strRegularRate varchar(2000),  -- 合格率（%）
@strOverHighTime varchar(2000),  -- 超高时间（分钟）
@strOverHighRate varchar(2000),  -- 超高率（%）
@strOverLowTime varchar(2000),  -- 超低时间（分钟）
@strOverLowRate varchar(2000),  -- 超低率（%）
@strPwDnTime varchar(2000),   -- 停电时间（分钟）
@strPwDnCnt varchar(2000),   -- 停电次数
@strAverU varchar(2000),   -- 平均电压（V）
@strMaxU varchar(2000),       -- 最高电压（V）
@strMinU varchar(2000),       -- 最低电压（V）
@strAverHarm varchar(2000),   -- 平均总谐波畸变率（%）
@strMaxHarm varchar(2000),   -- 最大总谐波畸变率（%）
@strMinHarm varchar(2000),   -- 最小总谐波畸变率（%）
@strAverFreq varchar(2000),   -- 平均频率（Hz）
@strMaxVoltStamp varchar(2000),     -- 最高电压出现时刻
@strMinVoltStamp varchar(2000),     -- 最低电压出现时刻
@strMaxHarmStamp varchar(2000),     -- 最大总谐波畸变率出现时刻
@strMinHarmStamp varchar(2000),     -- 最小总谐波畸变率出现时刻
@strSafeTimeHarm varchar(2000),  -- 谐波合格时间（分钟）
@strSafeRateHarm varchar(2000),  -- 谐波合格率（%）
@strWarmTimeHarm varchar(2000),  -- 谐波超限时间（分钟）
@strWarmRateHarm varchar(2000)  -- 谐波超限率（%）

--时间转换列名
select @strTotalTime=ISNULL(@strTotalTime+',','')+'SUM(case when CONVERT(varchar(6),[Time],112)='''+日期+''' then TotalTime ELSE 0 END) AS ['+日期+'月]',
@strRegularTime=ISNULL(@strRegularTime+',','')+'SUM(case when CONVERT(varchar(6),[Time],112)='''+日期+''' then RegularTime ELSE 0 END) AS ['+日期+'月]',
@strRegularRate=ISNULL(@strRegularRate+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then RegularRate ElSE 0 END ) AS ['+日期+'月]',
@strOverHighTime=ISNULL(@strOverHighTime+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then OverHighTime ElSE 0 END ) AS ['+日期+'月]',
@strOverHighRate=ISNULL(@strOverHighRate+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then OverHighRate ElSE 0 END ) AS ['+日期+'月]',
@strOverLowTime=ISNULL(@strOverLowTime+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then OverLowTime ElSE 0 END ) AS ['+日期+'月]',
@strOverLowRate=ISNULL(@strOverLowRate+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then OverLowRate ElSE 0 END ) AS ['+日期+'月]',
@strPwDnTime=ISNULL(@strPwDnTime+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then PwDnTime ElSE 0 END ) AS ['+日期+'月]',
@strPwDnCnt=ISNULL(@strPwDnCnt+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then PwDnCnt ElSE 0 END ) AS ['+日期+'月]',
@strAverU=ISNULL(@strAverU+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then AverU ElSE 0 END ) AS ['+日期+'月]',
@strMaxU=ISNULL(@strMaxU+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then MaxU ElSE 0 END ) AS ['+日期+'月]',
@strMinU=ISNULL(@strMinU+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then MinU ElSE 0 END ) AS ['+日期+'月]',
@strAverHarm=ISNULL(@strAverHarm+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then AverHarm ElSE 0 END ) AS ['+日期+'月]',
@strMaxHarm=ISNULL(@strMaxHarm+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then MaxHarm ElSE 0 END ) AS ['+日期+'月]',
@strMinHarm=ISNULL(@strMinHarm+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then MinHarm ElSE 0 END ) AS ['+日期+'月]',
@strAverFreq=ISNULL(@strAverFreq+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then AverFreq ElSE 0 END ) AS ['+日期+'月]',
@strMaxVoltStamp=ISNULL(@strMaxVoltStamp+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then Convert(varchar(12),MaxVoltStamp,112) ElSE 0 END) AS ['+日期+'月]',
@strMinVoltStamp=ISNULL(@strMinVoltStamp+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then Convert(varchar(12),MinVoltStamp,112) ElSE 0 END) AS ['+日期+'月]',
@strMaxHarmStamp=ISNULL(@strMaxHarmStamp+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then Convert(varchar(12),MaxHarmStamp,112) ElSE 0 END) AS ['+日期+'月]',
@strMinHarmStamp=ISNULL(@strMinHarmStamp+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then Convert(varchar(12),MinHarmStamp,112) ElSE 0 END) AS ['+日期+'月]',
@strSafeTimeHarm=ISNULL(@strSafeTimeHarm+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then SafeTimeHarm ElSE 0 END ) AS ['+日期+'月]',
@strSafeRateHarm=ISNULL(@strSafeRateHarm+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then SafeRateHarm ElSE 0 END ) AS ['+日期+'月]',
@strWarmTimeHarm=ISNULL(@strWarmTimeHarm+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then WarmTimeHarm ElSE 0 END ) AS ['+日期+'月]',
@strWarmRateHarm=ISNULL(@strWarmRateHarm+',','')+'SUM(case when Convert(varchar(6),[Time],112)='''+日期+''' then WarmRateHarm ElSE 0 END ) AS ['+日期+'月]'
from (select distinct CONVERT(varchar(6),[Time],112)AS 日期 from MonthStats where [Time] like
'%'+@year+'%')t--模糊查询某年

--合并表,合计全部是字符串
EXEC(
'SELECT DevNo,'+@strTotalTime +',Convert(varchar,sum(TotalTime)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,'
+@strRegularTime+',Convert(varchar,sum(RegularTime)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,'
+@strRegularRate+',Convert(varchar,sum(RegularRate)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,'
+@strOverHighTime+',Convert(varchar,sum(OverHighTime)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,'
+@strOverHighRate+',Convert(varchar,sum(OverHighRate)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,'
+@strOverLowTime+',Convert(varchar,sum(OverLowTime)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,'
+@strOverLowRate+',Convert(varchar,sum(OverLowRate)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,' +@strPwDnTime+',Convert(varchar,sum(PwDnTime)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,' +@strPwDnCnt+',Convert(varchar,sum(PwDnCnt)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,' +@strAverU+',Convert(varchar,sum(AverU)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,' +@strMaxU+',Convert(varchar,sum(MaxU)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,' +@strMinU+',Convert(varchar,sum(MinU)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,' +@strAverHarm+',Convert(varchar,sum(AverHarm)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,' +@strMaxHarm+',Convert(varchar,sum(MaxHarm)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,' +@strMinHarm+',Convert(varchar,sum(MinHarm)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,' +@strAverFreq+',Convert(varchar,sum(AverFreq)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo, '+@strMaxVoltStamp+',Convert(varchar,(select top 1 [MaxVoltStamp] from MonthStats where
DevNo='+@devNo+' and MaxU=(select max(MaxU) from MonthStats where
DevNo='+@devNo+')),112) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo, '+@strMinVoltStamp+',Convert(varchar,(select top 1 [MinVoltStamp] from MonthStats where
DevNo='+@devNo+' and MinU=(select min(MinU) from MonthStats where
DevNo='+@devNo+')),112) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo, '+@strMaxHarmStamp+',Convert(varchar,(select top 1 [MaxHarmStamp] from MonthStats where
DevNo='+@devNo+' and MaxHarm=(select max(MaxHarm) from MonthStats where
DevNo='+@devNo+')),112) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo, '+@strMinHarmStamp+',Convert(varchar,(select top 1 [MinHarmStamp] from MonthStats where
DevNo='+@devNo+' and MinHarm=(select min(MinHarm) from MonthStats where
DevNo='+@devNo+')),112) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
union all
SELECT DevNo,'
+@strSafeTimeHarm+',Convert(varchar,sum(SafeTimeHarm)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
   union all
SELECT DevNo,'
+@strSafeRateHarm+',Convert(varchar,sum(SafeRateHarm)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
   union all
SELECT DevNo,'
+@strWarmTimeHarm+',Convert(varchar,sum(WarmTimeHarm)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
   union all
SELECT DevNo,'
+@strWarmRateHarm+',Convert(varchar,sum(WarmRateHarm)) 合计 FROM MonthStats where
DevNo='+@devNo+' group by DevNo
')

执行语句

EXEC PP 1,2010

效果:

DevNo 201001月 201002月 201003月 201004月 201005月 201006月 201007月 201008月 201009月 201010月 201011月 201012月合计 ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------------------------------ 0 0 0 16 16 0 0 0 0 0 48 0 0 0 20 20 0 0 0 0 0 60 0 0 0 1.1 1.1 0 0 0 0 0 3.3 0 0 0 3 3 0 0 0 0 0 9 0 0 0 2.1 2.1 0 0 0 0 0 6.3 0 0 0 6 6 0 0 0 0 0 18 0 0 0 5.5 5.5 0 0 0 0 0 16.5 0 0 0 8 8 0 0 0 0 0 24 0 0 0 1 1 0 0 0 0 0 3 0 0 0 18.5 18.5 0 0 0 0 0 55.5 0 0 0 30.4 20.3 0 0 0 0 0 66 0 0 0 30 50 0 0 0 0 0 90 0 0 0 155.3 155.3 0 0 0 0 0 465.9 0 0 0 23.5 23.5 0 0 0 0 0 70.5 0 0 0 65.4 65.4 0 0 0 0 0 196.2 0 0 0 43.6 43.6 0 0 0 0 0 130.8 0 0 0 20100303 20101223 0 0 0 0 0 20100303 0 0 0 20101103 20101203 0 0 0 0 0 20101201 0 0 0 20101215 20101215 0 0 0 0 0 20101215 0 0 0 20101218 20101218 0 0 0 0 0 20101218 0 0 0 86400 86400 0 0 0 0 0 259200 0 0 0 95 96 0 0 0 0 0 281 0 0 0 1 1 0 0 0 0 0 3 0 0 0 0.1 0.1 0 0 0 0 0 0.3 (24 行受影响)

图：