我新建了些基础数据,贴上来。 建立两个表LocalBscBts、EnergyTab_Ext,环境是SQL SERVER 2005 USE [ceshi] GO /****** 对象: Table [dbo].[collect] 脚本日期: 04/26/2014 08:42:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE LocalBscBts( [Local] [varchar](30), [City] [varchar](30) NOT NULL, [LocalTabID] [bigint] NOT NULL, [BSC] [varchar](30) NOT NULL, [BSCTabID] [bigint] NOT NULL, [BTS_Name] [varchar](30) NOT NULL, [ID] [bigint] NOT NULL ) ON [PRIMARY]USE [ceshi] GO /****** 对象: Table [dbo].[EnergyTab_Ext] 脚本日期: 04/26/2014 09:39:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[EnergyTab_Ext]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [BTSTab_ID] [bigint] NOT NULL, [Energy] [float] NULL DEFAULT (0.0), [Energy_Ext] [float] NULL DEFAULT (0.0), [DateTime] [datetime] NULL, [CollectType] [int] NULL DEFAULT (0), ) ON [PRIMARY]两张表的数据 use ceshi go insert into LocalBscBts values('区域1','城市11',11,'站点111',111,'基站1111',1111) insert into LocalBscBts values('区域1','城市11',11,'站点111',111,'基站1112',1112) insert into LocalBscBts values('区域1','城市11',11,'站点111',111,'基站1112',1113) insert into LocalBscBts values('区域1','城市12',12,'站点121',121,'基站1211',1211) insert into LocalBscBts values('区域1','城市12',12,'站点122',122,'基站1221',1221) insert into LocalBscBts values('区域1','城市12',12,'站点123',123,'基站1231',1231) insert into LocalBscBts values('区域1','城市13',13,'站点131',131,'基站1311',1311) insert into LocalBscBts values('区域1','城市13',13,'站点132',132,'基站1321',1321) insert into LocalBscBts values('区域1','城市13',13,'站点131',131,'基站1312',1312) insert into LocalBscBts values('区域2','城市21',21,'站点211',211,'基站2111',2111) insert into LocalBscBts values('区域2','城市21',21,'站点211',211,'基站2112',2112) insert into LocalBscBts values('区域2','城市21',21,'站点211',211,'基站2112',2113) insert into LocalBscBts values('区域2','城市22',22,'站点221',221,'基站2211',2211) insert into LocalBscBts values('区域2','城市22',22,'站点222',222,'基站2221',2221) insert into LocalBscBts values('区域2','城市22',22,'站点223',223,'基站2231',2231) insert into LocalBscBts values('区域2','城市23',23,'站点231',231,'基站2311',2311) insert into LocalBscBts values('区域2','城市23',23,'站点232',232,'基站2321',2321) insert into LocalBscBts values('区域2','城市23',23,'站点231',231,'基站2312',2312) go use ceshi go insert into EnergyTab_Ext values(1111,1002,2003,'2013-5-1',1) insert into EnergyTab_Ext values(1111,1004,2006,'2013-5-2',1) insert into EnergyTab_Ext values(1111,1006,2008,'2013-5-3',1) insert into EnergyTab_Ext values(1111,1008,2010,'2013-5-4',1) insert into EnergyTab_Ext values(1111,1100,2020,'2013-5-10',1) insert into EnergyTab_Ext values(1111,11002,2031,'2013-5-11',1) insert into EnergyTab_Ext values(1111,11003,2038,'2013-5-12',1) insert into EnergyTab_Ext values(1111,11006,2045,'2013-5-13',1) insert into EnergyTab_Ext values(1111,11010,2070,'2013-5-15',1) insert into EnergyTab_Ext values(1112,1015,2013,'2013-5-1',1) insert into EnergyTab_Ext values(1112,1021,2026,'2013-5-7',1) insert into EnergyTab_Ext values(1112,1044,2038,'2013-5-8',1) insert into EnergyTab_Ext values(1112,1051,2040,'2013-5-9',1) insert into EnergyTab_Ext values(1112,1054,2050,'2013-5-10',1) insert into EnergyTab_Ext values(1112,1059,2051,'2013-5-11',1) insert into EnergyTab_Ext values(1112,1063,2068,'2013-5-12',1) insert into EnergyTab_Ext values(1112,1070,2075,'2013-5-13',1) insert into EnergyTab_Ext values(1112,1075,2080,'2013-5-15',1) go要求统计2013-5-10到2013-5-15号之间的所有属于区域1的基站的用电量(分别为Energy,Energy_Ext的差值),类似结果如下:Energy的起始值默认是从指定时间的前两天中统计出最新值,若前两天没有的话就从指定时间内统计
猜测楼主的意思,写了如下的SQL,要的日期直接更换即可SELECT t1.LOCAL,t1.city,t1.BSC,t1.BTS_Name, MAX(CASE t2.Datetime WHEN '2013-05-10 00:00:00' THEN CONVERT(varchar(100),t2.Datetime, 23) END) AS StartTime, MAX(CASE t2.Datetime WHEN '2013-05-15 00:00:00' THEN CONVERT(varchar(100),t2.Datetime, 23) END) AS EndTime, MAX(CASE t2.Datetime WHEN '2013-05-10 00:00:00' THEN t2.Energy END) AS StartEnergy, MAX(CASE t2.Datetime WHEN '2013-05-15 00:00:00' THEN t2.Energy END) AS EndEnergy, MAX(CASE t2.Datetime WHEN '2013-05-10 00:00:00' THEN t2.Energy_Ext END) AS StartEnergy_Ext, MAX(CASE t2.Datetime WHEN '2013-05-15 00:00:00' THEN t2.Energy_Ext END) AS EndEnergy_Ext FROM LocalBscBts AS t1 INNER JOIN EnergyTab_Ext AS t2 ON t1.ID = t2.BTSTab_ID WHERE datetime = '2013-05-10 00:00:00' OR datetime= '2013-05-15 00:00:00' GROUP BY t1.LOCAL,t1.city,t1.BSC,t1.BTS_Name
不说说2013-5-10到2013-5-15的 你图里居然还有9号的数据;with tb as ( select a.local,a.bts_name,Energy,Energy_Ext,b.DateTime ,ROW_NUMBER() over(order by DateTime) as orderid From LocalBscBts a join EnergyTab_Ext b on a.ID=b.BTSTab_ID where b.DateTime>='2013-05-10' and b.DateTime<='2013-5-15' ) select local,bts_name,Energy,Energy_Ext,convert(varchar(10),DateTime,120) as Dt,stype into #t from tb a join ( select min(orderid) as orderid,0 as stype from tb group by local,bts_name union all select max(orderid) as orderid,1 as stype from tb group by local,bts_name ) b on a.orderid=b.orderid select a.local,a.bts_name, a.Dt as StartTime,b.Dt as EndTime, a.Energy as StartEnergy,b.Energy as EndEnergy , a.Energy_Ext as StartEnergy_Ext,b.Energy_Ext as EndEnergy_Ext from #t a join #t b on a.local=b.local and a.bts_name=b.bts_name and a.stype+1=b.stype
;with tb as ( select local,city,bsc,bts_name,Energy,Energy_Ext,DateTime ,ROW_NUMBER()over(order by DateTime) as orderid from ( select a.local,a.city,a.bsc,a.bts_name,Energy,Energy_Ext,b.DateTime From LocalBscBts a join EnergyTab_Ext b on a.ID=b.BTSTab_ID where b.DateTime>='2013-05-10' and b.DateTime<='2013-5-15' union all select a.local,a.city,a.bsc,a.bts_name,Energy,Energy_Ext,b.DateTime From LocalBscBts a join EnergyTab_Ext b on a.ID=b.BTSTab_ID join ( select a.local,a.bts_name,max(b.DateTime) as DateTime From LocalBscBts a join EnergyTab_Ext b on a.ID=b.BTSTab_ID where b.DateTime>=dateadd(day,-2,'2013-05-10') and b.DateTime<'2013-05-10' group by a.local,a.bts_name ) c on a.local=c.local and a.bts_name=c.bts_name and b.DateTime=c.DateTime ) t ) select local,city,bsc,bts_name,Energy,Energy_Ext,convert(varchar(10),DateTime,120) as Dt,stype into #t from tb a join ( select min(orderid) as orderid,0 as stype from tb group by local,city,bsc,bts_name union all select max(orderid) as orderid,1 as stype from tb group by local,city,bsc,bts_name ) b on a.orderid=b.orderid
select a.local,a.city,a.bsc,a.bts_name, a.Dt as StartTime,b.Dt as EndTime, a.Energy as StartEnergy,b.Energy as EndEnergy , a.Energy_Ext as StartEnergy_Ext,b.Energy_Ext as EndEnergy_Ext from #t a join #t b on a.local=b.local and a.bts_name=b.bts_name and a.stype+1=b.stype
建立两个表LocalBscBts、EnergyTab_Ext,环境是SQL SERVER 2005
USE [ceshi]
GO
/****** 对象: Table [dbo].[collect] 脚本日期: 04/26/2014 08:42:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE LocalBscBts(
[Local] [varchar](30),
[City] [varchar](30) NOT NULL,
[LocalTabID] [bigint] NOT NULL,
[BSC] [varchar](30) NOT NULL,
[BSCTabID] [bigint] NOT NULL,
[BTS_Name] [varchar](30) NOT NULL,
[ID] [bigint] NOT NULL
) ON [PRIMARY]USE [ceshi]
GO
/****** 对象: Table [dbo].[EnergyTab_Ext] 脚本日期: 04/26/2014 09:39:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EnergyTab_Ext](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[BTSTab_ID] [bigint] NOT NULL,
[Energy] [float] NULL DEFAULT (0.0),
[Energy_Ext] [float] NULL DEFAULT (0.0),
[DateTime] [datetime] NULL,
[CollectType] [int] NULL DEFAULT (0),
) ON [PRIMARY]两张表的数据
use ceshi
go
insert into LocalBscBts values('区域1','城市11',11,'站点111',111,'基站1111',1111)
insert into LocalBscBts values('区域1','城市11',11,'站点111',111,'基站1112',1112)
insert into LocalBscBts values('区域1','城市11',11,'站点111',111,'基站1112',1113)
insert into LocalBscBts values('区域1','城市12',12,'站点121',121,'基站1211',1211)
insert into LocalBscBts values('区域1','城市12',12,'站点122',122,'基站1221',1221)
insert into LocalBscBts values('区域1','城市12',12,'站点123',123,'基站1231',1231)
insert into LocalBscBts values('区域1','城市13',13,'站点131',131,'基站1311',1311)
insert into LocalBscBts values('区域1','城市13',13,'站点132',132,'基站1321',1321)
insert into LocalBscBts values('区域1','城市13',13,'站点131',131,'基站1312',1312)
insert into LocalBscBts values('区域2','城市21',21,'站点211',211,'基站2111',2111)
insert into LocalBscBts values('区域2','城市21',21,'站点211',211,'基站2112',2112)
insert into LocalBscBts values('区域2','城市21',21,'站点211',211,'基站2112',2113)
insert into LocalBscBts values('区域2','城市22',22,'站点221',221,'基站2211',2211)
insert into LocalBscBts values('区域2','城市22',22,'站点222',222,'基站2221',2221)
insert into LocalBscBts values('区域2','城市22',22,'站点223',223,'基站2231',2231)
insert into LocalBscBts values('区域2','城市23',23,'站点231',231,'基站2311',2311)
insert into LocalBscBts values('区域2','城市23',23,'站点232',232,'基站2321',2321)
insert into LocalBscBts values('区域2','城市23',23,'站点231',231,'基站2312',2312)
go
use ceshi
go
insert into EnergyTab_Ext values(1111,1002,2003,'2013-5-1',1)
insert into EnergyTab_Ext values(1111,1004,2006,'2013-5-2',1)
insert into EnergyTab_Ext values(1111,1006,2008,'2013-5-3',1)
insert into EnergyTab_Ext values(1111,1008,2010,'2013-5-4',1)
insert into EnergyTab_Ext values(1111,1100,2020,'2013-5-10',1)
insert into EnergyTab_Ext values(1111,11002,2031,'2013-5-11',1)
insert into EnergyTab_Ext values(1111,11003,2038,'2013-5-12',1)
insert into EnergyTab_Ext values(1111,11006,2045,'2013-5-13',1)
insert into EnergyTab_Ext values(1111,11010,2070,'2013-5-15',1)
insert into EnergyTab_Ext values(1112,1015,2013,'2013-5-1',1)
insert into EnergyTab_Ext values(1112,1021,2026,'2013-5-7',1)
insert into EnergyTab_Ext values(1112,1044,2038,'2013-5-8',1)
insert into EnergyTab_Ext values(1112,1051,2040,'2013-5-9',1)
insert into EnergyTab_Ext values(1112,1054,2050,'2013-5-10',1)
insert into EnergyTab_Ext values(1112,1059,2051,'2013-5-11',1)
insert into EnergyTab_Ext values(1112,1063,2068,'2013-5-12',1)
insert into EnergyTab_Ext values(1112,1070,2075,'2013-5-13',1)
insert into EnergyTab_Ext values(1112,1075,2080,'2013-5-15',1)
go要求统计2013-5-10到2013-5-15号之间的所有属于区域1的基站的用电量(分别为Energy,Energy_Ext的差值),类似结果如下:Energy的起始值默认是从指定时间的前两天中统计出最新值,若前两天没有的话就从指定时间内统计
MAX(CASE t2.Datetime WHEN '2013-05-10 00:00:00' THEN CONVERT(varchar(100),t2.Datetime, 23) END) AS StartTime,
MAX(CASE t2.Datetime WHEN '2013-05-15 00:00:00' THEN CONVERT(varchar(100),t2.Datetime, 23) END) AS EndTime,
MAX(CASE t2.Datetime WHEN '2013-05-10 00:00:00' THEN t2.Energy END) AS StartEnergy,
MAX(CASE t2.Datetime WHEN '2013-05-15 00:00:00' THEN t2.Energy END) AS EndEnergy,
MAX(CASE t2.Datetime WHEN '2013-05-10 00:00:00' THEN t2.Energy_Ext END) AS StartEnergy_Ext,
MAX(CASE t2.Datetime WHEN '2013-05-15 00:00:00' THEN t2.Energy_Ext END) AS EndEnergy_Ext
FROM LocalBscBts AS t1 INNER JOIN EnergyTab_Ext AS t2 ON t1.ID = t2.BTSTab_ID
WHERE datetime = '2013-05-10 00:00:00' OR datetime= '2013-05-15 00:00:00'
GROUP BY t1.LOCAL,t1.city,t1.BSC,t1.BTS_Name
LOCAL city BSC BTS_Name StartTime EndTime StartEnergy EndEnergy StartEnergy_Ext EndEnergy_Ext
区域1 城市11 站点111 基站1111 2013-05-10 2013-05-15 1100 11010 2020 2070
区域1 城市11 站点111 基站1112 2013-05-10 2013-05-15 1054 1075 2050 2080
你图里居然还有9号的数据;with tb as
(
select a.local,a.bts_name,Energy,Energy_Ext,b.DateTime ,ROW_NUMBER() over(order by DateTime) as orderid
From LocalBscBts a join EnergyTab_Ext b on a.ID=b.BTSTab_ID
where b.DateTime>='2013-05-10'
and b.DateTime<='2013-5-15'
)
select local,bts_name,Energy,Energy_Ext,convert(varchar(10),DateTime,120) as Dt,stype into #t
from tb a
join (
select min(orderid) as orderid,0 as stype from tb group by local,bts_name
union all select max(orderid) as orderid,1 as stype from tb group by local,bts_name
) b on a.orderid=b.orderid select a.local,a.bts_name,
a.Dt as StartTime,b.Dt as EndTime,
a.Energy as StartEnergy,b.Energy as EndEnergy ,
a.Energy_Ext as StartEnergy_Ext,b.Energy_Ext as EndEnergy_Ext from
#t a join #t b on a.local=b.local and a.bts_name=b.bts_name and a.stype+1=b.stype
drop table #t
;with tb as
(
select local,city,bsc,bts_name,Energy,Energy_Ext,DateTime ,ROW_NUMBER()over(order by DateTime) as orderid
from
(
select a.local,a.city,a.bsc,a.bts_name,Energy,Energy_Ext,b.DateTime
From LocalBscBts a join EnergyTab_Ext b on a.ID=b.BTSTab_ID
where b.DateTime>='2013-05-10'
and b.DateTime<='2013-5-15'
union all
select a.local,a.city,a.bsc,a.bts_name,Energy,Energy_Ext,b.DateTime
From LocalBscBts a join EnergyTab_Ext b on a.ID=b.BTSTab_ID
join (
select a.local,a.bts_name,max(b.DateTime) as DateTime
From LocalBscBts a join EnergyTab_Ext b on a.ID=b.BTSTab_ID
where b.DateTime>=dateadd(day,-2,'2013-05-10')
and b.DateTime<'2013-05-10'
group by a.local,a.bts_name
) c on a.local=c.local and a.bts_name=c.bts_name and b.DateTime=c.DateTime
) t
)
select local,city,bsc,bts_name,Energy,Energy_Ext,convert(varchar(10),DateTime,120) as Dt,stype into #t
from tb a
join (
select min(orderid) as orderid,0 as stype from tb group by local,city,bsc,bts_name
union all select max(orderid) as orderid,1 as stype from tb group by local,city,bsc,bts_name
) b on a.orderid=b.orderid
select a.local,a.city,a.bsc,a.bts_name,
a.Dt as StartTime,b.Dt as EndTime,
a.Energy as StartEnergy,b.Energy as EndEnergy ,
a.Energy_Ext as StartEnergy_Ext,b.Energy_Ext as EndEnergy_Ext from
#t a join #t b on a.local=b.local and a.bts_name=b.bts_name and a.stype+1=b.stype
drop table #t