这是表关系,在基础表里面插入了职务层级和政治面貌。
select * from T21_EAP_ACC_PUNISH_INFO --员工问责处罚信息表
accbaseid 员工问责基础信息id
punishtype 处罚种类 (01:纪律处分,02:组织处理,03:经济扣罚)
punishmoney 处罚金额(元)
select * from t21_eap_acc_base_info --员工问责基础信息表
accbaseid 主键
username 被处罚人姓名
usercode 被处罚人编号
postlevel 被处罚时时岗位层级
politicalstatus 政治面貌
joblevel 被处罚时职务层级
punishorgcode 处罚机构号
---SQL
----职务层级
select count(a.jobstatus),a.happenorgname,a.jobstatus
from t21_eap_acc_base_info a,t21_eap_acc_punish_info b
where a.accbaseid = b.accbaseid and a.jobstatus in('01','02','03')
group by a.jobstatus,a.happenorgcode,a.happenorgname
order by a.happenorgcode
--岗位层级
select count(a.postlevel),a.happenorgname,a.postlevel
from t21_eap_acc_base_info a,t21_eap_acc_punish_info b
where a.accbaseid = b.accbaseid and a.jobstatus in('01','02','03','04')
group by a.postlevel,a.happenorgcode,a.happenorgname
order by a.happenorgcode
----政治面貌
select count(a.politicalstatus) ,a.happenorgname,a.politicalstatus
from t21_eap_acc_base_info a,t21_eap_acc_punish_info b
where a.accbaseid = b.accbaseid and a.politicalstatus in('01','02','03','04')
group by a.politicalstatus,a.happenorgcode,a.happenorgname
order by a.happenorgcode
--纪律处分(01,记过,02,记大过......)
select count(b.punishway) ,a.happenorgname,b.punishway,b.punishtype
from t21_eap_acc_base_info a,t21_eap_acc_punish_info b
where a.accbaseid = b.accbaseid
and b.punishtype='01' and b.punishway in ('01','02','03','04','05','06','07') group by b.punishtype,b.punishway,a.happenorgcode,a.happenorgname
--order by a.happenorgcode
--union all
--组织处理(01.诫勉谈话02........)
select count(b.punishway) ,a.happenorgname,b.punishway,b.punishtype
from t21_eap_acc_base_info a,t21_eap_acc_punish_info b
where a.accbaseid = b.accbaseid
and b.punishtype='02' and b.punishway in ('01','02','03','04','05','06','07') group by b.punishtype,b.punishway,a.happenorgcode,a.happenorgname
--order by a.happenorgcode
--经济扣罚
---punishmoney 经济扣罚金额
select count(b.punishway) ,sum(b.punishmoney),a.happenorgname,b.punishway,b.punishtype
from t21_eap_acc_base_info a,t21_eap_acc_punish_info b
where a.accbaseid = b.accbaseid
and b.punishtype='03' group by b.punishtype,b.punishway,a.happenorgcode,a.happenorgname
--order by a.happenorgcode
- * 想把这几个表中的简化一下sql 例:在一个机构中,纪律处分中,被记过的总共有几人,记大过的总共有几人