有表table1,表中数据如下
table1
Price Date
1 2006-5-1
1 2006-5-2
1.5 2006-5-3
1.5 2006-5-4
1.5 2006-5-6
1 2006-5-7
结果:
TOTAL
2 2006-5-1-----2006-5-2
4.5 2006-5-3-----2006-5-6
1 2006-5-5-----2006-5-5
27 个解决方案
#1
select
sum(case when date between() then price else 0 end ) as ''
from table
sum(case when date between() then price else 0 end ) as ''
from table
#2
declare @t table (price float,date varchar(50))
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6'
select * from @t
select sum(price)as total,(min(date)+'----'+max(date))as 时间段
from @t
group by price
不知道你后面那个1怎么搞。期待高手
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6'
select * from @t
select sum(price)as total,(min(date)+'----'+max(date))as 时间段
from @t
group by price
不知道你后面那个1怎么搞。期待高手
#3
select sum(price),min(date)+'------'+max(date)
from table1 group by price
from table1 group by price
#4
你的例子有問題哦 . 5-5 的 1 是怎樣出來的.
#5
最後一條
1 2006-5-5-----2006-5-5
從哪里來的?
1 2006-5-5-----2006-5-5
從哪里來的?
#6
看不出规律 尤其是
1 2006-5-5-----2006-5-5
说明一下
1 2006-5-5-----2006-5-5
说明一下
#7
我想lz的意思是按照时间排序再分组。但这样怎么做?没法用group了
#8
应当是
1 2006-5-7-----2006-5-7
吧,lz?
1 2006-5-7-----2006-5-7
吧,lz?
#9
select sum(price) , min(date) + '--------' + max(date) from t
group by date
group by date
#10
declare @t table(price float,date nvarchar(50))
insert into @t select 1.5,'2006-5-3'
union select 1,'2006-5-1'
union select 1,'2006-5-2'
union select 1.5,'2006-5-4'
union select 1.5,'2006-5-6'
union select 1,'2006-5-7'
select sum(price),case when cast(date as datetime) between '2006-5-1' and '2006-5-2' then '2006-5-1到2006-5-2' when cast(date as datetime)
between '2006-5-3' and '2006-5-6' then '2006-5-3到2006-5-6' when cast(date as datetime)='2006-5-7' then '2006-5-7' end
from @t group by case when cast(date as datetime) between '2006-5-1' and '2006-5-2' then '2006-5-1到2006-5-2' when cast(date as datetime)
between '2006-5-3' and '2006-5-6' then '2006-5-3到2006-5-6' when cast(date as datetime)='2006-5-7' then '2006-5-7' end
insert into @t select 1.5,'2006-5-3'
union select 1,'2006-5-1'
union select 1,'2006-5-2'
union select 1.5,'2006-5-4'
union select 1.5,'2006-5-6'
union select 1,'2006-5-7'
select sum(price),case when cast(date as datetime) between '2006-5-1' and '2006-5-2' then '2006-5-1到2006-5-2' when cast(date as datetime)
between '2006-5-3' and '2006-5-6' then '2006-5-3到2006-5-6' when cast(date as datetime)='2006-5-7' then '2006-5-7' end
from @t group by case when cast(date as datetime) between '2006-5-1' and '2006-5-2' then '2006-5-1到2006-5-2' when cast(date as datetime)
between '2006-5-3' and '2006-5-6' then '2006-5-3到2006-5-6' when cast(date as datetime)='2006-5-7' then '2006-5-7' end
#11
輸入
1, '2006-5-1'
1, '2006-5-2'
1, '2006-5-3'
1.5, '2006-5-4'
1.5, '2006-5-5'
1.5, '2006-5-6'
1.5, '2006-5-7'
1, '2006-5-8'
1.5, '2006-5-9'
1.5, '2006-5-10'
結果
3.0 05-1-2006----05-3-2006
6.0 05-4-2006----05-7-2006
1.0 05-8-2006----05-8-2006
3.0 05-9-2006----05-10-2006
declare @t table (price float,datee datetime)
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-5' union all select
1.5, '2006-5-6' union all select
1.5, '2006-5-7' union all select
1, '2006-5-8' union all select
1.5, '2006-5-9' union all select
1.5, '2006-5-10'
select *, idnb= identity(int,1,1) into #temp1 from @t a where not exists(select 1 from @t where a.price=price and a.datee>datee and a.datee-datee=1)
select *, idnb= identity(int,1,1) into #temp2 from @t a where not exists(select 1 from @t where a.price=price and a.datee<datee and datee-a.datee=1)
select (select sum(price) from @t where datee between a.datee and b.datee) total
,cast(a.datee as nvarchar(10))+'----'+cast(b.datee as nvarchar(10))
from #temp1 a join #temp2 b on a.idnb=b.idnb
drop table #temp1
drop table #temp2
1, '2006-5-1'
1, '2006-5-2'
1, '2006-5-3'
1.5, '2006-5-4'
1.5, '2006-5-5'
1.5, '2006-5-6'
1.5, '2006-5-7'
1, '2006-5-8'
1.5, '2006-5-9'
1.5, '2006-5-10'
結果
3.0 05-1-2006----05-3-2006
6.0 05-4-2006----05-7-2006
1.0 05-8-2006----05-8-2006
3.0 05-9-2006----05-10-2006
declare @t table (price float,datee datetime)
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-5' union all select
1.5, '2006-5-6' union all select
1.5, '2006-5-7' union all select
1, '2006-5-8' union all select
1.5, '2006-5-9' union all select
1.5, '2006-5-10'
select *, idnb= identity(int,1,1) into #temp1 from @t a where not exists(select 1 from @t where a.price=price and a.datee>datee and a.datee-datee=1)
select *, idnb= identity(int,1,1) into #temp2 from @t a where not exists(select 1 from @t where a.price=price and a.datee<datee and datee-a.datee=1)
select (select sum(price) from @t where datee between a.datee and b.datee) total
,cast(a.datee as nvarchar(10))+'----'+cast(b.datee as nvarchar(10))
from #temp1 a join #temp2 b on a.idnb=b.idnb
drop table #temp1
drop table #temp2
#12
楼上的有Bug-_-
你在数据里少输入一行
1.5, '2006-5-5'
试试就知道了
你在数据里少输入一行
1.5, '2006-5-5'
试试就知道了
#13
--完成了-_-
--测试环境
declare @t table (price float,date varchar(50))
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7' union all select
2, '2006-5-8' union all select
2, '2006-5-10'
--一下开始执行
select identity(int,1,1)as Idx,*
into #temp
from @t t
order by convert(datetime,date)
insert into #temp
select max(price),max(date)
from #temp
select *,
isnull((select case when price<>t.price then 1 else 0 end as aa from #temp where idx=t.idx-1),1)v
into #temp1
from #temp t
select 金额,期间
from
(
select sum((endidx-idx+1)*price) as 金额,(begindate+' 到 '+enddate) as 期间
from
(
select idx,price,
left(convert(varchar,date,120),10) as begindate
,(isnull((
select max(t2.idx) from #temp t2 where t2.idx>t1.idx and t2.idx<(select min(idx) from #temp where v=1 and idx>t2.idx) and price=t1.price
),idx)
) as endidx
,
(
isnull((
select max(date) from #temp t2 where t2.idx>t1.idx and t2.idx<(select min(idx) from #temp where v=1 and idx>t2.idx) and price=t1.price
),date)
) as enddate
from #temp1 t1
where v=1
)a
group by (begindate+' 到 '+enddate)
)b
order by convert(datetime,left(期间,charindex(期间,' ')))
--清理测试环境
drop table #temp
drop table #temp1
/*结果
金额 期间
-------------------------------------------- ------------------------------6.0 2006-5-1 到 2006-5-7
4.5 2006-5-3 到 2006-5-6
1.0 2006-5-7 到 2006-5-7
4.0 2006-5-8 到 2006-5-10
*/
--测试环境
declare @t table (price float,date varchar(50))
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7' union all select
2, '2006-5-8' union all select
2, '2006-5-10'
--一下开始执行
select identity(int,1,1)as Idx,*
into #temp
from @t t
order by convert(datetime,date)
insert into #temp
select max(price),max(date)
from #temp
select *,
isnull((select case when price<>t.price then 1 else 0 end as aa from #temp where idx=t.idx-1),1)v
into #temp1
from #temp t
select 金额,期间
from
(
select sum((endidx-idx+1)*price) as 金额,(begindate+' 到 '+enddate) as 期间
from
(
select idx,price,
left(convert(varchar,date,120),10) as begindate
,(isnull((
select max(t2.idx) from #temp t2 where t2.idx>t1.idx and t2.idx<(select min(idx) from #temp where v=1 and idx>t2.idx) and price=t1.price
),idx)
) as endidx
,
(
isnull((
select max(date) from #temp t2 where t2.idx>t1.idx and t2.idx<(select min(idx) from #temp where v=1 and idx>t2.idx) and price=t1.price
),date)
) as enddate
from #temp1 t1
where v=1
)a
group by (begindate+' 到 '+enddate)
)b
order by convert(datetime,left(期间,charindex(期间,' ')))
--清理测试环境
drop table #temp
drop table #temp1
/*结果
金额 期间
-------------------------------------------- ------------------------------6.0 2006-5-1 到 2006-5-7
4.5 2006-5-3 到 2006-5-6
1.0 2006-5-7 到 2006-5-7
4.0 2006-5-8 到 2006-5-10
*/
#14
还是有Bug-_-
#15
谢谢大家的热情参与,对不起,以下才是正确的示例
Price Date
1 2006-5-1
1 2006-5-2
1.5 2006-5-3
1.5 2006-5-4
1.5 2006-5-6
1 2006-5-7
结果:
TOTAL
2 2006-5-1-----2006-5-2
4.5 2006-5-3-----2006-5-6
1 2006-5-7-----2006-5-7
Price Date
1 2006-5-1
1 2006-5-2
1.5 2006-5-3
1.5 2006-5-4
1.5 2006-5-6
1 2006-5-7
结果:
TOTAL
2 2006-5-1-----2006-5-2
4.5 2006-5-3-----2006-5-6
1 2006-5-7-----2006-5-7
#16
实在看不出规律
最后一条为什么不与前三条一起参与运算. 难道记录的位置也是因素?
最后一条为什么不与前三条一起参与运算. 难道记录的位置也是因素?
#17
看来真的是好有难度
多谢谢大家,继续。。。。。
多谢谢大家,继续。。。。。
#18
最后一条为什么不与前三条一起参与运算. 难道记录的位置也是因素?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如果合在一起算,就反应不出价格的波动时间段了
所有数据都是按日期排好序的(不一定连续)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如果合在一起算,就反应不出价格的波动时间段了
所有数据都是按日期排好序的(不一定连续)
#19
select
Price*count(*),
cast(min(Date) as varchar(20)) + '----' + cast(max(Date) as varchar(20))
from
tb
group by
Price
Price*count(*),
cast(min(Date) as varchar(20)) + '----' + cast(max(Date) as varchar(20))
from
tb
group by
Price
#20
看错题了
#21
declare @t table
(price float,
[date] datetime)
insert into @t
select 1,'2006-5-1'
union all select 1,'2006-5-2'
union all select 1.5,'2006-5-3'
union all select 1.5,'2006-5-4'
union all select 1.5,'2006-5-6'
union all select 1,'2006-5-7'
declare @price float
declare @date datetime
declare @sql varchar(8000)
declare @lastval float, @totalval float
declare @datestr varchar(100), @lastdatestr varchar(100)
declare @flag int
set @sql=''
set @lastval = 0.01
set @datestr = ''
set @flag=0
declare cur cursor for select price,[date] from @t order by [date]
open cur
fetch next from cur into @price,@date
while @@fetch_status = 0
begin
if @price<>@lastval
begin
if @flag=0
begin
set @flag=1
set @datestr = convert(varchar(20),@date,102)
set @lastdatestr = @datestr
set @lastval = @price
set @totalval = @price
end
else
begin
select @datestr = @datestr + '----' + @lastdatestr
select @sql = @sql + ' select '+cast(@totalval as varchar(10))+ ' a,''' + @datestr + ''' b union'
set @datestr = convert(varchar(20),@date,102)
set @lastdatestr = @datestr
set @lastval = @price
set @totalval = @price
end
end
else
begin
select @totalval = @totalval + @price
set @lastdatestr = convert(varchar(20),@date,102)
end
fetch next from cur into @price,@date
end
select @datestr = @datestr + '----' + @lastdatestr
select @sql = @sql + ' select '+cast(@totalval as varchar(10))+ ',''' + @datestr + ''' union'
close cur
deallocate cur
select @sql = 'select * from (' + left(@sql, len(@sql)-5) + ') tb order by tb.b'
exec(@sql)
/*
2.0 2006.05.01----2006.05.02
4.5 2006.05.03----2006.05.06
1.0 2006.05.07----2006.05.07
*/
(price float,
[date] datetime)
insert into @t
select 1,'2006-5-1'
union all select 1,'2006-5-2'
union all select 1.5,'2006-5-3'
union all select 1.5,'2006-5-4'
union all select 1.5,'2006-5-6'
union all select 1,'2006-5-7'
declare @price float
declare @date datetime
declare @sql varchar(8000)
declare @lastval float, @totalval float
declare @datestr varchar(100), @lastdatestr varchar(100)
declare @flag int
set @sql=''
set @lastval = 0.01
set @datestr = ''
set @flag=0
declare cur cursor for select price,[date] from @t order by [date]
open cur
fetch next from cur into @price,@date
while @@fetch_status = 0
begin
if @price<>@lastval
begin
if @flag=0
begin
set @flag=1
set @datestr = convert(varchar(20),@date,102)
set @lastdatestr = @datestr
set @lastval = @price
set @totalval = @price
end
else
begin
select @datestr = @datestr + '----' + @lastdatestr
select @sql = @sql + ' select '+cast(@totalval as varchar(10))+ ' a,''' + @datestr + ''' b union'
set @datestr = convert(varchar(20),@date,102)
set @lastdatestr = @datestr
set @lastval = @price
set @totalval = @price
end
end
else
begin
select @totalval = @totalval + @price
set @lastdatestr = convert(varchar(20),@date,102)
end
fetch next from cur into @price,@date
end
select @datestr = @datestr + '----' + @lastdatestr
select @sql = @sql + ' select '+cast(@totalval as varchar(10))+ ',''' + @datestr + ''' union'
close cur
deallocate cur
select @sql = 'select * from (' + left(@sql, len(@sql)-5) + ') tb order by tb.b'
exec(@sql)
/*
2.0 2006.05.01----2006.05.02
4.5 2006.05.03----2006.05.06
1.0 2006.05.07----2006.05.07
*/
#22
drop table T
create table T(price decimal(18,2),date datetime)
insert T
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7'
declare @T table (price decimal(18,2),T_date varchar(500))
Declare T_SC cursor for Select price,[date ] from T
Declare @price decimal(18,4)
Declare @date datetime
declare @T_price decimal(18,4)
declare @sum_price decimal(18,4)
declare @Max varchar(10)
declare @Min varchar(10)
open T_SC
set @T_price=0
set @sum_price=0
fetch next from T_SC into @price,@date
while @@FETCH_STATUS=0
begin
if @T_price=0
begin
set @T_price=@price
set @sum_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else if @price<>@T_price
begin
insert @T select @sum_price, @Min+'-----'+@Max
set @sum_price=@price
set @T_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else
begin
set @sum_price=@sum_price+@price
set @Max=convert(char(10),@date,120)
end
fetch next from T_SC into @price,@date
end
insert @T select @sum_price, @Min+'-----'+@Max
close T_SC
deallocate T_SC
create table T(price decimal(18,2),date datetime)
insert T
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7'
declare @T table (price decimal(18,2),T_date varchar(500))
Declare T_SC cursor for Select price,[date ] from T
Declare @price decimal(18,4)
Declare @date datetime
declare @T_price decimal(18,4)
declare @sum_price decimal(18,4)
declare @Max varchar(10)
declare @Min varchar(10)
open T_SC
set @T_price=0
set @sum_price=0
fetch next from T_SC into @price,@date
while @@FETCH_STATUS=0
begin
if @T_price=0
begin
set @T_price=@price
set @sum_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else if @price<>@T_price
begin
insert @T select @sum_price, @Min+'-----'+@Max
set @sum_price=@price
set @T_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else
begin
set @sum_price=@sum_price+@price
set @Max=convert(char(10),@date,120)
end
fetch next from T_SC into @price,@date
end
insert @T select @sum_price, @Min+'-----'+@Max
close T_SC
deallocate T_SC
#23
create table T(price decimal(18,2),date datetime)
insert T
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7'
declare @T table (price decimal(18,2),T_date varchar(500))
Declare T_SC cursor for Select price,[date ] from T
Declare @price decimal(18,4)
Declare @date datetime
declare @T_price decimal(18,4)
declare @sum_price decimal(18,4)
declare @Max varchar(10)
declare @Min varchar(10)
open T_SC
set @T_price=0
set @sum_price=0
fetch next from T_SC into @price,@date
while @@FETCH_STATUS=0
begin
if @T_price=0
begin
set @T_price=@price
set @sum_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else if @price<>@T_price
begin
insert @T select @sum_price, @Min+'-----'+@Max
set @sum_price=@price
set @T_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else
begin
set @sum_price=@sum_price+@price
set @Max=convert(char(10),@date,120)
end
fetch next from T_SC into @price,@date
end
insert @T select @sum_price, @Min+'-----'+@Max
close T_SC
deallocate T_SC
select * from @t
insert T
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7'
declare @T table (price decimal(18,2),T_date varchar(500))
Declare T_SC cursor for Select price,[date ] from T
Declare @price decimal(18,4)
Declare @date datetime
declare @T_price decimal(18,4)
declare @sum_price decimal(18,4)
declare @Max varchar(10)
declare @Min varchar(10)
open T_SC
set @T_price=0
set @sum_price=0
fetch next from T_SC into @price,@date
while @@FETCH_STATUS=0
begin
if @T_price=0
begin
set @T_price=@price
set @sum_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else if @price<>@T_price
begin
insert @T select @sum_price, @Min+'-----'+@Max
set @sum_price=@price
set @T_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else
begin
set @sum_price=@sum_price+@price
set @Max=convert(char(10),@date,120)
end
fetch next from T_SC into @price,@date
end
insert @T select @sum_price, @Min+'-----'+@Max
close T_SC
deallocate T_SC
select * from @t
#24
呵呵,代码惊人相似。
#25
楼主早说他意思不就好了.
生成临时数据
----------------------------
select
1 ,'2006-5-1' union all select
1 ,'2006-5-2' union all select
1.5 ,'2006-5-3' union all select
1.5 ,'2006-5-4' union all select
1.5 ,'2006-5-6' union all select
1 ,'2006-5-7' union all select
1 ,'2006-5-8' union all select
2 ,'2006-5-9'
存入临时表#tb,目的是生成唯一的标识号id,以便后面处理
-------------------------------------------------
select price,date,identity(int) id into #tb from @t
查询所需数据.
--------------------------------------------
select id,price,date,hasBefore=(select count(1) from #tb b where b.price=a.price and b.id=a.id-1) into #base from #tb a
select price,date=min(date) + '--' + max(date) from (select price ,date,far=(case hasBefore when 0 then id else (select max(x.id) from #base x where x.price=b.price and x.id<b.id and x.hasBefore=0) end) from #base b) base group by price,far
显示结果
-------------------------------
1.0 2006-5-1--2006-5-2
1.5 2006-5-3--2006-5-6
1.0 2006-5-7--2006-5-8
2.0 2006-5-9--2006-5-9
生成临时数据
----------------------------
select
1 ,'2006-5-1' union all select
1 ,'2006-5-2' union all select
1.5 ,'2006-5-3' union all select
1.5 ,'2006-5-4' union all select
1.5 ,'2006-5-6' union all select
1 ,'2006-5-7' union all select
1 ,'2006-5-8' union all select
2 ,'2006-5-9'
存入临时表#tb,目的是生成唯一的标识号id,以便后面处理
-------------------------------------------------
select price,date,identity(int) id into #tb from @t
查询所需数据.
--------------------------------------------
select id,price,date,hasBefore=(select count(1) from #tb b where b.price=a.price and b.id=a.id-1) into #base from #tb a
select price,date=min(date) + '--' + max(date) from (select price ,date,far=(case hasBefore when 0 then id else (select max(x.id) from #base x where x.price=b.price and x.id<b.id and x.hasBefore=0) end) from #base b) base group by price,far
显示结果
-------------------------------
1.0 2006-5-1--2006-5-2
1.5 2006-5-3--2006-5-6
1.0 2006-5-7--2006-5-8
2.0 2006-5-9--2006-5-9
#26
朋友(樓主). 你要的東西 (其實拿我上次貼的改一下就可以了) :
declare @t table (price float,datee datetime)
insert @t
select
1,'2006/5/1' union all select
1,'2006/5/2' union all select
1.5,'2006/5/3' union all select
1.5,'2006/5/4' union all select
1.5,'2006/5/6' union all select
1,'2006/5/7'
select *, idnb= identity(int,1,1) into #temp1 from @t a
where not exists(select 1 from @t c where a.price=c.price and a.datee>c.datee --and a.datee-datee=1
and (select count(1) from @t b where b.datee > c.datee and b.datee < a.datee and b.price<>a.price)<=0
)
select *, idnb= identity(int,1,1) into #temp2 from @t a
where not exists(select 1 from @t c where a.price=c.price and a.datee<c.datee --and datee-a.datee=1
and (select count(1) from @t b where b.datee > a.datee and b.datee < c.datee and b.price<>a.price)<=0
)
select (select sum(price) from @t where datee between a.datee and b.datee) total
,cast(a.datee as nvarchar(10))+'----'+cast(b.datee as nvarchar(10))
from #temp1 a join #temp2 b on a.idnb=b.idnb
drop table #temp1
drop table #temp2
declare @t table (price float,datee datetime)
insert @t
select
1,'2006/5/1' union all select
1,'2006/5/2' union all select
1.5,'2006/5/3' union all select
1.5,'2006/5/4' union all select
1.5,'2006/5/6' union all select
1,'2006/5/7'
select *, idnb= identity(int,1,1) into #temp1 from @t a
where not exists(select 1 from @t c where a.price=c.price and a.datee>c.datee --and a.datee-datee=1
and (select count(1) from @t b where b.datee > c.datee and b.datee < a.datee and b.price<>a.price)<=0
)
select *, idnb= identity(int,1,1) into #temp2 from @t a
where not exists(select 1 from @t c where a.price=c.price and a.datee<c.datee --and datee-a.datee=1
and (select count(1) from @t b where b.datee > a.datee and b.datee < c.datee and b.price<>a.price)<=0
)
select (select sum(price) from @t where datee between a.datee and b.datee) total
,cast(a.datee as nvarchar(10))+'----'+cast(b.datee as nvarchar(10))
from #temp1 a join #temp2 b on a.idnb=b.idnb
drop table #temp1
drop table #temp2
#27
最高分: 2分... 抢分艰难啊..^0^ ^0^
#1
select
sum(case when date between() then price else 0 end ) as ''
from table
sum(case when date between() then price else 0 end ) as ''
from table
#2
declare @t table (price float,date varchar(50))
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6'
select * from @t
select sum(price)as total,(min(date)+'----'+max(date))as 时间段
from @t
group by price
不知道你后面那个1怎么搞。期待高手
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6'
select * from @t
select sum(price)as total,(min(date)+'----'+max(date))as 时间段
from @t
group by price
不知道你后面那个1怎么搞。期待高手
#3
select sum(price),min(date)+'------'+max(date)
from table1 group by price
from table1 group by price
#4
你的例子有問題哦 . 5-5 的 1 是怎樣出來的.
#5
最後一條
1 2006-5-5-----2006-5-5
從哪里來的?
1 2006-5-5-----2006-5-5
從哪里來的?
#6
看不出规律 尤其是
1 2006-5-5-----2006-5-5
说明一下
1 2006-5-5-----2006-5-5
说明一下
#7
我想lz的意思是按照时间排序再分组。但这样怎么做?没法用group了
#8
应当是
1 2006-5-7-----2006-5-7
吧,lz?
1 2006-5-7-----2006-5-7
吧,lz?
#9
select sum(price) , min(date) + '--------' + max(date) from t
group by date
group by date
#10
declare @t table(price float,date nvarchar(50))
insert into @t select 1.5,'2006-5-3'
union select 1,'2006-5-1'
union select 1,'2006-5-2'
union select 1.5,'2006-5-4'
union select 1.5,'2006-5-6'
union select 1,'2006-5-7'
select sum(price),case when cast(date as datetime) between '2006-5-1' and '2006-5-2' then '2006-5-1到2006-5-2' when cast(date as datetime)
between '2006-5-3' and '2006-5-6' then '2006-5-3到2006-5-6' when cast(date as datetime)='2006-5-7' then '2006-5-7' end
from @t group by case when cast(date as datetime) between '2006-5-1' and '2006-5-2' then '2006-5-1到2006-5-2' when cast(date as datetime)
between '2006-5-3' and '2006-5-6' then '2006-5-3到2006-5-6' when cast(date as datetime)='2006-5-7' then '2006-5-7' end
insert into @t select 1.5,'2006-5-3'
union select 1,'2006-5-1'
union select 1,'2006-5-2'
union select 1.5,'2006-5-4'
union select 1.5,'2006-5-6'
union select 1,'2006-5-7'
select sum(price),case when cast(date as datetime) between '2006-5-1' and '2006-5-2' then '2006-5-1到2006-5-2' when cast(date as datetime)
between '2006-5-3' and '2006-5-6' then '2006-5-3到2006-5-6' when cast(date as datetime)='2006-5-7' then '2006-5-7' end
from @t group by case when cast(date as datetime) between '2006-5-1' and '2006-5-2' then '2006-5-1到2006-5-2' when cast(date as datetime)
between '2006-5-3' and '2006-5-6' then '2006-5-3到2006-5-6' when cast(date as datetime)='2006-5-7' then '2006-5-7' end
#11
輸入
1, '2006-5-1'
1, '2006-5-2'
1, '2006-5-3'
1.5, '2006-5-4'
1.5, '2006-5-5'
1.5, '2006-5-6'
1.5, '2006-5-7'
1, '2006-5-8'
1.5, '2006-5-9'
1.5, '2006-5-10'
結果
3.0 05-1-2006----05-3-2006
6.0 05-4-2006----05-7-2006
1.0 05-8-2006----05-8-2006
3.0 05-9-2006----05-10-2006
declare @t table (price float,datee datetime)
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-5' union all select
1.5, '2006-5-6' union all select
1.5, '2006-5-7' union all select
1, '2006-5-8' union all select
1.5, '2006-5-9' union all select
1.5, '2006-5-10'
select *, idnb= identity(int,1,1) into #temp1 from @t a where not exists(select 1 from @t where a.price=price and a.datee>datee and a.datee-datee=1)
select *, idnb= identity(int,1,1) into #temp2 from @t a where not exists(select 1 from @t where a.price=price and a.datee<datee and datee-a.datee=1)
select (select sum(price) from @t where datee between a.datee and b.datee) total
,cast(a.datee as nvarchar(10))+'----'+cast(b.datee as nvarchar(10))
from #temp1 a join #temp2 b on a.idnb=b.idnb
drop table #temp1
drop table #temp2
1, '2006-5-1'
1, '2006-5-2'
1, '2006-5-3'
1.5, '2006-5-4'
1.5, '2006-5-5'
1.5, '2006-5-6'
1.5, '2006-5-7'
1, '2006-5-8'
1.5, '2006-5-9'
1.5, '2006-5-10'
結果
3.0 05-1-2006----05-3-2006
6.0 05-4-2006----05-7-2006
1.0 05-8-2006----05-8-2006
3.0 05-9-2006----05-10-2006
declare @t table (price float,datee datetime)
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-5' union all select
1.5, '2006-5-6' union all select
1.5, '2006-5-7' union all select
1, '2006-5-8' union all select
1.5, '2006-5-9' union all select
1.5, '2006-5-10'
select *, idnb= identity(int,1,1) into #temp1 from @t a where not exists(select 1 from @t where a.price=price and a.datee>datee and a.datee-datee=1)
select *, idnb= identity(int,1,1) into #temp2 from @t a where not exists(select 1 from @t where a.price=price and a.datee<datee and datee-a.datee=1)
select (select sum(price) from @t where datee between a.datee and b.datee) total
,cast(a.datee as nvarchar(10))+'----'+cast(b.datee as nvarchar(10))
from #temp1 a join #temp2 b on a.idnb=b.idnb
drop table #temp1
drop table #temp2
#12
楼上的有Bug-_-
你在数据里少输入一行
1.5, '2006-5-5'
试试就知道了
你在数据里少输入一行
1.5, '2006-5-5'
试试就知道了
#13
--完成了-_-
--测试环境
declare @t table (price float,date varchar(50))
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7' union all select
2, '2006-5-8' union all select
2, '2006-5-10'
--一下开始执行
select identity(int,1,1)as Idx,*
into #temp
from @t t
order by convert(datetime,date)
insert into #temp
select max(price),max(date)
from #temp
select *,
isnull((select case when price<>t.price then 1 else 0 end as aa from #temp where idx=t.idx-1),1)v
into #temp1
from #temp t
select 金额,期间
from
(
select sum((endidx-idx+1)*price) as 金额,(begindate+' 到 '+enddate) as 期间
from
(
select idx,price,
left(convert(varchar,date,120),10) as begindate
,(isnull((
select max(t2.idx) from #temp t2 where t2.idx>t1.idx and t2.idx<(select min(idx) from #temp where v=1 and idx>t2.idx) and price=t1.price
),idx)
) as endidx
,
(
isnull((
select max(date) from #temp t2 where t2.idx>t1.idx and t2.idx<(select min(idx) from #temp where v=1 and idx>t2.idx) and price=t1.price
),date)
) as enddate
from #temp1 t1
where v=1
)a
group by (begindate+' 到 '+enddate)
)b
order by convert(datetime,left(期间,charindex(期间,' ')))
--清理测试环境
drop table #temp
drop table #temp1
/*结果
金额 期间
-------------------------------------------- ------------------------------6.0 2006-5-1 到 2006-5-7
4.5 2006-5-3 到 2006-5-6
1.0 2006-5-7 到 2006-5-7
4.0 2006-5-8 到 2006-5-10
*/
--测试环境
declare @t table (price float,date varchar(50))
insert @t
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7' union all select
2, '2006-5-8' union all select
2, '2006-5-10'
--一下开始执行
select identity(int,1,1)as Idx,*
into #temp
from @t t
order by convert(datetime,date)
insert into #temp
select max(price),max(date)
from #temp
select *,
isnull((select case when price<>t.price then 1 else 0 end as aa from #temp where idx=t.idx-1),1)v
into #temp1
from #temp t
select 金额,期间
from
(
select sum((endidx-idx+1)*price) as 金额,(begindate+' 到 '+enddate) as 期间
from
(
select idx,price,
left(convert(varchar,date,120),10) as begindate
,(isnull((
select max(t2.idx) from #temp t2 where t2.idx>t1.idx and t2.idx<(select min(idx) from #temp where v=1 and idx>t2.idx) and price=t1.price
),idx)
) as endidx
,
(
isnull((
select max(date) from #temp t2 where t2.idx>t1.idx and t2.idx<(select min(idx) from #temp where v=1 and idx>t2.idx) and price=t1.price
),date)
) as enddate
from #temp1 t1
where v=1
)a
group by (begindate+' 到 '+enddate)
)b
order by convert(datetime,left(期间,charindex(期间,' ')))
--清理测试环境
drop table #temp
drop table #temp1
/*结果
金额 期间
-------------------------------------------- ------------------------------6.0 2006-5-1 到 2006-5-7
4.5 2006-5-3 到 2006-5-6
1.0 2006-5-7 到 2006-5-7
4.0 2006-5-8 到 2006-5-10
*/
#14
还是有Bug-_-
#15
谢谢大家的热情参与,对不起,以下才是正确的示例
Price Date
1 2006-5-1
1 2006-5-2
1.5 2006-5-3
1.5 2006-5-4
1.5 2006-5-6
1 2006-5-7
结果:
TOTAL
2 2006-5-1-----2006-5-2
4.5 2006-5-3-----2006-5-6
1 2006-5-7-----2006-5-7
Price Date
1 2006-5-1
1 2006-5-2
1.5 2006-5-3
1.5 2006-5-4
1.5 2006-5-6
1 2006-5-7
结果:
TOTAL
2 2006-5-1-----2006-5-2
4.5 2006-5-3-----2006-5-6
1 2006-5-7-----2006-5-7
#16
实在看不出规律
最后一条为什么不与前三条一起参与运算. 难道记录的位置也是因素?
最后一条为什么不与前三条一起参与运算. 难道记录的位置也是因素?
#17
看来真的是好有难度
多谢谢大家,继续。。。。。
多谢谢大家,继续。。。。。
#18
最后一条为什么不与前三条一起参与运算. 难道记录的位置也是因素?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如果合在一起算,就反应不出价格的波动时间段了
所有数据都是按日期排好序的(不一定连续)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如果合在一起算,就反应不出价格的波动时间段了
所有数据都是按日期排好序的(不一定连续)
#19
select
Price*count(*),
cast(min(Date) as varchar(20)) + '----' + cast(max(Date) as varchar(20))
from
tb
group by
Price
Price*count(*),
cast(min(Date) as varchar(20)) + '----' + cast(max(Date) as varchar(20))
from
tb
group by
Price
#20
看错题了
#21
declare @t table
(price float,
[date] datetime)
insert into @t
select 1,'2006-5-1'
union all select 1,'2006-5-2'
union all select 1.5,'2006-5-3'
union all select 1.5,'2006-5-4'
union all select 1.5,'2006-5-6'
union all select 1,'2006-5-7'
declare @price float
declare @date datetime
declare @sql varchar(8000)
declare @lastval float, @totalval float
declare @datestr varchar(100), @lastdatestr varchar(100)
declare @flag int
set @sql=''
set @lastval = 0.01
set @datestr = ''
set @flag=0
declare cur cursor for select price,[date] from @t order by [date]
open cur
fetch next from cur into @price,@date
while @@fetch_status = 0
begin
if @price<>@lastval
begin
if @flag=0
begin
set @flag=1
set @datestr = convert(varchar(20),@date,102)
set @lastdatestr = @datestr
set @lastval = @price
set @totalval = @price
end
else
begin
select @datestr = @datestr + '----' + @lastdatestr
select @sql = @sql + ' select '+cast(@totalval as varchar(10))+ ' a,''' + @datestr + ''' b union'
set @datestr = convert(varchar(20),@date,102)
set @lastdatestr = @datestr
set @lastval = @price
set @totalval = @price
end
end
else
begin
select @totalval = @totalval + @price
set @lastdatestr = convert(varchar(20),@date,102)
end
fetch next from cur into @price,@date
end
select @datestr = @datestr + '----' + @lastdatestr
select @sql = @sql + ' select '+cast(@totalval as varchar(10))+ ',''' + @datestr + ''' union'
close cur
deallocate cur
select @sql = 'select * from (' + left(@sql, len(@sql)-5) + ') tb order by tb.b'
exec(@sql)
/*
2.0 2006.05.01----2006.05.02
4.5 2006.05.03----2006.05.06
1.0 2006.05.07----2006.05.07
*/
(price float,
[date] datetime)
insert into @t
select 1,'2006-5-1'
union all select 1,'2006-5-2'
union all select 1.5,'2006-5-3'
union all select 1.5,'2006-5-4'
union all select 1.5,'2006-5-6'
union all select 1,'2006-5-7'
declare @price float
declare @date datetime
declare @sql varchar(8000)
declare @lastval float, @totalval float
declare @datestr varchar(100), @lastdatestr varchar(100)
declare @flag int
set @sql=''
set @lastval = 0.01
set @datestr = ''
set @flag=0
declare cur cursor for select price,[date] from @t order by [date]
open cur
fetch next from cur into @price,@date
while @@fetch_status = 0
begin
if @price<>@lastval
begin
if @flag=0
begin
set @flag=1
set @datestr = convert(varchar(20),@date,102)
set @lastdatestr = @datestr
set @lastval = @price
set @totalval = @price
end
else
begin
select @datestr = @datestr + '----' + @lastdatestr
select @sql = @sql + ' select '+cast(@totalval as varchar(10))+ ' a,''' + @datestr + ''' b union'
set @datestr = convert(varchar(20),@date,102)
set @lastdatestr = @datestr
set @lastval = @price
set @totalval = @price
end
end
else
begin
select @totalval = @totalval + @price
set @lastdatestr = convert(varchar(20),@date,102)
end
fetch next from cur into @price,@date
end
select @datestr = @datestr + '----' + @lastdatestr
select @sql = @sql + ' select '+cast(@totalval as varchar(10))+ ',''' + @datestr + ''' union'
close cur
deallocate cur
select @sql = 'select * from (' + left(@sql, len(@sql)-5) + ') tb order by tb.b'
exec(@sql)
/*
2.0 2006.05.01----2006.05.02
4.5 2006.05.03----2006.05.06
1.0 2006.05.07----2006.05.07
*/
#22
drop table T
create table T(price decimal(18,2),date datetime)
insert T
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7'
declare @T table (price decimal(18,2),T_date varchar(500))
Declare T_SC cursor for Select price,[date ] from T
Declare @price decimal(18,4)
Declare @date datetime
declare @T_price decimal(18,4)
declare @sum_price decimal(18,4)
declare @Max varchar(10)
declare @Min varchar(10)
open T_SC
set @T_price=0
set @sum_price=0
fetch next from T_SC into @price,@date
while @@FETCH_STATUS=0
begin
if @T_price=0
begin
set @T_price=@price
set @sum_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else if @price<>@T_price
begin
insert @T select @sum_price, @Min+'-----'+@Max
set @sum_price=@price
set @T_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else
begin
set @sum_price=@sum_price+@price
set @Max=convert(char(10),@date,120)
end
fetch next from T_SC into @price,@date
end
insert @T select @sum_price, @Min+'-----'+@Max
close T_SC
deallocate T_SC
create table T(price decimal(18,2),date datetime)
insert T
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7'
declare @T table (price decimal(18,2),T_date varchar(500))
Declare T_SC cursor for Select price,[date ] from T
Declare @price decimal(18,4)
Declare @date datetime
declare @T_price decimal(18,4)
declare @sum_price decimal(18,4)
declare @Max varchar(10)
declare @Min varchar(10)
open T_SC
set @T_price=0
set @sum_price=0
fetch next from T_SC into @price,@date
while @@FETCH_STATUS=0
begin
if @T_price=0
begin
set @T_price=@price
set @sum_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else if @price<>@T_price
begin
insert @T select @sum_price, @Min+'-----'+@Max
set @sum_price=@price
set @T_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else
begin
set @sum_price=@sum_price+@price
set @Max=convert(char(10),@date,120)
end
fetch next from T_SC into @price,@date
end
insert @T select @sum_price, @Min+'-----'+@Max
close T_SC
deallocate T_SC
#23
create table T(price decimal(18,2),date datetime)
insert T
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7'
declare @T table (price decimal(18,2),T_date varchar(500))
Declare T_SC cursor for Select price,[date ] from T
Declare @price decimal(18,4)
Declare @date datetime
declare @T_price decimal(18,4)
declare @sum_price decimal(18,4)
declare @Max varchar(10)
declare @Min varchar(10)
open T_SC
set @T_price=0
set @sum_price=0
fetch next from T_SC into @price,@date
while @@FETCH_STATUS=0
begin
if @T_price=0
begin
set @T_price=@price
set @sum_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else if @price<>@T_price
begin
insert @T select @sum_price, @Min+'-----'+@Max
set @sum_price=@price
set @T_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else
begin
set @sum_price=@sum_price+@price
set @Max=convert(char(10),@date,120)
end
fetch next from T_SC into @price,@date
end
insert @T select @sum_price, @Min+'-----'+@Max
close T_SC
deallocate T_SC
select * from @t
insert T
select
1, '2006-5-1' union all select
1, '2006-5-2' union all select
1.5, '2006-5-3' union all select
1.5, '2006-5-4' union all select
1.5, '2006-5-6' union all select
1, '2006-5-7'
declare @T table (price decimal(18,2),T_date varchar(500))
Declare T_SC cursor for Select price,[date ] from T
Declare @price decimal(18,4)
Declare @date datetime
declare @T_price decimal(18,4)
declare @sum_price decimal(18,4)
declare @Max varchar(10)
declare @Min varchar(10)
open T_SC
set @T_price=0
set @sum_price=0
fetch next from T_SC into @price,@date
while @@FETCH_STATUS=0
begin
if @T_price=0
begin
set @T_price=@price
set @sum_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else if @price<>@T_price
begin
insert @T select @sum_price, @Min+'-----'+@Max
set @sum_price=@price
set @T_price=@price
set @Min=convert(char(10),@date,120)
set @Max=convert(char(10),@date,120)
end
else
begin
set @sum_price=@sum_price+@price
set @Max=convert(char(10),@date,120)
end
fetch next from T_SC into @price,@date
end
insert @T select @sum_price, @Min+'-----'+@Max
close T_SC
deallocate T_SC
select * from @t
#24
呵呵,代码惊人相似。
#25
楼主早说他意思不就好了.
生成临时数据
----------------------------
select
1 ,'2006-5-1' union all select
1 ,'2006-5-2' union all select
1.5 ,'2006-5-3' union all select
1.5 ,'2006-5-4' union all select
1.5 ,'2006-5-6' union all select
1 ,'2006-5-7' union all select
1 ,'2006-5-8' union all select
2 ,'2006-5-9'
存入临时表#tb,目的是生成唯一的标识号id,以便后面处理
-------------------------------------------------
select price,date,identity(int) id into #tb from @t
查询所需数据.
--------------------------------------------
select id,price,date,hasBefore=(select count(1) from #tb b where b.price=a.price and b.id=a.id-1) into #base from #tb a
select price,date=min(date) + '--' + max(date) from (select price ,date,far=(case hasBefore when 0 then id else (select max(x.id) from #base x where x.price=b.price and x.id<b.id and x.hasBefore=0) end) from #base b) base group by price,far
显示结果
-------------------------------
1.0 2006-5-1--2006-5-2
1.5 2006-5-3--2006-5-6
1.0 2006-5-7--2006-5-8
2.0 2006-5-9--2006-5-9
生成临时数据
----------------------------
select
1 ,'2006-5-1' union all select
1 ,'2006-5-2' union all select
1.5 ,'2006-5-3' union all select
1.5 ,'2006-5-4' union all select
1.5 ,'2006-5-6' union all select
1 ,'2006-5-7' union all select
1 ,'2006-5-8' union all select
2 ,'2006-5-9'
存入临时表#tb,目的是生成唯一的标识号id,以便后面处理
-------------------------------------------------
select price,date,identity(int) id into #tb from @t
查询所需数据.
--------------------------------------------
select id,price,date,hasBefore=(select count(1) from #tb b where b.price=a.price and b.id=a.id-1) into #base from #tb a
select price,date=min(date) + '--' + max(date) from (select price ,date,far=(case hasBefore when 0 then id else (select max(x.id) from #base x where x.price=b.price and x.id<b.id and x.hasBefore=0) end) from #base b) base group by price,far
显示结果
-------------------------------
1.0 2006-5-1--2006-5-2
1.5 2006-5-3--2006-5-6
1.0 2006-5-7--2006-5-8
2.0 2006-5-9--2006-5-9
#26
朋友(樓主). 你要的東西 (其實拿我上次貼的改一下就可以了) :
declare @t table (price float,datee datetime)
insert @t
select
1,'2006/5/1' union all select
1,'2006/5/2' union all select
1.5,'2006/5/3' union all select
1.5,'2006/5/4' union all select
1.5,'2006/5/6' union all select
1,'2006/5/7'
select *, idnb= identity(int,1,1) into #temp1 from @t a
where not exists(select 1 from @t c where a.price=c.price and a.datee>c.datee --and a.datee-datee=1
and (select count(1) from @t b where b.datee > c.datee and b.datee < a.datee and b.price<>a.price)<=0
)
select *, idnb= identity(int,1,1) into #temp2 from @t a
where not exists(select 1 from @t c where a.price=c.price and a.datee<c.datee --and datee-a.datee=1
and (select count(1) from @t b where b.datee > a.datee and b.datee < c.datee and b.price<>a.price)<=0
)
select (select sum(price) from @t where datee between a.datee and b.datee) total
,cast(a.datee as nvarchar(10))+'----'+cast(b.datee as nvarchar(10))
from #temp1 a join #temp2 b on a.idnb=b.idnb
drop table #temp1
drop table #temp2
declare @t table (price float,datee datetime)
insert @t
select
1,'2006/5/1' union all select
1,'2006/5/2' union all select
1.5,'2006/5/3' union all select
1.5,'2006/5/4' union all select
1.5,'2006/5/6' union all select
1,'2006/5/7'
select *, idnb= identity(int,1,1) into #temp1 from @t a
where not exists(select 1 from @t c where a.price=c.price and a.datee>c.datee --and a.datee-datee=1
and (select count(1) from @t b where b.datee > c.datee and b.datee < a.datee and b.price<>a.price)<=0
)
select *, idnb= identity(int,1,1) into #temp2 from @t a
where not exists(select 1 from @t c where a.price=c.price and a.datee<c.datee --and datee-a.datee=1
and (select count(1) from @t b where b.datee > a.datee and b.datee < c.datee and b.price<>a.price)<=0
)
select (select sum(price) from @t where datee between a.datee and b.datee) total
,cast(a.datee as nvarchar(10))+'----'+cast(b.datee as nvarchar(10))
from #temp1 a join #temp2 b on a.idnb=b.idnb
drop table #temp1
drop table #temp2
#27
最高分: 2分... 抢分艰难啊..^0^ ^0^