guangdong-medical-record-sql（guangdong medical record SQL 搜索）

广东省病案统计管理系统 SQL 查询大师

本技能基于《广东省医疗机构病案统计管理系统(2012版)》数据库表结构文档构建，精通该系统所有表的SQL查询。

一、系统概览

系统: 广东省医疗机构病案统计管理系统（2012版） 数据库类型: 通常为 SQL Server 或 Oracle 核心模块: 标准编码 / 门诊日志 / 住院病案 / 报表统计 / 医技 / 妇婴

二、核心表清单

2.1 标准编码表

2.2 系统字典表

2.3 门诊工作日志表

2.4 住院病案核心表

2.5 住院工作日志表

2.6 报表主表

2.7 病案质量与核查

2.8 综合查询（自定义）

三、常用SQL查询示例

3.1 门诊统计

【门诊日志】按科室按日统计诊疗人次

SELECT 
    CONVERT(VARCHAR(10), wl.FDate, 120) AS 统计日期,
    w.FName AS 科室名称,
    SUM(wl.FZsrs) AS 医生人数,
    SUM(wl.FZzlrc) AS 诊疗人次,
    SUM(wl.FZzlygrc) AS 主诊疗人次,
    SUM(wl.FZlzrc) AS 留诊人次
FROM tWorklogNoDoctor wl
JOIN tWorkroom w ON wl.FTKh = w.FTKh
WHERE wl.FDate BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY wl.FDate, w.FName
ORDER BY wl.FDate, w.FName;

【门诊医生工作量】按医生统计月工作量

SELECT 
    w.FName AS 科室,
    d.FName AS 医生姓名,
    d.FZhicheng AS 职称,
    SUM(l.FZzlrc) AS 总诊疗人次,
    SUM(l.FZlzrc) AS 留诊人次,
    SUM(l.FSsusrc) AS 手术人次,
    COUNT(DISTINCT CONVERT(VARCHAR(7), l.FDate, 120)) AS 出诊天数
FROM tWorklog l
JOIN tdoctor d ON l.FGh = d.FGh
JOIN tWorkroom w ON l.FTKh = w.FTKh
WHERE l.FDate BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY w.FName, d.FName, d.FZhicheng
ORDER BY SUM(l.FZzlrc) DESC;

【急诊日志】按日统计急诊情况

SELECT 
    CONVERT(VARCHAR(10), el.FDate, 120) AS 日期,
    SUM(el.FJzsrc) AS 急诊人次,
    SUM(el.FQzysrc) AS 抢救人次,
    SUM(el.FQzcgysrc) AS 抢救成功人次,
    CASE WHEN SUM(el.FJzsrc) > 0 
         THEN ROUND(SUM(el.FQzcgysrc) * 100.0 / SUM(el.FJzsrc), 2) 
         ELSE 0 END AS 抢救成功率Pct
FROM tEmergeLogNoKs el
WHERE el.FDate BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY el.FDate
ORDER BY el.FDate;

【观察室日志】

SELECT 
    CONVERT(VARCHAR(10), ol.FDate, 120) AS 日期,
    SUM(ol.FGcsbeds) AS 观察室床位数,
    SUM(ol.FYbrs) AS 现有病人数,
    SUM(ol.FCsbrs) AS 出室病人数,
    SUM(ol.FCssmws) AS 出室病人死亡,
    SUM(ol.FQzbingsrc) AS 抢救病人数,
    SUM(ol.FCzbrs) AS 出室病人占床日数
FROM tObservelogNoKs ol
WHERE ol.FDate BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY ol.FDate
ORDER BY ol.FDate;

3.2 住院病案查询

【病案首页】查询指定时间段出院病人

SELECT 
    pv.FBaNo AS 病案号,
    pv.FFromHos AS 出院科室,
    CONVERT(VARCHAR(10), pv.FOutDate, 120) AS 出院日期,
    pv.FInDays AS 住院天数,
    d.FDiagName AS 主要诊断,
    d.FICDCode AS 主要诊断ICD,
    o.FOperaName AS 手术名称,
    o.FOperaDate AS 手术日期,
    o.FMZDoctorName AS 麻醉医生,
    pv.FCureRate AS 治疗效果
