达梦数据库left join + group查询

表1 MBM_CASE

表2 MOS_ORGANIZATION

统计主体表：表1，其中字段P_JUSTICE_BUREAU存储的为 表2 的type_id = '255afee102374b60b770de3b601f0f0b'类型的id，需翻译成name

select a.P_JUSTICE_BUREAU,b.P_JUSTICE_BUREAU_NAMER,count(*) as total from MBM_CASE as a

left join (select id,p_name as P_JUSTICE_BUREAU_NAMER from MOS_ORGANIZATION as b where type_id = '255afee102374b60b770de3b601f0f0b') as b on a.P_JUSTICE_BUREAU = b.id

where a.P_STATUS = 'VALID' AND a.P_CASE_STATUS != 'DRAFT' AND a.P_ACCEPTANCE_DATE BETWEEN '2017-01-01' AND '2017-12-31'

GROUP BY a.P_JUSTICE_BUREAU,b.P_JUSTICE_BUREAU_NAMER

查询当前登录的用户所有的表名

SELECT TABLE_NAME FROM USER_TABLES

问题解决了。select distinct object_name TABLE_SCHEMA from all_objects where object_type = 'SCH'。可以查到

---