IBM Rational 系统开发最佳实践工具包	WebSphere MQ 最佳实践 TOP 15

邹键！等各位老大求助！

楼主83born（X^X）2006-03-08 13:41:45 在 MS-SQL Server / 基础类提问

有以下难题，特向大师请教：

有一包房收费系统，包房分套房和标房两种类型，不同的类型收费标准不一样，收费以时间为单位，且不同的时段收费也不同。

1、表结构如下：
--收费标准表
create table qp_feestd (
room_type varchar(20) not null,--包厢类型
time_begin varchar(20) null,--费用起始时间点，例：‘00：00：00’
time_end varchar(20) null,--费用起始时间点
std decimal(18,8) null,--费用标准（每小时所需费用）
constraint PK_QP_FEESTD primary key (room_type)
)

2、模拟记录如下：
记录如下（还可能会有不同的时间段）：
标房，‘00：00：00’，‘11：59：59’，50
标房，‘12：00：00’，‘23：59：59’，100
套房，‘00：00：00’，‘11：59：59’，100
套房，‘12：00：00’，‘23：59：59’，200

3、问题为
现给出一实际消费：

包厢类型，开始时间，结束时间
标房，‘2006-03-08　10：00：00’，‘2006-03-09　19：00：00’

如何写出“在指定包厢类型的情况下，求任意时间段内此包厢的费用”的存储过程？

问题点数：100、回复次数：24Top

1 楼scmail81（琳·风の狼(修罗)）回复于 2006-03-08 15:04:59 得分 20

Try:
create table qp_feestd (
room_type varchar(20) not null,--包厢类型
time_begin varchar(20) null,--费用起始时间点，例：‘00：00：00’
time_end varchar(20) null,--费用起始时间点
std decimal(18,8) null,--费用标准（每小时所需费用）
)

insert qp_feestd select '标房','00:00:00','11:59:59',50
insert qp_feestd select '标房','12:00:00','23:59:59',100
insert qp_feestd select '套房','00:00:00','11:59:59',100
insert qp_feestd select '套房','12:00:00','23:59:59',200

create table A
(
包厢类型 varchar(10),
开始时间 datetime,
结束时间 datetime
)

insert A select '标房','2006-03-08 10:00:00','2006-03-09 19:00:00'

select datediff(hh,T.开始时间,convert(char(10),T.开始时间,120)+ ' '+ (select top 1 time_end from qp_feestd where T.包厢类型=room_type order by time_end)) * (select top 1 std from qp_feestd where T.包厢类型=room_type order by time_end)
+
datediff(hh,T.开始时间,convert(char(10),T.开始时间,120)+ ' '+ (select top 1 time_end from qp_feestd where T.包厢类型=room_type order by time_end DESC)) * (select top 1 std from qp_feestd where T.包厢类型=room_type order by time_end DESC)
from A TTop

2 楼zhangyanxxxx（张言）回复于 2006-03-08 15:12:38 得分 0

如果是10点20进来的怎么考虑?20分钟是按一个小时算还是按每分钟算?Top

3 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2006-03-08 15:18:25 得分 20

--生成测试数据
create table t1 (
room_type varchar(20) not null,
time_begin varchar(20) null,
time_end varchar(20) null,
std money null
)

insert into t1 select '标房','00:00:00','11:59:59',50
insert into t1 select '标房','12:00:00','23:59:59',100
insert into t1 select '套房','00:00:00','11:59:59',100
insert into t1 select '套房','12:00:00','23:59:59',200
go

--创建用户定义函数
create function f_getfee(
@room_type varchar(20),
@time_begin datetime,
@time_end datetime)
returns money
as
begin
declare @sum money
declare @t table(DS datetime,DE datetime)
while @time_begin<@time_end
begin
insert into @t
select rtrim(datepart(hh,@time_begin))+':00:00',rtrim(datepart(hh,@time_begin))+':59:59'
set @time_begin=dateadd(hh,1,@time_begin)
end

select @sum=sum(a.std) from t1 a,@t b
where
b.DS between a.time_begin and a.time_end
and
b.DE between a.time_begin and a.time_end
and
a.room_type=@room_type

return @sum
end
go

--执行查询
select dbo.f_getfee('标房','2006-03-08 10:00:00','2006-03-09 19:00:00') as fee
go

/*
fee
---------------------
2600.0000
*/

--删除测试数据
drop function f_getfee
drop table t1Top

4 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2006-03-08 15:19:14 得分 0

以上实现方式的处理原则，不足1小时按1小时结算。Top

5 楼83born（X^X）回复于 2006-03-08 15:20:02 得分 0

<＝10分钟不算，>10且<60分钟按1小时算Top

6 楼happyflystone（无枪的狙击手）回复于 2006-03-08 15:42:56 得分 10

set nocount on
create table qp_feestd (
room_type varchar(20) not null,--包厢类型
time_begin varchar(20) null,--费用起始时间点，例：‘00：00：00’
time_end varchar(20) null,--费用起始时间点
std decimal(18,8) null,--费用标准（每小时所需费用）
)