FROM tPatientVisit pv
LEFT JOIN (
    SELECT FPatientID, FDiagName, FICDCode
    FROM tDiagnose WHERE FType = 1  -- 主诊断
) d ON pv.FPatientID = d.FPatientID
LEFT JOIN (
    SELECT FPatientID, FOperaName, FOperaDate, FMZDoctorName
    FROM tOperation WHERE FSeq = 1  -- 主手术
) o ON pv.FPatientID = o.FPatientID
WHERE pv.FOutDate BETWEEN '2024-01-01' AND '2024-01-31'
  AND pv.FStatus = '已出院'
ORDER BY pv.FOutDate DESC;

【诊断查询】按ICD编码统计疾病

SELECT 
    diag.FICDCode AS ICD编码,
    icd.FICDName AS 疾病名称,
    COUNT(*) AS 病例数,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS 构成比Pct
FROM tDiagnose diag
JOIN tICD icd ON diag.FICDCode = icd.FICDCode
WHERE diag.FType = 1  -- 主诊断
  AND diag.FDiagDate BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY diag.FICDCode, icd.FICDName
HAVING COUNT(*) >= 5  -- 过滤小于5例
ORDER BY COUNT(*) DESC;

【手术查询】按手术医生统计

SELECT 
    o.FOperaDoctorName AS 手术医生,
    d.FZhicheng AS 职称,
    COUNT(*) AS 手术总例数,
    SUM(CASE WHEN o.FQzxSzbh = 'I' THEN 1 ELSE 0 END) AS 一类切口,
    SUM(CASE WHEN o.FQzxSzbh = 'II' THEN 1 ELSE 0 END) AS 二类切口,
    SUM(CASE WHEN o.FQzxSzbh = 'III' THEN 1 ELSE 0 END) AS 三类切口,
    SUM(CASE WHEN o.FYhyhdj = '甲' THEN 1 ELSE 0 END) AS 甲级愈合
FROM tOperation o
JOIN tdoctor d ON o.FOperaDoctorCode = d.FGh
WHERE o.FOperaDate BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY o.FOperaDoctorName, d.FZhicheng
ORDER BY COUNT(*) DESC;

【转科查询】住院期间转科记录

SELECT 
    pv.FBaNo AS 病案号,
    pv.FName AS 姓名,
    sk.FInDate AS 转入日期,
    w1.FName AS 转出科室,
    w2.FName AS 转入科室
FROM tSwitchKs sk
JOIN tPatientVisit pv ON sk.FPatientID = pv.FPatientID
JOIN tWorkroom w1 ON sk.FFromDept = w1.FTKh
JOIN tWorkroom w2 ON sk.FToDept = w2.FTKh
WHERE sk.FInDate BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY pv.FBaNo, sk.FInDate;

【科室病床占用情况】

SELECT 
    CONVERT(VARCHAR(10), wl.FDate, 120) AS 日期,
    w.FName AS 科室,
    wl.FCyrys AS 出院人数,
    wl.FZyrs AS 在院人数,
    wl.FBeds AS 病床数,
    ROUND(wl.FBedsOccRate, 2) AS 病床占用率
FROM TZyWardWorklog wl
JOIN tWorkroom w ON wl.FWardCode = w.FTKh
WHERE wl.FDate BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY wl.FDate, w.FName;

3.3 ICD编码与疾病分类

【ICD查询】精确查找ICD编码

-- ICD-10诊断编码精确查询
SELECT 
    FICDCode AS 编码,
    FICDName AS 名称,
    FStdClass AS 分类
FROM tICD
WHERE FICDCode = 'I10' 
   OR FICDName LIKE '%高血压%';

