再开一贴，解决的话两贴一起结，都给解决的人，拜托了，谢谢了～

[juliens]

具体情况如下：
SchoolId Course Score Period
1 语文 100 20060102
1 数学 95 20050204
1 英语 103 20060103
3 数学 86 20060106
.
.
.
表说明：SchoolId： 学校ID
Course：课程
Score：分数
Period：发布期号

期号说明：2006 01 06
| | |
年份 周期 期号

周期分为：01：月报
02：季报
03：半年报

这个表里包含了每个学校根据自己的发布周期发布的成绩（每个学校的发布周期可能不同）；
现在教育局要定期汇总平均成绩，汇总的时候教育局也要建立一个规则和学校一样的发布期，然后取这个发布期之前的每个学校的最新一期的成绩来进行平均运算。
请问如何写SQL语句。

上帖的地址：http://community.csdn.net/Expert/topic/5007/5007722.xml?temp=.3337366

[juliens]

开了3贴，总共200分，不够再开100分，直到解决为止，谢谢大家了～

[zsforever]

还没解决我帮你好了

[juliens]

到现在为止大家理解我的需求了么？

[playwarcraft]

--貌似~~~偶有了點理解了...
--看看能不能滿足~~

create table T (schoolID int,course varchar(10),score int,period varchar(08))
insert into T select 1,'chinese',100,'20060101'
insert into T select 1,'math',95,'20060101'
insert into T select 1,'chinese',103,'20060102'
insert into T select 1,'math',86,'20060102'
insert into T select 1,'chinese',100,'20060103'
insert into T select 2,'math',95,'20060201'
insert into T select 2,'English',103,'20060201'
insert into T select 2,'math',86,'20060202'
insert into T select 2,'chinese',100,'20060202'
insert into T select 3,'math',95,'20060104'
insert into T select 3,'English',103,'20060104'
insert into T select 3,'math',86,'20060105'

declare @period varchar(08)
set @period ='20060103' ----教育局要發布的月期號
select T.* from T inner join
(select schoolID,max(period) as period from T
where left(period,4)<=left(@period,4)
and convert(int,substring(period,5,2))*convert(int,right(period,2))<=convert(int,substring(@period,5,2))*convert(int,right(@period,2))
group by schoolID ) A
on T.schoolID=A.schoolID and T.period=A.period
order by T.schoolID

/**根據樓主給的數據,測試**/
schoolID course score period
----------- ---------- ----------- --------
1 chinese 100 20060103
2 math 95 20060201
2 English 103 20060201

[zsforever]

东西太散，看着眼花，你一起把能想到的再贴一遍
原始数据，输入参数，结果集

[juliens]

我想我表达的已经蛮清楚了：
总共有两个发布期，一个是学校的，一个是教育局的
要求是取到跟教育局发布期最接近的每个学校的发布期

困难在于：学校发布期有周期的概念，也有可能夸年度，两个年度之间可以是不同的周期。
如果教育局的周期跟学校的周期不同，那么就需要某种转换来满足条件。

[playwarcraft]

PP...樓主快測試一下~~~好多分阿~~

[yczealot]

你把Period全部转换为月报格式，然后比较大小，取Period小于等于教育局月报的最大值即可

[yczealot]

期号是表示第几月，第几季。。。吗？

[zhang_yzy]

你的解释跟开始的要求不一样了
------------------------------------
如果教育局的周期跟学校的周期不同，那么就需要某种转换来满足条件。

？？这个是什么意思？怎么转换？

[juliens]

期号是表示第几月，第几季。。。吗？

期号是根据发布周期来的，月报的话可以有12期，季报的话就只有4期。

[zhang_yzy]

另外，我觉得你的表设计的有问题？
--------------------------------------
为什么表中不设置一个发布时间的字段，
这样你就只需要判断发布的时间就可以了，与这个什么Period就没有什么关系了

[juliens]

实际上发布实际是有的，但是这个时间跟发布期没有密切的联系，因为学校可能在同一时间发布本月和上月的成绩。

[zhang_yzy]

个人感觉，你求的视图可以做出来，
但是必须将结果保存到一个表中，
若在以后的时间，用现在表中的数据就无法重现了？

[juliens]

不用的呀，我计算出平均价后就存到另外一个表中了，而学校发布的成绩作为历史记录留在那里，如果还需要，同样通过我要求的方法可以算出来的。

[playwarcraft]

--稍微改下可能好點

