一个极其具有挑战性的问题！（1000分相送，不够再加）
一个极其具有挑战性的问题！（1000分相送，不够再加）
已知：有5种蛋糕，每种蛋糕每天的价格都有可能不相同，100个蛋糕店为这5种蛋糕提供价格。
提问：用何种方式知道某一段时间里面某种蛋糕的平均价最低的3个蛋糕店
提示：
可能的情况有：001号蛋糕店录入第1种蛋糕数据
蛋糕编号 价格 蛋糕店编号 时间段（按具体时间） 时间段（按周数）
001 5.2 098 2004-8-2,2004-8-3,2004-8-6
002 5.3 022 2004-9-9,2004-9-13,2004-10-6
001 5.7 031 2004-8-2,2004-9-1|5,6,7
001 5.9 016 2004-6-2,2004-12-1|5,6
001 5.2 051 2004-7-1
004 5.0 100 2004-8-2,2004-8-3,2004-8-6
001 5.2 078 2004-1-1,2004-12-31|5,6
001 5.2 051 2004-8-2,2004-8-3,2004-8-6,2004-8-7
时间短（按具体时间）：就是可以录入具体某一天的时间，可以是30个日期或者更多
时间段（按周数） ：就是根据某一个起止日期内的所有星期数
有什么办法知道以上数据在2004-8-1到2004-9-1这段时间内谁哪三个蛋糕店提供的价格（平均价）最低呢。目前已经知道如何去做，但是执行效率极其低下。
可以重新建立表，可以添加新的字段。
请各位不惜赐教。
msn: v2boy@msn.com
q q: 129071
问题点数：100、回复次数：35Top
1 楼solidpanther（╃╄╃我爱机器猫╄╃╄）回复于 2004-09-02 03:07:15 得分 0
还是看不懂周的那个字段Top
2 楼zheninchangjiang（徐若涵）回复于 2004-09-02 07:48:45 得分 0
哇，1000分哪，咱可没这能力Top
3 楼txlicenhe（马可）回复于 2004-09-02 08:26:08 得分 0
时间段（按具体时间） 时间段（按周数）
这两个字段不要，另加两个字段： 开始时间，结束时间。
将
001 5.2 098 2004-8-2,2004-8-3,2004-8-6
改为
001 5.2 098 2004-8-2 2004-8-3
001 5.2 098 2004-8-6 2004-8-6
其它类似。这样就好解了。
代码略。
Top
4 楼zjcxc（邹建）回复于 2004-09-02 08:36:40 得分 0
表设计得不太好,建议用马可的表设计方法,否则还要分拆时间段,还要考虑是否合法日期的问题.Top
5 楼zjcxc（邹建）回复于 2004-09-02 08:43:08 得分 0
--完全的表设计建议是这样的:
蛋糕信息表
蛋糕编号 蛋糕名称 .....
---------- --------- -----
001 A
002 B
....
蛋糕店信息表
蛋糕店编号 蛋糕店名称 负责人 联系电话 ...
---------- ------------ ------ --------- ----
001 A蛋糕店 张三 12346785
002 B蛋糕店 张三 12346785
003 C蛋糕店 张三 12346785
....
价格供应表
蛋糕编号 蛋糕店编号 供应的开始时间 供应的结束时间 供应价格
---------- ----------- ---------------- -------------- --------------
001 001 2004-7-1 2004-7-1 5.1
001 001 2004-8-1 2004-9-1 5.2
001 002 2004-7-1 2004-7-1 5.3
.......................
Top
6 楼liuxiang_csdn（刘翔）回复于 2004-09-02 08:55:44 得分 0
同意马可和邹建。Top
7 楼zjcxc（邹建）回复于 2004-09-02 09:03:56 得分 0
--根据上面的表设计,实现查询的存储过程
create proc p_qry
@dt1 datetime='2004-8-1', --开始统计时间
@dt2 datetime='2004-9-1' --结束统计时间
as
set nocount on
select 蛋糕编号,蛋糕店编号
,平均供应价格=avg(供应价格)
,sid=0
into #t from 价格供应表
where 供应的开始时间<=@dt2
and 供应的结束时间>=@dt1
group by 蛋糕编号,蛋糕店编号
order by 蛋糕编号,平均供应价格
declare @i int,@蛋糕编号 varchar(10)
update #t set @i=case 蛋糕编号 when @蛋糕编号 then @i+1 else 0 end
,sid=@i,@蛋糕编号=蛋糕编号
select 蛋糕编号,蛋糕店编号,平均供应价格
from #t
where sid between 1 and 3
Top
8 楼zjcxc（邹建）回复于 2004-09-02 09:04:24 得分 0
如果还要显示其他相关信息,则用关联条件把表关联起来就行了.Top
9 楼thunderclap（认识事物都有两面性）回复于 2004-09-02 09:12:05 得分 0
跨栏王也来这里啊。Top
10 楼liuxiang_csdn（刘翔）回复于 2004-09-02 09:14:30 得分 0
跨栏王也要学SQL，得有危机感啊。Top
11 楼v2boy（大格）回复于 2004-09-02 09:16:33 得分 0
Re:txlicenhe(马可) ( ) ，zjcxc(邹建)
谢谢你们的回复。忘记说了，表结构不能修改。否则整个系统会重做。2000多个ASP程序啊，老大们！～！Top
12 楼zjcxc（邹建）回复于 2004-09-02 09:17:46 得分 0
那没办法,按照你目前做出的方法去做吧.
要分拆你的时间字段再统计,速度不慢那是不可能的.Top
13 楼zjcxc（邹建）回复于 2004-09-02 09:18:13 得分 0
楼主说的"可以重新建立表，可以添加新的字段。"
原来是白说的啊?Top
14 楼Microsoft1020（）回复于 2004-09-02 09:21:35 得分 0
跨栏王学SQL搞世界记录统计啊Top
15 楼v2boy（大格）回复于 2004-09-02 09:23:47 得分 0
如果有10000种产品，1年365天，1000个供应商提供每天的价格。如果按照各位老大的方法，数据库里面就会有 10000X365X1000 ＝ 3650000000
那数据库里面，光价格就有36亿条记录，估计要老板要把数据库换大型机才能跑。
而上面我说的情况是有的。
Top
16 楼liuxiang_csdn（刘翔）回复于 2004-09-02 09:23:49 得分 0
等跑不动了要转行的时候，多一门技能不好吗？
Top
17 楼v2boy（大格）回复于 2004-09-02 09:25:07 得分 0
Re：
楼主说的"可以重新建立表，可以添加新的字段。"
原来是白说的啊?
－－－－－－－－－－－－－－－－－－－－－－
我说的重建表是增加字段或者在不修改原有的表结构上添加其他表。抱歉没有说清楚。Top
18 楼zjcxc（邹建）回复于 2004-09-02 09:26:38 得分 0
难道你还会经常查询1个月以前的价格???
不但要算记录数,还要算现实吧?
历史记录既然不是经常查(甚至是不查的),为什么还要放在一个表中? 早该清除历史记录啦.Top
19 楼zjcxc（邹建）回复于 2004-09-02 09:29:05 得分 0
就算按你自己的那种方法,如果每个供应商每天一个价格信息,你的字段岂不是要改为ntext类型? ntext类型的分拆你有没有想过有多麻烦? 效率如何?
每统计一次,就要去拆一次,你又觉得这个分拆有没有必要(把最多的资源浪费在最不必要的重复劳动上,至少我认为不值)Top
20 楼chump（木人）回复于 2004-09-02 09:30:03 得分 0
以数据仓库的方式来解决！
http://community.csdn.net/Expert/topic/3331/3331690.xml?temp=8.489627E-02Top
21 楼v2boy（大格）回复于 2004-09-02 09:41:30 得分 0
Re： zjcxc(邹建) ( ) 信誉：378
那个存放日期的字段最多一次能录入2个月的时间Top
22 楼v2boy（大格）回复于 2004-09-02 09:43:32 得分 0
Re:木人的方法试验一下先Top
23 楼zjcxc（邹建）回复于 2004-09-02 09:44:52 得分 0
那按你的设计,不是也要 10000*6*1000=60000000
6千万条数据? 加上分拆处理时的临时表开销之类,不是更累吗?Top
24 楼v2boy（大格）回复于 2004-09-02 19:48:39 得分 0
各位大哥，给个建议，如果在不破坏现在的数据结构和程序的情况下，我该如何做。Top
25 楼txlicenhe（马可）回复于 2004-09-02 20:11:02 得分 0
那就再加一个如上所说的表，然后你的问题就转成了两个表之间的数据转换了。
Top
26 楼v2boy（大格）回复于 2004-09-03 11:16:41 得分 0
非常感谢各位大大：基本上已经定为各位大大提供的三种意见
思路是这样的
1、首先，不修改目前数据表结果，新建一个表做为存放价格数据的临时表，主要是把日期格式化一下便于索引和统计
2、该表只能存放当天之后30天的数据记录
3、数据录入程序仍然按照以前的，然后在该数据录入的程序中添加一个 处理过程。（该过程：将当前价格信息格式化之后存放到刚才建立的临时表中）
4、查询和统计使用临时表，临时表中的小于当天的历史数据需要每天执行一个程序删除。
这种思路来源于
chump(木人)
http://community.csdn.net/Expert/topic/3331/3331690.xml?temp=.5929376
zjcxc(邹建) txlicenhe(马可)
http://community.csdn.net/Expert/topic/3331/3331687.xml?temp=.4018824
可惜我对维度不熟悉
另外有
windindance(风舞轻扬) ( ) liufuyahong() ( )
http://community.csdn.net/Expert/topic/3331/3331696.xml?temp=4.078311E-02
crankfe(crank)
http://community.csdn.net/Expert/topic/3331/3331688.xml?temp=.3809473
还有一下大大的我看的不是很懂，看上去好像非常不错
qimini(循序渐进) ( ) 信誉：106
http://community.csdn.net/Expert/topic/3331/3331688.xml?temp=.3809473
希望大大们综合一下，最终搞个结论出来，今天结贴。综合的好的，每人1000分。其余各献计献策的都有分送。谢谢各位的支持。
另外，惨痛的教训就是廉价的程序员和DBA会导致系统要用数百倍的价格来补偿。
Top
27 楼zjcxc（邹建）回复于 2004-09-03 11:40:40 得分 0
--那就可以这样整理思路
1.原价格供应表增加一个自动编号的标识字段,这样不影响数据录入,方便与临时表数据同步
如果原表本来就有主键,那就直接利用主键,不需要增加字段
2.增加一个处理的临时表,保存最近30天的供应数据,表结构如下:
供应id(与原价格供应表的id对应) 蛋糕编号 蛋糕店编号 供应开始时间 供应结束时间 价格
----------------------------- --------- ---------- ------------ ------------ ----
1 001 001 2004-7-1 2004-7-1 5.1
1 001 001 2004-8-1 2004-9-1 5.2
1 001 002 2004-7-1 2004-7-1 5.3
3.数据录入方式不变,时间段(楼主的原表是分两个时间段字段吧?)
4.在原价格供应表上,写一个触发器,同步临时表
如果是新增,修改,则对inserted表中的时间段进行分拆处理
并将分拆的结果写入处理临时表
如果是修改/删除,则删除处理临时表中,供应id与deleted表中id相同的记录
5.查询统计时,从处理临时表中统计数据
6.写一个作业,对处理临时表中的记录进行删除处理,删除供应时间已经过期的供应信息
Top
28 楼agen10120216（agen）回复于 2004-09-03 15:21:42 得分 0
学习了！Top
29 楼ouyld（ゎたしすきぁぉた）回复于 2004-09-03 15:48:10 得分 0
UPTop
30 楼prcgolf（小鸟）回复于 2004-09-03 16:08:59 得分 0
upTop
31 楼37350792（嵇幼雄-不算高手）回复于 2004-09-03 17:32:49 得分 0
这道题最主要的难点是确定价格的具体时间,
我只最供一种思路!
1.先求出每天每个蛋糕店每个蛋糕的价格(这是一个很复杂的过程)
蛋糕编号 价格 蛋糕店编号 时间点
1) 将一个时间段的数据分隔,插入到临时表
2) 用一种算法计算拆分第二个时间段到同一临时表
2.求平均值Top
32 楼37350792（嵇幼雄-不算高手）回复于 2004-09-03 17:34:40 得分 0
当然分隔后插入到临时表,需要大量的角本!
我想你也是这样做的吧!
要不然性能不会那么慢啊!
Top
33 楼zjcxc（邹建）回复于 2004-09-03 22:52:19 得分 0
分拆处理,相对来说还不算是太麻烦,只要楼主写清楚两种时间段的存储格式,还是比较容易写出来的.Top
34 楼zjcxc（邹建）回复于 2004-09-03 22:54:06 得分 0
比如时间段1,就是按逗号分拆而已,这种分拆是最简单的.
对于时间段2,因为不但有起止时间,还有周数,不知道楼主对于这个规则是怎么定义的,是否会存在多个这种时间段存储在同一条记录中的情况.
这些都有待说明.Top
35 楼zjcxc（邹建）回复于 2004-09-03 23:00:14 得分 100
--先说说第一种时间段的分拆方法(第二种要楼主先写清楚规则)
--以下面的数据为示例,把它分拆成多条记录
create table tb(蛋糕编号 varchar(10),价格 decimal(10,1),蛋糕店编号 varchar(10),时间段1 varchar(8000))
insert tb select '001',5.2,'098','2004-8-2,2004-8-3,2004-8-6'
union all select '002',5.3,'022','2004-9-9,2004-9-13,2004-10-6'
go
--创建辅助处理的序数表(这个表,在用于实际中时,应该是一个正式表,这里只是演示,直接用临时表)
set rowcount 8000
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount 0
--分拆显示
select a.蛋糕编号,a.价格,a.蛋糕店编号
,开始时间=substring(a.时间段1,b.id,charindex(',',a.时间段1+',',b.id)-b.id)
,结束时间=substring(a.时间段1,b.id,charindex(',',a.时间段1+',',b.id)-b.id)
from tb a,#t b
where b.id<=len(a.时间段1)
and substring(','+a.时间段1,b.id,1)=','
order by a.蛋糕编号
drop table #t
go
--删除测试
drop table tb
/*--测试结果
蛋糕编号 价格 蛋糕店编号 开始时间 结束时间
---------- ------------ ---------- ---------- -------------
001 5.2 098 2004-8-2 2004-8-2
001 5.2 098 2004-8-3 2004-8-3
001 5.2 098 2004-8-6 2004-8-6
002 5.3 022 2004-10-6 2004-10-6
002 5.3 022 2004-9-13 2004-9-13
002 5.3 022 2004-9-9 2004-9-9
（所影响的行数为 6 行）
--*/Top