-- 按章节统计ICD使用频次
SELECT 
    LEFT(diag.FICDCode, 3) AS 章节码,
    COUNT(*) AS 使用次数
FROM tDiagnose diag
WHERE diag.FDiagDate BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY LEFT(diag.FICDCode, 3)
ORDER BY COUNT(*) DESC;

【单病种查询】

SELECT 
    sd.FCode AS 病种码,
    sd.FName AS 病种名称,
    sd.FPjhjs AS 平均住院日,
    sd.FZyyhls AS 治愈好转率,
    sd.FZdqds AS 平均确诊天数
FROM tSDISEASE sd
WHERE sd.FIsMainOnly = 1  -- 只统计主诊断
ORDER BY sd.FCode;

【手术编码查询】

SELECT 
    FOperaCode AS 手术编码,
    FOperaName AS 手术名称,
    FFlName AS 手术分类
FROM tOperate
WHERE FOperaName LIKE '%子宫切除%'
   OR FOperaCode LIKE '68%';

3.4 报表统计

【住院报表主表】按月汇总

SELECT 
    rm.FReportDateStr AS 报表月份,
    rm.FReportType AS 报表类型,
    rm.FStartDate AS 开始日期,
    rm.FEndDate AS 结束日期,
    rm.FStatus AS 报表状态,
    u.FUserName AS 汇总人
FROM TZyReportMain rm
LEFT JOIN TSYSUSER u ON rm.FHzUserCode = u.FUserCode
WHERE rm.FReportDateStr LIKE '2024-01%'
ORDER BY rm.FReportDateStr;

【十种疾病术后十日内死亡统计】

SELECT 
    r.FReportDateStr AS 报表月份,
    jf.FName AS 疾病类别,
    op.FName AS 手术类别,
    r.FFromHosNum AS 出院人数,
    r.FOptNum AS 手术人数,
    r.FDead10Num AS 术后10日内死亡人数,
    CASE WHEN r.FOptNum > 0 
         THEN ROUND(r.FDead10Num * 100.0 / r.FOptNum, 2) 
         ELSE 0 END AS 死亡率Pct
FROM TZy20ICDDeadInTenReport r
JOIN tJBFLB jf ON r.FJbflCode = jf.FCode
JOIN tOperateFlb op ON r.FOptCode = op.FCode
WHERE r.FReportDateStr LIKE '2024-01%'
ORDER BY r.FDead10Num DESC;

【经济收入报表】

SELECT 
    r.FReportDateStr AS 月份,
    r.FYwSrTotal AS 业务收入合计,
    r.FMzSrTotal AS 门诊收入合计,
    r.FZySrTotal AS 住院收入合计,
    r.FYwZcTotal AS 业务支出合计,
    ROUND((r.FYwSrTotal - r.FYwZcTotal) * 1.0 / r.FYwSrTotal * 100, 2) AS 收支结余率,
    r.FPjZcyDays AS 出院者平均住院日,
    r.FPjZyF AS 出院者平均费用
FROM TZyHosEcInOutReport r
WHERE r.FReportDateStr BETWEEN '2024-01' AND '2024-12'
ORDER BY r.FReportDateStr;

3.5 病案质量核查

【首页核查】查询未通过核查的病案

SELECT 
    pv.FBaNo AS 病案号,
    pv.FFromHos AS 科室,
    b.FFieldName AS 核查字段,
    b.FErrorMsg AS 错误信息,
    b.FChkDate AS 核查日期
FROM TBasyBalance b
JOIN tPatientVisit pv ON b.FPatientID = pv.FPatientID
WHERE b.FResult = 0  -- 未通过
  AND b.FChkDate BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY pv.FFromHos, b.FChkDate;

【病案借阅查询】

SELECT 
    lv.FBarCode AS 病案条码,
    pv.FName AS 病人姓名,
    lv.FOutDate AS 借出日期,
    lv.FPlanReturnDate AS 应还日期,
    CASE WHEN lv.FIsReturn = 1 THEN '已还' ELSE '未还' END AS 状态,
    lv.FReaderDept AS 借阅科室,
    lv.FReaderName AS 借阅人
