如果表中具有多个年份的数据，每个年份的审核结果都能显示
SELECT year,code,
p_id, v1
FROM t603_2
WHERE code IN (’600001’,’600002’)
MODEL RETURN UPDATED ROWS
PARTITION BY (code,year)
DIMENSION BY (p_id)
MEASURES (v1)
RULES (
v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
ORDER BY code, p_id;
YEAR CODE P_ID V1
2008 600001 err1 0
2007 600001 err1 0
2007 600002 err1 1
2008 600002 err1 1
规则也可以是多个维度不同取值，本例假定不同年份之间比较，比如要求2008年的’10’=2007年的’30’+’31’
SELECT year,code,
p_id, v1
FROM t603_2
WHERE code IN (’600001’,’600002’)
MODEL RETURN UPDATED ROWS
PARTITION BY (code)
DIMENSION BY (p_id,year)
MEASURES (v1)
RULES (
v1[’err1’,2008] = v1[’30’,2007] + v1[’31’,2007] -v1[’10’,2008])
ORDER BY code, p_id;
YEAR CODE P_ID V1
2008 600001 err1 0
2008 600002 err1 1
如果年份很多，每个年份都是和上年比较，这种描述可以用CV()函数简化
SQL> insert into t603_2 select ’2006’ year,code,p_id,v1,v2 from t603_1;
SELECT year,code,
p_id, v1
FROM t603_2
WHERE code IN (’600001’,’600002’)
MODEL RETURN UPDATED ROWS
PARTITION BY (code)
DIMENSION BY (p_id,year)
MEASURES (v1)
RULES (
v1[’err1’,for year in( 2007 ,2008)] = v1[’30’,CV(year)-1] + v1[’31’,CV(year)-1] -v1[’10’,CV(year)])
ORDER BY code, p_id;
YEAR CODE P_ID V1
2007 600001 err1 0
2008 600001 err1 0
2007 600002 err1 1
2008 600002 err1 1
如果year是数值类型，还可以用for year from 2007 to 2009 increment 1的语法，如果是其他类型，还可以用在in子句带子查询的办法，
比如for year in (select year from t603_2)。
但需要注意不能采用year in 的语法，year in的语法只能符号引用已经存在的单元格，而v1[’err1’,x]是新单元格。
单个年份的写法如下:
SELECT year,code,
p_id, v1
FROM t603_2
WHERE code IN (’600001’,’600002’)
MODEL RETURN UPDATED ROWS
PARTITION BY (code)
DIMENSION BY (p_id,year)
MEASURES (v1)
RULES (
v1[’err1’,2008] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])
ORDER BY code, p_id;
YEAR CODE P_ID V1
2008 600001 err1 0
2008 600002 err1 1
而
SELECT year,code,
p_id, v1
FROM t603_2
WHERE code IN (’600001’,’600002’)
MODEL RETURN UPDATED ROWS
PARTITION BY (code)
DIMENSION BY (p_id,year)
MEASURES (v1)
RULES (
v1[’err1’,year in (’2008’)] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])
则返回0行
3COME考试频道为您精心整理，希望对您有所帮助，更多信息在http://www.reader8.com/exam/
