有两张表,一张家庭表,一张人员表
家庭表有fid,fnumid,deptid
人员表有fnumid,usderid,cultureType,jobtYPE,ISJOB
家庭是主表,人员是从表,通过fnumid关联.
clubtureType有五个值(1,2,3,4,5)
jobType有四个值(1,2,3,4)
isjob有两个值(1,2)
现在要统计,clubtureType分员为(1,3,4)
jobtype是(2,3)
isjob是(1)
的总数,并把他们加起来,得到一个总数.
前通过DEPTID来分组.
要怎么创建视图.
要写存储过程吗?要写临时表,还是要写函数呢
家庭表有fid,fnumid,deptid
人员表有fnumid,usderid,cultureType,jobtYPE,ISJOB
家庭是主表,人员是从表,通过fnumid关联.
clubtureType有五个值(1,2,3,4,5)
jobType有四个值(1,2,3,4)
isjob有两个值(1,2)
现在要统计,clubtureType分员为(1,3,4)
jobtype是(2,3)
isjob是(1)
的总数,并把他们加起来,得到一个总数.
前通过DEPTID来分组.
要怎么创建视图.
要写存储过程吗?要写临时表,还是要写函数呢
附表:MEMBERcreate temporary table tmp
select count(*) number from member where cultureType in (1,3,4) and fnumid in (select fnumid from home group by deptid)
union all
select count(1) from member where jobtype in (2,3) and fnumid in (select fnumid from home group by deptid)
union all
select count(*) from member where isjob = 1 and fnumid in (select fnumid from home group by deptid);select sum(number) from tmp;