declare @period varchar(08)
set @period ='20060103' ----教育局要發布的月期號
select T.* from T inner join
(select schoolID,max(period) as period from T
where left(period,4)<left(@period,4) or
(left(period,4)=left(@period,4)
and convert(int,substring(period,5,2))*convert(int,right(period,2))<=convert(int,substring(@period,5,2))*convert(int,right(@period,2)))
group by schoolID ) A
on T.schoolID=A.schoolID and T.period=A.period
order by T.schoolID

[juliens]

多谢多谢，我继续测

BTW:还是很喜欢你的签名，太帅了，哈哈

[zicxc]

好像解决了

冒牌就不参乎了，楼太高，有小小晕

[yczealot]

-- SCHOOLID COURSE SCORE PERIOD
-- 1 语文 100 20060101
-- 1 数学 95 20060101
-- 1 语文 103 20060102
-- 1 数学 86 20060102
-- 1 语文 100 20060103
-- 2 数学 95 20050201
-- 2 英语 103 20060201
-- 2 数学 86 20060202
-- 2 语文 100 20060202
-- 3 数学 95 20060104
-- 3 英语 103 20060104
-- 3 数学 86 20060105

-- 01：月报
-- 02：双月报
-- 03：季报
-- 06：半年报
-- 12：年报

DROP TABLE T

DECLARE
@DATE CHAR(8),
@SCHOOLID INT

CREATE TABLE T (SCHOOLID INT,COURSE VARCHAR(10),SCORE INT,PERIOD VARCHAR(08),MPERIOD VARCHAR(08))
INSERT INTO T SELECT 1,'CHINESE',100,'20060101',''
INSERT INTO T SELECT 1,'MATH',95,'20060101',''
INSERT INTO T SELECT 1,'CHINESE',103,'20060102',''
INSERT INTO T SELECT 1,'MATH',86,'20060102',''
INSERT INTO T SELECT 1,'CHINESE',100,'20060103',''
INSERT INTO T SELECT 2,'MATH',95,'20060204',''
INSERT INTO T SELECT 2,'ENGLISH',103,'20060201',''
INSERT INTO T SELECT 2,'MATH',86,'20060202',''
INSERT INTO T SELECT 2,'CHINESE',100,'20060202',''
INSERT INTO T SELECT 3,'MATH',95,'20060104',''
INSERT INTO T SELECT 3,'ENGLISH',103,'20060104',''
INSERT INTO T SELECT 3,'MATH',86,'20060105',''
INSERT INTO T SELECT 3,'MATH',86,'20060302',''



UPDATE T SET MPERIOD= CASE CONVERT(INT,SUBSTRING(PERIOD,5,2))
WHEN 1 THEN CONVERT(INT,SUBSTRING(PERIOD,7,2))
WHEN 2 THEN CONVERT(INT,SUBSTRING(PERIOD,7,2))*2
WHEN 3 THEN CONVERT(INT,SUBSTRING(PERIOD,7,2))*3
WHEN 6 THEN CONVERT(INT,SUBSTRING(PERIOD,7,2))*6
WHEN 12 THEN 12
END


SET @DATE='20060109'
SET @SCHOOLID=2


SELECT * FROM T WHERE SCHOOLID=@SCHOOLID

SELECT * FROM T WHERE YEAR(PERIOD)=YEAR(@DATE) AND MPERIOD<=CONVERT(INT,SUBSTRING(@DATE,7,2))
AND SCHOOLID=@SCHOOLID AND MPERIOD=(SELECT MAX(MPERIOD) FROM T WHERE YEAR(PERIOD)=YEAR(@DATE) AND MPERIOD<=CONVERT(INT,SUBSTRING(@DATE,7,2)) AND COURSE='MATH') AND COURSE='MATH'
UNION
SELECT * FROM T WHERE YEAR(PERIOD)=YEAR(@DATE) AND MPERIOD<=CONVERT(INT,SUBSTRING(@DATE,7,2))
AND SCHOOLID=@SCHOOLID AND MPERIOD=(SELECT MAX(MPERIOD) FROM T WHERE YEAR(PERIOD)=YEAR(@DATE) AND MPERIOD<=CONVERT(INT,SUBSTRING(@DATE,7,2)) AND COURSE='CHINESE') AND COURSE='CHINESE'
UNION
SELECT * FROM T WHERE YEAR(PERIOD)=YEAR(@DATE) AND MPERIOD<=CONVERT(INT,SUBSTRING(@DATE,7,2))
AND SCHOOLID=@SCHOOLID AND MPERIOD=(SELECT MAX(MPERIOD) FROM T WHERE YEAR(PERIOD)=YEAR(@DATE) AND MPERIOD<=CONVERT(INT,SUBSTRING(@DATE,7,2)) AND COURSE='ENGLISH') AND COURSE='ENGLISH'

[prcgolf]

up