FROM tbaLend lv
JOIN tPatientVisit pv ON lv.FPatientID = pv.FPatientID
WHERE lv.FOutDate BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY lv.FIsReturn, lv.FOutDate;

3.6 医技与特殊报表

【医技工作量报表】

SELECT 
    r.FReportDateStr AS 月份,
    r.FGcsrc AS 观察室人次,
    r.FYjksrc AS 医技科室人次,
    r.FZzrc AS 诊察人次,
    r.FJcxsrc AS 检查想人次,
    r.FZlxsrc AS 治疗想人次
FROM tMedicalTechLog r
WHERE r.FReportDateStr LIKE '2024-01%';

【家庭病床报表】

SELECT 
    r.FReportDateStr AS 月份,
    r.FCcbrs AS 撤床病人数,
    r.FCcbrzrc AS 撤床病人诊疗人次,
    r.FTnskbs AS 期内开设总病床数,
    r.FZcbrzrs AS 期内总撤床病日数
FROM TZyHomeBedReport r
WHERE r.FReportDateStr BETWEEN '2024-01' AND '2024-12';

四、字段命名规则（字段前缀对照）

五、常用条件与过滤

日期范围

-- 日期字段通常是 FDate 或 FOutDate (出院日期)
WHERE FDate >= '2024-01-01' AND FDate < '2024-02-01'
-- 或字符串格式
WHERE FReportDateStr BETWEEN '2024-01' AND '2024-12'

主诊断/主手术

-- 诊断类型: FType = 1 为主诊断, 2+ 为其他诊断
WHERE FType = 1
-- 手术 FSeq = 1 为主手术
WHERE FSeq = 1

报表状态

-- 0=日报, 1=月报, 2=季度报, 3=年度报
WHERE FReportType = 1
-- 报表状态: 草稿/已提交/已审核
WHERE FStatus IN ('已提交', '已审核')

切口愈合等级

-- 甲级愈合 / 乙级愈合 / 丙级愈合
WHERE FYhyhdj = '甲'

治疗效果

-- 治愈 / 好转 / 未愈 / 死亡 / 其他
WHERE FCureRate = '治愈'

六、查询技巧

6.1 关联HIS系统数据

-- 对应HIS_MZLOG1等HIS接口表
SELECT * FROM HIS_MZLOG1 
WHERE FReportDate = '2024-01-15';

6.2 拼音码查询

-- 医生/科室拼音查询（通过TWinpy表关联）
SELECT d.FGh, d.FName, wp.FFirstLetter AS 拼音首字母
FROM tdoctor d
LEFT JOIN TWinpy wp ON d.FName = wp.FHz
WHERE wp.FFirstLetter LIKE 'ZXK%';

6.3 分页查询（大数据量）

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY FOutDate DESC) AS RowNum,
           pv.*
    FROM tPatientVisit pv
    WHERE pv.FOutDate >= '2024-01-01'
) t
WHERE t.RowNum BETWEEN 1 AND 50;

6.4 医保病案上传状态

SELECT * FROM UPLOADZYBA 
WHERE FMedInsCode = '贵阳市医保'
  AND FUploadDate IS NOT NULL;

七、注意事项

1. 日期字段: 不同表日期字段不同，注意 FDate(日志) vs FOutDate(出院) vs FInDate(入院)
2. 编码对应: 使用 FTKh(统一科号) 而非科室名称进行关联
3. 报表月份: 报表常用字符串格式如 YYYY-MM，需用 LIKE '2024-01%'
4. 空值处理: 出院病人若无手术，tOperation 联合查询时用 LEFT JOIN
5. HIS接口: HIS系统传入数据存储在 HIS_* 前缀表中，可用于数据核对

💡 使用提示: 提问时尽量说明"查什么"、"时间段"、"按什么维度汇总"，我可生成更精准的SQL语句。