致远OA新闻公告讨论调查信息查询SQL

需求

想查询新闻、公告、讨论、调查模块的数据报表，统计标题、类型、发布时间、发布人、发布部门等信息。

操作

通过数据库直接查询。

新闻信息

SELECT nd.TITLE AS 标题,
nd.TYPE_ID AS 新闻类型ID,
nt.TYPE_NAME AS 新闻类型,
nd.PUBLISH_DATE AS 发布时间,
nd.PUBLISH_USER_ID AS 发布人ID,
om.NAME AS 发布人,
nd.PUBLISH_DEPARTMENT_ID AS 发布部门ID,
ou.NAME AS 发布部门
FROM news_data nd 
LEFT JOIN news_type nt ON nd.TYPE_ID = nt.ID 
LEFT JOIN org_member om ON nd.PUBLISH_USER_ID = om.ID 
LEFT JOIN org_unit ou ON nd.PUBLISH_DEPARTMENT_ID = ou.ID 
;

公告信息

SELECT 
bd.TITLE AS 标题,
bd.TYPE_ID ,
bt.TYPE_NAME AS 公告类型,
bd.PUBLISH_DATE AS 发布时间,
bd.PUBLISH_USER_ID ,
om.NAME AS 发布人,
bd.PUBLISH_DEPARTMENT_ID ,
ou.NAME AS 发布部门
FROM bul_data bd 
LEFT JOIN bul_type bt ON bd.TYPE_ID = bt.ID
LEFT JOIN org_member om ON bd.PUBLISH_USER_ID = om.ID 
LEFT JOIN org_unit ou ON bd.PUBLISH_DEPARTMENT_ID = ou.ID 
;

讨论信息

SELECT 
ba.ARTICLE_NAME AS 主题,
ba.ISSUE_USER_ID ,
om.NAME AS 发布人,
ba.DEPARTMENT ,
ou.NAME AS 发布部门
FROM bbs_article ba 
LEFT JOIN org_member om ON ba.ISSUE_USER_ID = om.ID 
LEFT JOIN org_unit ou ON ba.DEPARTMENT = ou.ID 
;

调查信息

SELECT 
is2.SURVEY_NAME AS 调查名称,
is2.SURVEYTYPE_ID ,
is3.TYPE_NAME AS 调查类型,
is2.ISSUER_ID ,
om.NAME AS 发布人,
is2.DEPARTMENT_ID ,
ou.NAME AS 发布部门
FROM inquiry_surveybasic is2 
LEFT JOIN inquiry_surveytype is3 ON is2.SURVEYTYPE_ID = is3.ID 
LEFT JOIN org_member om ON is2.ISSUER_ID = om.ID 
LEFT JOIN org_unit ou ON is2.DEPARTMENT_ID = ou.ID 
;