1.1w+
社区成员
6.8w+
社区内容
- 主页
这种SQL怎么写
数据如下：
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
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
...全文
orange1983 2007年07月13日
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的算法可以简化直接用最大月份-最小月份的值就可以了
( 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的算法可以简化直接用最大月份-最小月份的值就可以了
gezhicyz 2007年07月13日
看高手出招.
liufuyahong 2007年07月11日
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)
*/
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)
*/
piecezz 2007年07月10日
暈，上面的結果給錯了，sql語句是正確的，現在更正
1 2007-5|2007-7 20 40
2 2007-3|2007-4 5 10
1 2007-5|2007-7 20 40
2 2007-3|2007-4 5 10
piecezz 2007年07月10日
--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
--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
blueplusplus 2007年07月09日
学习学习,好复杂啊,,,
wolf5200 2007年07月09日
还不会写函数，正在学习当中。学习了！
ojuju10 2007年07月09日
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 行受影响)
WhyAndAnswer 2007年07月07日
更正一下
不知道preid+年+月是不是唯一的？
不知道preid+年+月是不是唯一的？
newqq 2007年07月07日
学习
WhyAndAnswer 2007年07月07日
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)
不知道是不是可以，具体要看更详细的数据
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)
不知道是不是可以，具体要看更详细的数据
free_pop2k 2007年07月07日
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
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
free_pop2k 2007年07月07日
是perid=1 year=2007 month=7 对应的bf1 减去perid=1 year=2007 month=5 对应的bf1 得到Bf1
Bf2也是同样的吧
好像是这样的!
Bf2也是同样的吧
好像是这样的!
轮胎企业信息化高总 2007年07月07日
处理年月合并的处理很简单，但是bf1和bf2真不知道怎么来的！
云中客 2007年07月07日
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
1 (6-5)+(7-6) (6-5)+(7-6)
2 (4-3) (4-3)
----------------------------------
以上3 4 5 6 7 是对应的月份
bf为对应月份后一个月减去前一个月
是固定的计算方式,还是什么....
楼主的问题看不懂
云中客 2007年07月07日
路过,看不懂,关注!!
Dear SQL(燊) 2007年07月07日
如果是跨年度將年月轉換字符串連接起來，再轉換為字數類型
LunTanZeng 2007年07月06日
月份不一定连续
paoluo 2007年07月06日
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
月一定是連續的嗎？
如果結果是這樣的，結果應該為怎樣？
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 2007年07月06日
bf1 bf2
1 (6-5)+(7-6) (6-5)+(7-6)
2 (4-3) (4-3)
----------------------------------
以上3 4 5 6 7 是对应的月份
bf为对应月份后一个月减去前一个月
1 (6-5)+(7-6) (6-5)+(7-6)
2 (4-3) (4-3)
----------------------------------
以上3 4 5 6 7 是对应的月份
bf为对应月份后一个月减去前一个月