oracle视图求救
我从来没有在oracle中建过视图,请大家不要笑话。
现在又三个表。
biao1 共有plintid,weight1,weight2,plinttype四个字段(plinttype,plintid存的是编码)
biao2 共有plintid,weight3,plintsizedate四个字段
biao3 共有plintid,plintidtext,plinttype,plinttypetext四个字段(plinttypetext,plintidtext存的是具体的编码信息)
想根据date时间段查询建立一个视图形
biao4字段为plintidtext,plinttypetext,sum(weight1),sum(weight2),sum(weight3),date
我从来没有在oracle中建过视图,请大家不要笑话。
现在又三个表。
biao1 共有plintid,weight1,weight2,plinttype四个字段(plinttype,plintid存的是编码)
biao2 共有plintid,weight3,plintsizedate四个字段
biao3 共有plintid,plintidtext,plinttype,plinttypetext四个字段(plinttypetext,plintidtext存的是具体的编码信息)
想根据date时间段查询建立一个视图形
biao4字段为plintidtext,plinttypetext,sum(weight1),sum(weight2),sum(weight3),date
AS
SQL语句为你要得到的
plintidtext,plinttypetext,sum(weight1),sum(weight2),sum(weight3),date
就可以了啊
sql语句
as
select plintidtext,plinttypetext,sum(weight1) weight1,sum(weight2) weight2,sum(weight3) weight3,plintsizedate date
from biao3,biao1,biao2
where biao3.plintid=biao1.plintid
and biao3.plintid=biao2.plintid
group by plintidtext,plinttypetext, plintsizedate;
AS
SELECT PLINTIDTEXT,
PLINTTYPETEXT,
SUM(WEIGHT1) AS S_WEIGHT1,
SUM(WEIGHT2) AS S_WEIGHT2,
SUM(WEIGHT3) AS S_WEIGHT3,
DATE
FROM BIAO1 T1,
BIAO2 T2,
BIAO3 T3
WHERE T3.PLINTID = T1.PLINTID
AND T3.PLINTID = T2.PLINTID
GROUP BY T3.PLINTIDTEXT,
T3.PLINTTYPETEXT,
T2.DATE
AS SQL语句,sql中对多个表进行关联查询出你想要的列即可。CREATE OR REPLACE VIEW biao4 AS
SELECT PLINTIDTEXT,
PLINTTYPETEXT,
SUM(WEIGHT1) AS S_WEIGHT1,
SUM(WEIGHT2) AS S_WEIGHT2,
SUM(WEIGHT3) AS S_WEIGHT3,
DATE as DATETIME
FROM BIAO1 T1,
BIAO2 T2,
BIAO3 T3
WHERE T1.PLINTID = T2PLINTID
AND T2.PLINTID = T3.PLINTID