--生成测试数据
create talbe t(
时间 datetime,
出入 int,
地点 varchar(20),
入金额 numeric(10,4),
入所占百分比 numeric(10,4),
出金额 numeric(10,4),
出所占百分比 numeric(10,4))insert into t select cast('2002-11-12 00:00:00' as datetime,1,'北京',NULL ,NULL ,30.3000,48.2600
insert into t select cast('2002-11-12 00:00:00' as datetime,1,'上海',NULL ,NULL ,23.1400,36.8500
insert into t select cast('2002-11-12 00:00:00' as datetime,0,'北京',32.8000,52.2400,NULL ,NULL
insert into t select cast('2002-11-12 00:00:00' as datetime,0,'上海',23.1400,36.8500,NULL ,NULL
insert into t select cast('2002-11-13 00:00:00' as datetime,1,'上海',NULL ,NULL ,15.3400,47.6200
insert into t select cast('2002-11-13 00:00:00' as datetime,1,'北京',NULL ,NULL ,8.5000 ,26.3900
insert into t select cast('2002-11-13 00:00:00' as datetime,0,'上海',15.2400,47.3100,NULL ,NULL
insert into t select cast('2002-11-13 00:00:00' as datetime,0,'北京',12.1000,37.5700,NULL ,NULL--执行查询
select
identity(int,1,1) as id,
0 as newid,
a.*
into
#t
from
t a
where
a.地点 in (select
top 3 地点
from
t
where
时间 = a.时间 and 出入 = a.出入
order by
入金额 desc,出金额 desc)update
a
set
a.newid = (a.id - b.id + 1)
from
#t a,
(select 出入,时间,min(id) as id from #t group by 出入,时间) b
where
a.出入 = b.出入 and a.时间 = b.时间select
时间,
出第一名地区 = max(case when newid = 1 and (出入 = 1) then 地点 end),
出金额 = max(case when newid = 1 and (出入 = 1) then 出金额 end),
入金额 = max(case when newid = 1 and (出入 = 1) then 入金额 end),
出第二名地区 = max(case when newid = 2 and (出入 = 1) then 地点 end),
出金额 = max(case when newid = 2 and (出入 = 1) then 出金额 end),
入金额 = max(case when newid = 2 and (出入 = 1) then 入金额 end),
出第三名地区 = max(case when newid = 3 and (出入 = 1) then 地点 end),
出金额 = max(case when newid = 3 and (出入 = 1) then 出金额 end),
入金额 = max(case when newid = 3 and (出入 = 1) then 入金额 end),
出第一名地区 = max(case when newid = 1 and (出入 = 0) then 地点 end),
出金额 = max(case when newid = 1 and (出入 = 0) then 出金额 end),
入金额 = max(case when newid = 1 and (出入 = 0) then 入金额 end),
出第二名地区 = max(case when newid = 2 and (出入 = 0) then 地点 end),
出金额 = max(case when newid = 2 and (出入 = 0) then 出金额 end),
入金额 = max(case when newid = 2 and (出入 = 0) then 入金额 end),
出第三名地区 = max(case when newid = 3 and (出入 = 0) then 地点 end),
出金额 = max(case when newid = 3 and (出入 = 0) then 出金额 end),
入金额 = max(case when newid = 3 and (出入 = 0) then 入金额 end)
from #t
group by 时间
group by 时间
create talbe t(
时间 datetime,
出入 int,
地点 varchar(20),
入金额 numeric(10,4),
入所占百分比 numeric(10,4),
出金额 numeric(10,4),
出所占百分比 numeric(10,4))insert into t select cast('2002-11-12 00:00:00' as datetime,1,'北京',NULL ,NULL ,30.3000,48.2600
insert into t select cast('2002-11-12 00:00:00' as datetime,1,'上海',NULL ,NULL ,23.1400,36.8500
insert into t select cast('2002-11-12 00:00:00' as datetime,0,'北京',32.8000,52.2400,NULL ,NULL
insert into t select cast('2002-11-12 00:00:00' as datetime,0,'上海',23.1400,36.8500,NULL ,NULL
insert into t select cast('2002-11-13 00:00:00' as datetime,1,'上海',NULL ,NULL ,15.3400,47.6200
insert into t select cast('2002-11-13 00:00:00' as datetime,1,'北京',NULL ,NULL ,8.5000 ,26.3900
insert into t select cast('2002-11-13 00:00:00' as datetime,0,'上海',15.2400,47.3100,NULL ,NULL
insert into t select cast('2002-11-13 00:00:00' as datetime,0,'北京',12.1000,37.5700,NULL ,NULL--执行查询
select
identity(int,1,1) as id,
0 as newid,
a.*
into
#t
from
t a
where
a.地点 in (select
top 3 地点
from
t
where
时间 = a.时间 and 出入 = a.出入
order by
入金额 desc,出金额 desc)update
a
set
a.newid = (a.id - b.id + 1)
from
#t a,
(select 出入,时间,min(id) as id from #t group by 出入,时间) b
where
a.出入 = b.出入 and a.时间 = b.时间select
时间,
出第一名地区 = max(case when newid = 1 and (出入 = 1) then 地点 end),
出金额 = max(case when newid = 1 and (出入 = 1) then 出金额 end),
入金额 = max(case when newid = 1 and (出入 = 1) then 入金额 end),
出第二名地区 = max(case when newid = 2 and (出入 = 1) then 地点 end),
出金额 = max(case when newid = 2 and (出入 = 1) then 出金额 end),
入金额 = max(case when newid = 2 and (出入 = 1) then 入金额 end),
出第三名地区 = max(case when newid = 3 and (出入 = 1) then 地点 end),
出金额 = max(case when newid = 3 and (出入 = 1) then 出金额 end),
入金额 = max(case when newid = 3 and (出入 = 1) then 入金额 end),
出第一名地区 = max(case when newid = 1 and (出入 = 0) then 地点 end),
出金额 = max(case when newid = 1 and (出入 = 0) then 出金额 end),
入金额 = max(case when newid = 1 and (出入 = 0) then 入金额 end),
出第二名地区 = max(case when newid = 2 and (出入 = 0) then 地点 end),
出金额 = max(case when newid = 2 and (出入 = 0) then 出金额 end),
入金额 = max(case when newid = 2 and (出入 = 0) then 入金额 end),
出第三名地区 = max(case when newid = 3 and (出入 = 0) then 地点 end),
出金额 = max(case when newid = 3 and (出入 = 0) then 出金额 end),
入金额 = max(case when newid = 3 and (出入 = 0) then 入金额 end)
from #t
group by 时间
group by 时间
解决方案 »
- 求SQL2005电子书籍 谢谢
- 轉移到歷史數據庫最好的方案是什麼?
- 怎么把多行 的数据归整到一行?
- sqlserver2005安装问题
- 一个表数据记录多,一个表数据记录少,后一个是前一个子集,怎么用一句sql得出后一个表少了哪些记录
- 在sql2005中创建全文目录失败?
- 一个简单问题,在线等待,急
- 语句的执行速度太慢了,有谁出个好主意,100分送上了。
- 请教:存储过程产生的数据记录如何输出到一个临时表?
- sos在线急救 一个时间转换的问题 convert函数使用的方法 Sql sever帮助我看了 没看懂
- 为什么每次重新运行程序都要新创建若干连接而不是从连接池中获取连接
- 求一个SQL语句,看谁的方法最好!高手请指教
insert ##tb
select '2002-11-12 00:00:00','1', N'北京', 0, 0, 30.3000, 48.2600 union
select '2002-11-12 00:00:00','1', N'上海', 0, 0, 23.1400, 36.8500 union
select '2002-11-12 00:00:00','1', N'广州', 0, 0, 10.1400, 10.8500 union
select '2002-11-12 00:00:00','0', N'北京', 32.8000, 52.2400, 0, 0 union
select '2002-11-12 00:00:00','0', N'上海', 23.1400, 36.8500, 0, 0 union
select '2002-11-12 00:00:00','0', N'广州', 10.1400, 10.8500, 0, 0 union
select '2002-11-13 00:00:00','1', N'上海', 0, 0, 15.3400, 47.6200 union
select '2002-11-13 00:00:00','1', N'北京', 0, 0, 8.5000, 26.3900 union
select '2002-11-13 00:00:00','0', N'上海', 15.2400, 47.3100, 0, 0 union
select '2002-11-13 00:00:00','0', N'北京', 12.1000, 37.5700, 0, 0
select Date,Max(InCost) as InCost into ##In1 from ##tb where OutOrIn = 0 group by Date -- 进第一名
select Date,Max(InCost) as InCost into ##In2 from ##tb where OutOrIn = 0 and Cast(Date as varchar(20))+cast(InCost as varchar(20)) not in (select Cast(Date as varchar(20))+cast(InCost as varchar(20)) from ##In1) group by Date -- 进第二名
select Date,Max(InCost) as InCost into ##In3 from ##tb where OutOrIn = 0 and Cast(Date as varchar(20))+cast(InCost as varchar(20)) not in (select Cast(Date as varchar(20))+cast(InCost as varchar(20)) from ##In1 union select Cast(Date as varchar(20))+cast(InCost as varchar(20)) from ##In2) group by Date -- 进第三名select Date,Max(OutCost) as OutCost into ##out1 from ##tb where OutOrIn = 1 group by Date -- 出第一名
select Date,Max(OutCost) as OutCost into ##out2 from ##tb where OutOrIn = 1 and Cast(Date as varchar(20))+cast(OutCost as varchar(20)) not in (select Cast(Date as varchar(20))+cast(OutCost as varchar(20)) from ##out1) group by Date -- 出第二名
select Date,Max(OutCost) as OutCost into ##out3 from ##tb where OutOrIn = 1 and Cast(Date as varchar(20))+cast(OutCost as varchar(20)) not in (select Cast(Date as varchar(20))+cast(OutCost as varchar(20)) from ##out1 union select Cast(Date as varchar(20))+cast(OutCost as varchar(20)) from ##out2) group by Date -- 出第三名select t.Date,
o1.Location as FirstOut,o1.OutCost,0 as InCost,
o2.Location as SecondOut,o1.OutCost,0 as InCost,
o3.Location as ThirdOut,o1.OutCost,0 as InCost,
i1.Location as FirstIn,0 as OutCost,i1.InCost,
i2.Location as SecondIn,0 as OutCost,i2.InCost,
i3.Location as ThirdIn,0 as OutCost,i3.InCost
from
(select distinct date from ##tb) t
left join (select i1.*,t.Location from ##in1 i1 left join ##tb t on i1.Date = t.Date and i1.InCost = t.InCost) i1 on i1.Date = t.Date
left join (select i2.*,t.Location from ##in2 i2 left join ##tb t on i2.Date = t.Date and i2.InCost = t.InCost) i2 on i2.Date = t.Date
left join (select i3.*,t.Location from ##in3 i3 left join ##tb t on i3.Date = t.Date and i3.InCost = t.InCost) i3 on i3.Date = t.Date
left join (select o1.*,t.Location from ##out1 o1 left join ##tb t on o1.Date = t.Date and o1.OutCost = t.OutCost) o1 on o1.Date = t.Date
left join (select o2.*,t.Location from ##out2 o2 left join ##tb t on o2.Date = t.Date and o2.OutCost = t.OutCost) o2 on o2.Date = t.Date
left join (select o3.*,t.Location from ##out3 o3 left join ##tb t on o3.Date = t.Date and o3.OutCost = t.OutCost) o3 on o3.Date = t.Date
insert into a
select '2002-11-12 00:00:00',1, '北京', NULL, NULL,30.3000,48.2600 union all
select '2002-11-12 00:00:00' ,1, '上海', NULL, NULL,23.1400,36.8500 union all
select '2002-11-12 00:00:00' ,0 , '北京',32.8000 ,52.2400,NULL, NULL union all
select '2002-11-12 00:00:00' ,0 ,'上海', 23.1400,36.8500,NULL,NULL
----------------------------下面執行語句------------------------------------------------
Create Proc kkb
as
select *,bb=0 into #a from a order by bdate,cr -------
Declare @i int,@p datetime,@k int
set @k=0
set @i=0
update #a set bb=@i,@i=(case when (@p=bdate and @k=cr) or @p is null then @i+1 else 1 end),@p=bdate,@k=cr
select 时间=bdate,出第一名地区=(select adr from #a where bdate=a.bdate and bb=1 and cr=1),
出金额=(select cm from #a where bdate=a.bdate and bb=1 and cr=1),
入金额=(select rm from #a where bdate=a.bdate and bb=1 and cr=1),
出第二名地区=(select adr from #a where bdate=a.bdate and bb=2 and cr=1),
出金额=(select cm from #a where bdate=a.bdate and bb=2 and cr=1),
入金额=(select rm from #a where bdate=a.bdate and bb=2 and cr=1),
出第三名地区=(select adr from #a where bdate=a.bdate and bb=3 and cr=1),
出金额=(select cm from #a where bdate=a.bdate and bb=3 and cr=1),
入金额=(select rm from #a where bdate=a.bdate and bb=3 and cr=1),
入第一名地区=(select adr from #a where bdate=a.bdate and bb=1 and cr=0),
出金额=(select cm from #a where bdate=a.bdate and bb=1 and cr=0),
入金额=(select rm from #a where bdate=a.bdate and bb=1 and cr=0),
入第二名地区=(select adr from #a where bdate=a.bdate and bb=2 and cr=0),
出金额=(select cm from #a where bdate=a.bdate and bb=2 and cr=0),
入金额=(select rm from #a where bdate=a.bdate and bb=2 and cr=0),
入第三名地区=(select adr from #a where bdate=a.bdate and bb=3 and cr=0),
出金额=(select cm from #a where bdate=a.bdate and bb=3 and cr=0),
入金额=(select rm from #a where bdate=a.bdate and bb=3 and cr=0) from a group by bdate
drop table #a
----------------------------End 過程---------------------------------------
exec kkb ---執行過程
----------------------結果-------------------------------------------------
时间 出第一名地区 出金额 入金额 出第二名地区 出金额 入金额 出第三名地区 出金额 入金额 入第一名地区 入金额 出金额 入第二名地区 入金额 出金额 入第三名地区 入金额 出金额
2002-11-12 00:00:00 北京 30.3000 null 上海 23.1400 null null null 北京 32.8000 null 上海 23.1400
create talbe t(
时间 datetime,
出入 int,
地点 varchar(20),
入金额 numeric(10,4),
入所占百分比 numeric(10,4),
出金额 numeric(10,4),
出所占百分比 numeric(10,4))insert into t select cast('2002-11-12 00:00:00' as datetime,1,'北京',NULL ,NULL ,30.3000,48.2600
insert into t select cast('2002-11-12 00:00:00' as datetime,1,'上海',NULL ,NULL ,23.1400,36.8500
insert into t select cast('2002-11-12 00:00:00' as datetime,0,'北京',32.8000,52.2400,NULL ,NULL
insert into t select cast('2002-11-12 00:00:00' as datetime,0,'上海',23.1400,36.8500,NULL ,NULL
insert into t select cast('2002-11-13 00:00:00' as datetime,1,'上海',NULL ,NULL ,15.3400,47.6200
insert into t select cast('2002-11-13 00:00:00' as datetime,1,'北京',NULL ,NULL ,8.5000 ,26.3900
insert into t select cast('2002-11-13 00:00:00' as datetime,0,'上海',15.2400,47.3100,NULL ,NULL
insert into t select cast('2002-11-13 00:00:00' as datetime,0,'北京',12.1000,37.5700,NULL ,NULL--执行查询
select
identity(int,1,1) as id,
0 as newid,
a.*
into
#t
from
t a
where
a.地点 in (select
top 3 地点
from
t
where
时间 = a.时间 and 出入 = a.出入
order by
入金额 desc,出金额 desc)update
a
set
a.newid = (a.id - b.id + 1)
from
#t a,
(select 出入,时间,min(id) as id from #t group by 出入,时间) b
where
a.出入 = b.出入 and a.时间 = b.时间select
时间,
出第一名地区 = max(case when newid = 1 and (出入 = 1) then 地点 end),
出金额 = max(case when newid = 1 and (出入 = 1) then 出金额 end),
入金额 = max(case when newid = 1 and (出入 = 1) then 入金额 end),
出第二名地区 = max(case when newid = 2 and (出入 = 1) then 地点 end),
出金额 = max(case when newid = 2 and (出入 = 1) then 出金额 end),
入金额 = max(case when newid = 2 and (出入 = 1) then 入金额 end),
出第三名地区 = max(case when newid = 3 and (出入 = 1) then 地点 end),
出金额 = max(case when newid = 3 and (出入 = 1) then 出金额 end),
入金额 = max(case when newid = 3 and (出入 = 1) then 入金额 end),
入第一名地区 = max(case when newid = 1 and (出入 = 0) then 地点 end),
出金额 = max(case when newid = 1 and (出入 = 0) then 出金额 end),
入金额 = max(case when newid = 1 and (出入 = 0) then 入金额 end),
入第二名地区 = max(case when newid = 2 and (出入 = 0) then 地点 end),
出金额 = max(case when newid = 2 and (出入 = 0) then 出金额 end),
入金额 = max(case when newid = 2 and (出入 = 0) then 入金额 end),
入第三名地区 = max(case when newid = 3 and (出入 = 0) then 地点 end),
出金额 = max(case when newid = 3 and (出入 = 0) then 出金额 end),
入金额 = max(case when newid = 3 and (出入 = 0) then 入金额 end)
from #t
group by 时间
group by 时间