这种SQL怎么写

[LunTanZeng]

数据如下：
id Preid Year Month Bf1 Bf2
-----------------------------------------------------------
...
10 1 2007 5 20 20
11 2 2007 3 30 50
12 1 2007 6 30 40
13 1 2007 7 40 60
14 2 2007 4 35 60
....
-------------------------------------------------------------
得到如下:

preid FromTo Bf1 Bf2
-------------------------------------------------------------
1 2007-5|2007-7 20 40
2 2007-3|2007-4 5 10

[LunTanZeng]

bf1 bf2

1 (6-5)+(7-6) (6-5)+(7-6)
2 (4-3) (4-3)
----------------------------------
以上3 4 5 6 7 是对应的月份
bf为对应月份后一个月减去前一个月

[paoluo]

paoluo(一天到晚游泳的鱼) ( ) 信誉：100 Blog 加为好友 2007-07-06 10:27:21 得分: 0


月一定是連續的嗎？

如果結果是這樣的，結果應該為怎樣？


id Preid Year Month Bf1 Bf2
-----------------------------------------------------------
...
10 1 2007 5 20 20
11 2 2007 3 30 50
12 1 2007 6 30 40
13 1 2007 7 40 60
14 1 2007 8 50 60
15 2 2007 4 35 60

[LunTanZeng]

月份不一定连续

[Dear SQL(燊)]

如果是跨年度將年月轉換字符串連接起來，再轉換為字數類型

[云中客]

路过,看不懂,关注!!

[云中客]

LunTanZeng(筱淼) ( ) 信誉：100 Blog 加为好友 2007-7-6 11:19:30 得分: 0

bf1 bf2

1 (6-5)+(7-6) (6-5)+(7-6)
2 (4-3) (4-3)
----------------------------------
以上3 4 5 6 7 是对应的月份
bf为对应月份后一个月减去前一个月

是固定的计算方式,还是什么....
楼主的问题看不懂

[橡胶轮胎行业数字化高总]

处理年月合并的处理很简单，但是bf1和bf2真不知道怎么来的！

[free_pop2k]

是perid=1 year=2007 month=7 对应的bf1 减去perid=1 year=2007 month=5 对应的bf1 得到Bf1
Bf2也是同样的吧

好像是这样的!

[free_pop2k]

create table test(id int,Preid int,Year int,Month int,Bf1 int,Bf2 int)
insert test select 10,1,2007,5,20,20
union all select 11,2,2007,3,30,50
union all select 12,1,2007,6,30,40
union all select 13,1,2007,7,40,60
union all select 14,2,2007,4,35,60


