数据库表常用SQL

# 首页涉及的相关表
```sql
select * from emr_first_page_button_list;--【导】首页个性化业务按钮配置
select * from emr_first_page_column_dict;--【导】首页GridControl类数据列显示配置
select * from emr_first_page_column_index;

select * from emr_first_page_config;

select * from emr_first_page_control_color;

select * from emr_first_page_control_config;
select * from emr_first_page_control_dict;

select * from emr_winform_templet; --首页模版
select * from emr_first_page_thirdsource_log;--患者采集的数据日志

--配置字典相关
select * from emr_first_page_thirdsource;--数据采集配置
select * from emr_first_page_print; --首页打印配置
select * from emr_first_page_item_dict; --首页字典
select * from emr_first_page_diag_oper_fre;--首页常用字典

select * from  VALUECHANGELOG; --首页修改记录

--病案首页自动质控相关
select *from emr_first_page_validate;------首页验证清单
select *from emr_first_page_validate_type;------首页验证类别
select * from  emr_first_page_qc_feedback; --首页质控反馈表
select* from  emr_first_page_qc_index; --首页自动质控记录表
select* from emr_first_page_qc_detail;--首页自动质控记录明细表

--病案首页人工质控相关
select * from drg_report_dict;--手动质控缺陷项目字典表
select * from DRG_REPORT_RECORD;--患者手动质控数据

SELECT T.* FROM VALIDATE_AREA_DEPT T;
SELECT T.* FROM VALIDATE_INFO_TYPE_DICT T; --方案与验证类别(基本信息，)映射
select * from QULITY_VALIDATE_VS_TYPE;

```
# 诊断手术术语库
```sql
-- #分类
select * from JHSYK_DIAGNOSIS_CLASS;           --诊断分类(章节、类目、亚目)  (34226条)
select * from JHSYK_OPERATION_CLASS;           --手术类别分类代码  (3887条)

-- #字典
select * from JHSYK_DIAGNOSIS_YB20;            --医保诊断2.0 (33304条)
select * from JHSYK_OPERATION_YB20;            --医保手术2.0 (13686条)

-- #详情
select * from JHSYK_DIAGNOSIS_CLASS_YB20;      --带有分类的医保诊断字典库 (34226条)
select * from JHSYK_OPERATION_CLASS_YB20;      --医保手术2.0及手术分类代码 (13687条)

-- #映射关系
select * from JHSYK_DIAGNOSIS_GL20_YB20;       --国家临床诊断2.0与医保2.0映射 (35587条)
select * from JHSYK_DIAGNOSIS_YB10_YB20;       --诊断医保1.0与医保2.0映射 (33440条)

select * from JHSYK_OPERATION_GL30_YB20;       --国家临床手术3.0与医保2.0映射 (13653条)
select * from JHSYK_OPERATION_YB10_YB20;       --手术医保1.0与医保2.0映射 (13765条)

```

```sql
exp jhemr/jiahemk@192.168.17.47/JHEMRSUN file=D:\首页.dmp tables=EMR_WINFORM_TEMPLET,EMR_FIRST_PAGE_VALIDATE_TYPE,EMR_FIRST_PAGE_VALIDATE,EMR_FIRST_PAGE_THIRDSOURCE_LOG,EMR_FIRST_PAGE_THIRDSOURCE,EMR_FIRST_PAGE_PRINT,EMR_FIRST_PAGE_ITEM_DICT,EMR_FIRST_PAGE_DIAG_OPER_FRE,EMR_FIRST_PAGE_CONTROL_DICT,EMR_FIRST_PAGE_CONTROL_CONFIG,EMR_FIRST_PAGE_CONTROL_COLOR,EMR_FIRST_PAGE_CONFIG,EMR_FIRST_PAGE_COLUMN_INDEX,EMR_FIRST_PAGE_COLUMN_DICT,EMR_FIRST_PAGE_BUTTON_LIST
```

# 数据库表修改
## 2024.03.19
```sql
-- Create table
create table EMR_FIRST_PAGE_SAVE_LOG
(
  patient_id      VARCHAR2(50) not null,
  visit_id        NUMBER(4) not null,
  SAVE_date      DATE not null,
  SAVE_user_Id         VARCHAR2(20),
  hospital_no     VARCHAR2(50) not null,
  SAVE_comment   VARCHAR2(40),
  SAVE_host_ip   VARCHAR2(50),
  SAVE_host_name VARCHAR2(50),
  SAVE_host_mac  VARCHAR2(50)
)
tablespace TSP_JHEMR;
-- Add comments to the table 
comment on table EMR_FIRST_PAGE_SAVE_LOG
  is '病案首页-日志类-首页保存日志';
-- Add comments to the columns 
comment on column EMR_FIRST_PAGE_SAVE_LOG.SAVE_date
  is '保存时间';
comment on column EMR_FIRST_PAGE_SAVE_LOG.SAVE_user_Id
  is '保存人';
comment on column EMR_FIRST_PAGE_SAVE_LOG.patient_id
  is '患者ID';
comment on column EMR_FIRST_PAGE_SAVE_LOG.visit_id
  is '患者住院次';
comment on column EMR_FIRST_PAGE_SAVE_LOG.hospital_no
  is '医院编码';
comment on column EMR_FIRST_PAGE_SAVE_LOG.SAVE_comment
  is '保存备注';
comment on column EMR_FIRST_PAGE_SAVE_LOG.SAVE_host_ip
  is '保存主机IP';
comment on column EMR_FIRST_PAGE_SAVE_LOG.SAVE_host_name
  is '保存主机名称';
comment on column EMR_FIRST_PAGE_SAVE_LOG.SAVE_host_mac
  is '保存主机物理地址';
-- Create/Recreate primary, unique and foreign key constraints 
alter table EMR_FIRST_PAGE_SAVE_LOG
  add constraint PK_EMR_FIRST_PAGE_SAVE_LOG primary key (SAVE_date, PATIENT_ID, VISIT_ID, HOSPITAL_NO)
  using index 
  tablespace TSP_JHEMR;
```

```sql
-- Add/modify columns 
alter table PAT_VISIT add discharge_first_save_time date;
-- Add comments to the columns 
comment on column PAT_VISIT.discharge_first_save_time
  is '患者出院后，第一次保存首页时间';
```