高手留步，電話費用結算sql，实属无奈才求救~！

[wusongwtl]

有a,b兩表 a表含有两字段 flt，areacode 前者记录通话时长，后者记录电话打往地

b表 areacode（对应a表） ，stime，etime，cost
用areacode会找到三笔记录

areacode stime etime cost
0~3分钟 0.22元/m 000 0 3 0.22
3~5分钟 0.20元/m 000 3 5 0.20
超过5分钟 0.11元/m 000 5 无穷大 0.11

如何求电话费用？（最好能通用，因有的areacode计费标准可能只有一条记录）

[lwl0606]

select a.flt,a.areacode,flt*cost as 电话费
from a left outer join b on a.areacode=b.areacode and flt >=stime and flt <=etime

[lwl0606]

或者
select a.flt,a.areacode,flt*cost as 电话费
from a left outer join b on a.areacode=b.areacode and flt between stime and etime

[肥胖的柠檬]

我想问一下,
0~3分钟 0.22元/m 000 0 3 0.22
3~5分钟 0.20元/m 000 3 5 0.20
超过5分钟 0.11元/m 000 5 无穷大 0.11

这个如果是打了11分钟,什么算? 11*0.11元/m ? 还是 0.22*2+0.20*2+0.11*7

如果是11*0.11 用lwl0606(寒泉) 有就可以了

[hellowork]

declare @ta table(flt int,areacode varchar(10))
insert @ta
select 1,'000' union all
select 4,'000' union all
select 6,'000' union all
select 2,'000'
declare @tb table(areacode varchar(10),stime int,etime int,cost decimal(5,2))
insert @tb
select '000',0,3,0.22 union all
select '000',3,5,0.20 union all
select '000',5,NULL,0.11

select a.areacode,sum(a.flt*b.cost) as cost from @ta as a left join @tb as b
on a.flt > isnull(b.etime,b.stime) or a.flt between b.stime and b.etime
group by a.areacode

/*结果
areacode cost
----------------
000 5.52
*/

[wusongwtl]

想简单了吧，分段计算的

[yrwx001]

create table #t (arecode varchar(10),money float)
decalre @stime int
select @stime = stime,@cost = cost*(flt-stime),
from a left outer join b on a.areacode=b.areacode and flt between stime and etime
insert #t
select areacode,money = sum((etime-stime)*cost) from a left outer join b on a.areacode=b.areacode and etime < @stime
insert #t
select areacode,money = cost(flt-stime)
from a left outer join b on a.areacode=b.areacode and flt between stime and etime

select areacode,sum(money) from #t group by areacode

[hellowork]

试一下这个,分段计算的:
declare @ta table(flt int,areacode varchar(10))
insert @ta
select 1,'000' union all
select 4,'000' union all
select 6,'000' union all
select 2,'000'
declare @tb table(areacode varchar(10),stime int,etime int,cost decimal(5,2))
insert @tb
select '000',0,3,0.22 union all
select '000',3,5,0.20 union all
select '000',5,NULL,0.11 /*注意:无限大时用NULL表示*/

SELECT areacode,sum(times*cost) as cost FROM
(select a.flt,a.areacode,times =
case
when a.flt between b.stime and b.etime then a.flt - b.stime
when a.flt > b.etime then b.etime - b.stime
when a.flt > b.stime and b.etime is null then a.flt - b.stime
end ,b.cost
from @ta as a inner join @tb as b
on a.flt > isnull(b.etime,b.stime) or a.flt between b.stime and b.etime) AS t
GROUP BY areacode

/*结果
areacode cost
----------------
000 2.69
*/

[hellowork]

注释：

2.69 =
1*.22 /*flt = 1*/
+ 3*.22 + 1*.20 /*flt = 4*/
+ 3*.22 + 2*.20 + 1*.11 /*flt = 6*/
+ 2*.22 /*flt = 2*/

[昵称被占用了]

declare @ta table(flt int,areacode varchar(10))
insert @ta
select 1,'000' union all
select 4,'000' union all
select 6,'000' union all
select 2,'000'
declare @tb table(areacode varchar(10),stime int,etime int,cost decimal(5,2))
insert @tb
select '000',0,3,0.22 union all
select '000',3,5,0.20 union all
select '000',5,NULL,0.11

select a.flt,a.areacode,sum(case when b.etime is null or a.flt between b.stime and b.etime then (a.flt-b.stime)*b.cost
else (b.etime-b.stime)*b.cost
end) as cost from @ta as a left join @tb as b
on a.flt > isnull(b.etime,b.stime) or a.flt between b.stime and b.etime
group by a.flt,a.areacode

--结果
flt areacode cost
----------- ---------- ----------------------------------------
1 000 .22
2 000 .44
4 000 .86
6 000 1.17

（所影响的行数为 4 行）

[lwl0606]

select a.flt,a.areacode,
(case when flt >= etime then etime else (case when flt > stime then flt - stime end ) end )*cost as 电话费
from a left outer join b on a.areacode=b.areacode
(case when flt >= etime then etime else (case when flt > stime then flt - stime end ) end ) between stime and etime

[lwl0606]

create table a (flt int ,areacode nvarchar(10))
insert into a select 1,'000'
insert into a select 3,'000'
insert into a select 4,'000'
insert into a select 10,'000'


create table b (areacode nvarchar(10),stime int, etime int ,cost decimal(10,2))
insert into b select '000',0,3,0.22
insert into b select '000',3,5,0.20
insert into b select '000',5,999999,0.11

select a.flt,a.areacode,
sum((case when flt >= etime then etime-stime else (case when flt > stime then flt - stime end ) end )*cost) as 电话费
from a left outer join b on a.areacode=b.areacode and
(case when flt >= etime then etime else (case when flt > stime then flt end ) end ) between stime and etime
group by a.flt,a.areacode

flt areacode 电话费
----------- ---------- ----------------------------------------
1 000 .22
3 000 .66
4 000 .86
10 000 1.61

(4 row(s) affected)

[winesmoke]

楼上正解

[wusongwtl]

一点了，不要午休了，我急啊