create proc dbo.proc_test
as
begin
select preid,cast([year] as char(4))+min(cast([month] as char(2))) + '|' + cast([year] as char(4)) + '-' + max(cast([month] as char(2)))as FromTo into #temp from test group by preid,[year]
select a.*,(case when not exists (select 1 from test where preid=a.preid and [year]=a.[year] and [month]>a.[month]) then 1 when not exists (select 1 from test where preid=a.preid and [year]=a.[year] and [month]<a.[month]) then 2 else 0 end) as Leve into #temp2
from test a
-- result
select a.*,(select Bf1 from #temp2 where preid=a.preid and leve=1)-(select Bf1 from #temp2 where preid=a.preid and leve=2) as Bf1,
(select Bf2 from #temp2 where preid=a.preid and leve=1)-(select Bf2 from #temp2 where preid=a.preid and leve=2) as Bf2
from #temp a
end

exec dbo.proc_test

-- drop test data

drop proc dbo.proc_test
drop table test

--result
Preid FromTo Bf1 Bf2
---------------------------------------
1 20075 |2007-7 20 40
2 20073 |2007-4 5 10

[WhyAndAnswer]

select preid,FromTo=convert(varchar(10),nym.year)+right('-'+convert(varchar(10),nym.month),2)+'|'+convert(varchar(10),xym.year)+right('-'+convert(varchar(10),xym.month),2),
Bf1=C.Bf1-B.Bf1,Bf2=C.Bf2-B.Bf2
from
(
select preid,nym=min(convert(varchar(10),year)+right('00'+convert(varchar(10),month),2)),xym=max(convert(varchar(10),year)+right('00'+convert(varchar(10),month),2))
from table1
group by preid
) A,
(
select ym=convert(varchar(10),year)+right('-00'+convert(varchar(10),month),2),Bf1,bf2,year,month
from table1
) B
left join
(
select ym=convert(varchar(10),year)+right('-00'+convert(varchar(10),month),2),Bf1,bf2,year,month
from table1
) C
where A.nym=B.ym and A.xym=B.ym

1、由于年月的最大、最小可能跨年的，所以采用“年月”相等来关联，不知道 年+月 是不是主键
2、bf1 ，bf2就是最大年月的bf1 ，bf2分别减去最小年月的bf1 ，bf2
bf1 bf2
1 (6-5)+(7-6) (6-5)+(7-6)
2 (4-3) (4-3)
不知道是不是可以，具体要看更详细的数据

[newqq]

学习

[WhyAndAnswer]

更正一下
不知道preid+年+月是不是唯一的？

[ojuju10]

create table tbl(id int,Preid int,year int ,month int ,Bf1 int,Bf2 int)
insert into tbl select 10, 1, 2007, 5, 20, 20
insert into tbl select 11, 2, 2007, 3, 30, 50
insert into tbl select 12, 1, 2007, 6, 30, 40
insert into tbl select 13, 1, 2007, 7, 40, 60
insert into tbl select 14, 2, 2007, 4, 35, 60

create function yoyo (@id int)
returns varchar(100)
as
begin
declare @v varchar(100)
select @v=min(rtrim(year)+'-' +rtrim(month)) from tbl
where preid=@id

select @v= @v+' | '+max(rtrim(year)+'-' +rtrim(month)) from tbl
where preid=@id
return @v
end

select preid,dbo.yoyo(preid) as fromto,sum(bf1) as bf1,sum(bf2) as bf2 from tbl
group by preid

preid fromto bf1 bf2
----------- -----------------------------------------
1 2007-5 | 2007-7 90 120
2 2007-3 | 2007-4 65 110

(2 行受影响)

[wolf5200]

还不会写函数，正在学习当中。学习了！

[blueplusplus]

学习学习,好复杂啊,,,

[piecezz]

--1.通過兩個group by 語句獲取最大"年+月"和最小"年+月"的值
--2.然後與主表test關聯獲取最大"年+月"和最小"年+月"對應行的相關值
--3.最後把過濾出的最大Bf1-最小Bf1
--創建表
create table test(id int,Preid varchar(5),Year varchar(4),Month varchar(2),Bf1 int,Bf2 int)
insert test select 10,'1','2007','5',20,20
union all select 11,'2','2007','3',30,50
union all select 12,'1','2007','6',30,40
union all select 13,'1','2007','7',40,60
union all select 14,'2','2007','4',35,60

--Sql語句
select a.Preid as Preid,b.Year+'-'+b.Month+'|'+a.Year+'-'+a.Month as FromTo,a.Bf1-b.Bf1 as Bf1,a.Bf2-b.Bf2 as Bf2
from (
select * from test a right join (
select max(Year+Month) as YM from test group by Preid
) b on a.Year+a.Month=b.YM
) a
left join (
select * from test a right join (
select min(Year+Month) as YM from test group by Preid
) b on a.Year+a.Month=b.YM
) b
on a.Preid=b.Preid

--所得結果
1 2007-5|2007-7 8 40
2 2007-3|2007-4 5 10

[piecezz]

暈，上面的結果給錯了，sql語句是正確的，現在更正
1 2007-5|2007-7 20 40
2 2007-3|2007-4 5 10

[liufuyahong]

create table #test(id int,Preid int,Year int,Month int,Bf1 int,Bf2 int)
go
insert #test select 10,1,2007,5,20,20
union all select 11,2,2007,3,30,50
union all select 12,1,2007,6,30,40
union all select 13,1,2007,7,40,60
union all select 14,2,2007,4,35,60


select PreId,convert(varchar(7),min(FromB),126)+'|'+convert(varchar(7),max(ToB),126) FromTo,sum(Bf1) Bf1,sum(bf2) Bf2
from(
select PreId,cast((cast(year as varchar)+'-'+cast(month as varchar)+'-1') as datetime) ToB,Bf1-Bf1B Bf1,Bf2-Bf2B Bf2,FromB
from(
select *,
(select top 1 Bf1 from #test
where preid=a.preid
and cast((cast(year as varchar)+'-'+cast(month as varchar)+'-1') as datetime)<a.ADateTime
order by cast((cast(year as varchar)+'-'+cast(month as varchar)+'-1') as datetime) desc
) Bf1B,
(select top 1 Bf2 from #test
where preid=a.preid
and cast((cast(year as varchar)+'-'+cast(month as varchar)+'-1') as datetime)<a.ADateTime
order by cast((cast(year as varchar)+'-'+cast(month as varchar)+'-1') as datetime) desc
) Bf2B,
(select top 1 cast((cast(year as varchar)+'-'+cast(month as varchar)+'-1') as datetime) from #test
where preid=a.preid
and cast((cast(year as varchar)+'-'+cast(month as varchar)+'-1') as datetime)<a.ADateTime
order by cast((cast(year as varchar)+'-'+cast(month as varchar)+'-1') as datetime) desc
) FromB
from (
select *,cast((cast(year as varchar)+'-'+cast(month as varchar)+'-1') as datetime) ADateTime from #test
) a
)b
where Bf1B is not null
and bf2B is not null
)c
group by preid


drop table #test

/*
PreId FromTo Bf1 Bf2
----------- --------------- ----------- -----------
1 2007-05|2007-07 20 40
2 2007-03|2007-04 5 10

(2 row(s) affected)
*/

[gezhicyz]

看高手出招.

[orange1983]

create table #temp
( Preid int,
Year varchar(50),
MaxMonth int,
MinMonth int,
MaxBf1 int,
MinBf1 int,
MaxBf2 int,
MinBf2 int
)
go
--取出最大最小月份以及其对应的Bf1值
insert into #temp(Preid,Year,maxMonth,minMonth,MaxBf1,MaxBf2)
select a.Preid,a.Year,b.maxMonth,b.minmonth,a.Bf1,a.Bf2 from test a,
(select Preid,year,max(month) as maxMonth,min(month) as minMonth from test
group by Preid,year) b
where a.Preid=b.Preid and a.year=b.year and a.month=b.maxMonth
--取出最大最小月份对应的Bf2值
update #temp set minBf1=a.Bf1,minBf2=a.Bf2
from
(select * from
(select a.Preid,a.Year,a.Bf1,a.Bf2 from test a,
(select Preid,year,max(month) as maxMonth,min(month) as minMonth from test
group by Preid,year) b
where a.Preid=b.Preid and a.year=b.year and a.month=b.minMonth) a) a,#temp
where a.Preid=#temp.Preid and a.year=#temp.year
--组合所需数据
select Preid,year+'-'+cast(MinMonth as varchar(10))+'|'+year+'-'+cast(MaxMonth as varchar(10)) as FromTo,
MaxBf1-MinBf1 as Bf1,MaxBf2-MinBf2 as Bf2 from #temp

go
drop table #temp
go

楼主所提Bf1,Bf2的算法可以简化直接用最大月份-最小月份的值就可以了