insert qp_feestd select '标房','00:00:00','11:59:59',50
insert qp_feestd select '标房','12:00:00','23:59:59',100
insert qp_feestd select '套房','00:00:00','11:59:59',100
insert qp_feestd select '套房','12:00:00','23:59:59',200
go

--insert A select '标房','2006-03-08 10:00:00','2006-03-09 19:00:00'
declare @room varchar(20)
set @room = '标房'
declare @stime datetime
declare @etime datetime
set @stime = '2006-03-08 10:00:00'
set @etime = '2006-03-09 19:00:00'
declare @t table(包厢类型 varchar(10),时间 varchar(20))
declare @I int
set @i = datediff(hh,@stime,@etime)
while @I >0
begin
insert @t select @room ,right(convert(varchar(19),dateadd(hh,@I ,@stime),120),8)
set @i = @i - 1
end

select a.包厢类型,sum(b.std) as 总价
from @t a,qp_feestd b
where a.包厢类型 = b.room_type and a.时间 between b.time_begin and b.time_end
group by a.包厢类型

drop table qp_feestd

/*

包厢类型总价
---------- ----------------------------------------
标房 2650.00000000

*/Top

7 楼happyflystone（无枪的狙击手）回复于 2006-03-08 15:47:45 得分 0

libin_ftsafe(子陌红尘) 老大的条件少算最后一个小时的

Top

8 楼scmail81（琳·风の狼(修罗)）回复于 2006-03-08 16:06:32 得分 20

不好意思!后来才发现原先那写错了!

create table qp_feestd (
room_type varchar(20) not null,--包厢类型
time_begin varchar(20) null,--费用起始时间点，例：‘00：00：00’
time_end varchar(20) null,--费用起始时间点
std decimal(18,8) null,--费用标准（每小时所需费用）
)

insert qp_feestd select '标房','00:00:00','11:59:59',50
insert qp_feestd select '标房','12:00:00','23:59:59',100
insert qp_feestd select '套房','00:00:00','11:59:59',100
insert qp_feestd select '套房','12:00:00','23:59:59',200

create table A
(
包厢类型 varchar(10),
开始时间 datetime,
结束时间 datetime
)

insert A select '标房','2006-03-08 10:00:00','2006-03-09 19:00:00'

create Function T_Money(@Flag varchar(10),@beginDate datetime,@EndDate datetime)
returns int
as
begin
declare @TempTime datetime
declare @T_Money int
set @T_Money=0
set @TempTime=@beginDate
while (datediff(mi,@TempTime,@EndDate)>10)
begin
select @T_Money=@T_Money+case when datediff(mi,@TempTime,@EndDate)%60>10 then (datediff(mi,@TempTime,@EndDate)/60+1)*std else datediff(mi,@TempTime,@EndDate)/60*std end,@TempTime=dateadd(ss,1,convert(char(10),@TempTime,120) + ' ' + time_end) from qp_feestd where room_type=@Flag and @TempTime between convert(char(10),@TempTime,120) + ' ' + time_begin and convert(char(10),@TempTime,120) + ' ' + time_end
end
return @T_Money
end
Top

9 楼83born（X^X）回复于 2006-03-08 16:47:49 得分 0

注意：
‘2006-03-08　10：00：00’，‘2006-03-09　19：00：00’是跨收费标准中的时间段的Top

10 楼happyflystone（无枪的狙击手）回复于 2006-03-08 16:51:10 得分 0

注意：
‘2006-03-08　10：00：00’，‘2006-03-09　19：00：00’是跨收费标准中的时间段的

---

当然注意了，你看看结果对不对Top

11 楼83born（X^X）回复于 2006-03-08 17:04:06 得分 0

happyflystone(没枪的狙击手)
执行你的时，出现：
无法解决 equal to 操作的排序规则冲突。Top

12 楼happyflystone（无枪的狙击手）回复于 2006-03-08 17:15:03 得分 0

你SQL版本是多少Top

13 楼83born（X^X）回复于 2006-03-08 17:29:48 得分 0

if exists (select 1
from sysobjects
where id = object_id('qp_feestd')
and type = 'U')
drop table qp_feestd
go

create table qp_feestd (
room_type varchar(20) null,--包厢类型
time_begin varchar(20) null,--费用起始时间点，例：‘00：00：00’
time_end varchar(20) null,--费用起始时间点
std decimal(18,8) null,--费用标准（每小时所需费用）
)

insert qp_feestd select '标房','00:00:00','11:59:59',50
insert qp_feestd select '标房','12:00:00','23:59:59',100
insert qp_feestd select '套房','00:00:00','11:59:59',100
insert qp_feestd select '套房','12:00:00','23:59:59',200
go

if exists (select 1
from sysobjects
where id = object_id('c')
and type = 'U')
drop table c
go

create table c (
roomtype varchar(10) null,
timecount varchar(20) null
)

declare @room varchar(20)
declare @stime datetime
declare @etime datetime

set @room = '标房'
set @stime = '2006-03-08 10:00:00'
set @etime = '2006-03-09 19:00:00'

declare @i int
set @i = datediff(hh,@stime,@etime)

while @i >0
begin
insert c
select @room ,right(convert(varchar(19),dateadd(hh,@i ,@stime),120),8)

