利用Oracle
以產品產量表為例
產品代碼具有審核關系
SQL> create table t
Table created
SQL> insert into t
SQL> insert into t
SQL> insert into t
SQL> insert into t
SQL> insert into t
SQL> insert into t
SQL> commit;
Commit complete
SQL> select * from t
CODE P_ID V
SELECT code
p_id
FROM t
WHERE code IN (
MODEL RETURN UPDATED ROWS
PARTITION BY (code)
DIMENSION BY (p_id)
MEASURES (v
RULES (
v
ORDER BY code
其中rule表示計算規則
PARTITION BY (code)表示按工廠分區
MODEL 關鍵字後面的 RETURN UPDATED ROWS 子句將結果限制為在該查詢中創建或更新的那些行
CODE P_ID V
如果返回值=
SELECT code
p_id
FROM t
WHERE code IN (
MODEL RETURN UPDATED ROWS
PARTITION BY (code)
DIMENSION BY (p_id)
MEASURES (v
RULES (
v
v
ORDER BY code
CODE P_ID V
如果表格中包含多個維度的數據
SELECT year
p_id
FROM t
WHERE code IN (
MODEL RETURN UPDATED ROWS
PARTITION BY (code)
DIMENSION BY (p_id
MEASURES (v
RULES (
v
ORDER BY code
YEAR CODE P_ID V
如果維度不影響規則
SELECT year
p_id
FROM t
WHERE code IN (
MODEL RETURN UPDATED ROWS
PARTITION BY (code
DIMENSION BY (p_id)
MEASURES (v
RULES (
v
ORDER BY code
YEAR CODE P_ID V
SQL> create table t
Table created
SQL> insert into t
如果表中具有多個年份的數據
SELECT year
p_id
FROM t
WHERE code IN (
MODEL RETURN UPDATED ROWS
PARTITION BY (code
DIMENSION BY (p_id)
MEASURES (v
RULES (
v
ORDER BY code
YEAR CODE P_ID V
From:http://tw.wingwit.com/Article/program/Oracle/201311/16914.html