set @i = @i - 1
end

select * from c

select a.roomtype,sum(b.std) as account
from c a,qp_feestd b
where a.roomtype = b.room_type and
a.timecount between b.time_begin and b.time_end
group by a.roomtype

改成这样顺利通过！
不过，未验证结果是否正确！Top

14 楼happyflystone（无枪的狙击手）回复于 2006-03-08 17:34:39 得分 0

结果肯定正确，我算过了Top

15 楼scmail81（琳·风の狼(修罗)）回复于 2006-03-08 23:28:40 得分 15

create Function T_Money(@Flag varchar(10),@beginDate datetime,@EndDate datetime)
returns int
as
begin
declare @T_Money int
declare @TempDate datetime
declare @T table(begintime datetime,endtime datetime,std int)
set @TempDate=@beginDate
while datediff(mi,@TempDate,@endDate)>10
begin
insert @T select @TempDate as begintime,
case when datediff(hh,@endDate,convert(char(10),@TempDate,120) + ' ' + time_end)>0 then @endDate else convert(char(10),@TempDate,120) + ' ' + time_end end as endtime,
std
from qp_feestd
where room_type=@Flag and @TempDate between convert(char(10),@TempDate,120) + ' ' + time_begin and convert(char(10),@TempDate,120) + ' ' + time_end
select @TempDate=case when datediff(hh,@endDate,convert(char(10),@TempDate,120) + ' ' + time_end)>0 then @endDate else dateadd(mi,1,convert(char(10),@TempDate,120) + ' ' + time_end) end
from qp_feestd
where room_type=@Flag and @TempDate between convert(char(10),@TempDate,120) + ' ' + time_begin and convert(char(10),@TempDate,120) + ' ' + time_end
end
select @T_Money=sum(hh*std) from
(select case when datediff(mi,begintime,endtime)%60>10 then datediff(mi,begintime,endtime)/60+1 else datediff(mi,begintime,endtime)/60 end as hh,std from @T) T
return @T_Money
end

select dbo.T_Money('标房','2006-03-08 10:00:00','2006-03-09 19:00:00')

怎么会是2650 ！？？
8 号 10－12 2×50
8 号 12－24 12×100
9 号 00－12 12×50
9 号 12－19 7×100
－－－－－－－－－－－－
2600Top

16 楼eddy8863（西北狂）回复于 2006-03-09 01:16:58 得分 0

学习。。Top

17 楼83born（X^X）回复于 2006-03-09 09:05:08 得分 0

2600
是正确的。
happyflystone(没枪的狙击手) 的思路也是正确的。
Top

18 楼83born（X^X）回复于 2006-03-09 09:11:03 得分 0

scmail81(琳·风の狼) 结果虽然正确，但看的好晕Top

19 楼ReViSion（和尚）回复于 2006-03-09 09:25:36 得分 0

说到底就是，检查当前时间在哪个时间段，得到在这个时间段内的收费，
再在该段的结束时间上加上1，再检查在哪个时段，一直到住房结束时间Top

20 楼luckyprg（lucky）回复于 2006-03-09 09:47:09 得分 0

学习一下。Top

21 楼happyflystone（无枪的狙击手）回复于 2006-03-09 10:24:44 得分 0

更正：
本人对子佰老大的结果怀疑是错误的，经再一次计算他的结果是对的Top

22 楼happyflystone（无枪的狙击手）回复于 2006-03-09 10:25:26 得分 15

set nocount on
create table qp_feestd (
room_type varchar(20) not null,--包厢类型
time_begin varchar(20) null,--费用起始时间点，例：‘00：00：00’
time_end varchar(20) null,--费用起始时间点
std decimal(18,8) null,--费用标准（每小时所需费用）
)

insert qp_feestd select '标房','00:00:00','11:59:59',50
insert qp_feestd select '标房','12:00:00','23:59:59',100
insert qp_feestd select '套房','00:00:00','11:59:59',100
insert qp_feestd select '套房','12:00:00','23:59:59',200
go

--insert A select '标房','2006-03-08 10:00:00','2006-03-09 19:00:00'
declare @room varchar(20)
set @room = '标房'
declare @stime datetime
declare @etime datetime
set @stime = '2006-03-08 10:00:00'
set @etime = '2006-03-09 19:00:00'
declare @t table(包厢类型 varchar(10),时间 varchar(20))
declare @I int
set @i = datediff(hh,@stime,@etime)-1
while @I >=0
begin
insert @t select @room ,right(convert(varchar(19),dateadd(hh,@I ,@stime),120),8)
set @i = @i - 1
end

select a.包厢类型,sum(b.std) as 总价
from @t a,qp_feestd b
where a.包厢类型 = b.room_type and a.时间 between b.time_begin and b.time_end
group by a.包厢类型

drop table qp_feestd
/*

包厢类型总价
---------- ----------------------------------------
标房 2600.00000000

*/Top

23 楼bohlee（我心澎湃）回复于 2006-03-09 10:43:05 得分 0

agreeTop

24 楼bbbbcccc（）回复于 2006-03-09 11:33:30 得分 0

http://valenhua.go3.icpcn.com/Top