数据库常用数据表结构

🧾 数据类型对照表

明细表

-- auto-generated definition
create table detail
(
    `就诊id`           String,
    `费用发生时间`     String,
    `结算id`           String,
    `定点医药机构编号` String,
    `定点医药机构名称` String,
    `人员编号`         String,
    `数量`             Decimal(10, 2),
    `单价`             Decimal(18, 2),
    `明细项目费用总额` Decimal(18, 2),
    `定价上限金额`     Nullable(Decimal(18, 2)),
    `自付比例`         Nullable(Decimal(5, 4)),
    `全自费金额`       Nullable(Decimal(18, 2)),
    `超限价自费费用`   Nullable(Decimal(18, 2)),
    `先行自付金额`     Decimal(18, 2),
    `符合范围金额`     Decimal(18, 2),
    `收费项目等级`     Nullable(String),
    `医保目录名称`     String,
    `开单医师姓名`     Nullable(String),
    `受单医师姓名`     Nullable(String),
    `报销比例`         Nullable(String)
)
    engine = MergeTree ORDER BY (`结算id`, `就诊id`, `费用发生时间`)
        SETTINGS index_granularity = 8192;

待遇表

-- auto-generated definition
create table treatment
(
    `结算id`                   String,
    `就诊id`                   String,
    `姓名`                     String,
    `性别`                     LowCardinality(String),
    `身份证号`                 String,
    `年龄`                     UInt8,
    `参保区划`                 String,
    `人员类别`                 LowCardinality(String),
    `特殊人员类别`             LowCardinality(String),
    `参保单位名称`             String,
    `定点医疗机构代码`         String,
    `定点医疗机构名称`         String,
    `医疗类别`                 LowCardinality(String),
    `入院主诊断名称`           String,
    `就诊入院时间(day)`        Nullable(String),
    `就诊出院时间(day)`        Nullable(String),
    `住院天数`                 Nullable(UInt16),
    `结算时间`                 DateTime,
    `医疗费总额`               Decimal(18, 2),
    `全自费金额`               Decimal(18, 2),
    `超限价自费费用`           Decimal(18, 2),
    `先行自付金额`             Decimal(18, 2),
    `符合范围金额`             Decimal(18, 2),
    `实际支付起付线`           Decimal(18, 2),
    `基本医疗统筹支付比例`     Decimal(5, 4),
    `基金支付总额`             Decimal(18, 2),
    `统筹基金支出`             Decimal(18, 2),
    `公务员医疗补助资金支出`   Decimal(18, 2),
    `补充医疗保险基金支出`     Decimal(18, 2),
    `大病补充医疗保险基金支出` Decimal(18, 2),
    `大额医疗补助基金支出`     Decimal(18, 2),
    `伤残人员医疗保障基金支出` Decimal(18, 2),
    `医疗救助基金支出`         Decimal(18, 2),
    `其它基金支付`             Decimal(18, 2),
    `个人支付金额`             Decimal(18, 2),
    `个人账户支出`             Decimal(18, 2),
    `范围外金额`               Decimal(18, 2),
    `现金支付金额`             Decimal(18, 2),
    `工会互助基金`             Decimal(18, 2),
    `政策范围内自付金额`       Decimal(18, 2),
    `介质类型`                 LowCardinality(String),
    `结算工作人员姓名`         String,
    `出院主诊断名称`           Nullable(String),
    `手机号码`                 Nullable(String),
    `联系方式`                 Nullable(String)
)
    engine = MergeTree ORDER BY (`结算id`, `就诊id`, `结算时间`)
        SETTINGS index_granularity = 8192;

表连接

DROP TABLE  if exists union_result;
CREATE TABLE union_result
    ENGINE = MergeTree
        ORDER BY (`结算id`, `就诊id`, `结算时间`,`费用发生时间`)
AS
SELECT DISTINCT
    a.`结算id`,
    a.`就诊id`,
    a.`姓名`,
    a.`性别`,
    a.`身份证号`,
    a.`年龄`,
    a.`参保区划`,
    a.`人员类别`,
    a.`特殊人员类别`,
    a.`参保单位名称`,
    a.`定点医疗机构代码`,
    a.`定点医疗机构名称`,
    a.`医疗类别`,
    a.`入院主诊断名称`,
    a.`就诊入院时间(day)`,
    a.`就诊出院时间(day)`,
    a.`住院天数`,
    a.`结算时间`,
    a.`医疗费总额`,
    a.`全自费金额`,
    a.`超限价自费费用`,
    a.`先行自付金额`,
    a.`符合范围金额`,
    a.`实际支付起付线`,
    a.`基本医疗统筹支付比例`,
    a.`基金支付总额`,
    a.`统筹基金支出`,
    a.`补充医疗保险基金支出`,
    a.`大额医疗补助基金支出`,
    a.`医疗救助基金支出`,
    b.`费用发生时间`,
    b.`人员编号`,
    b.`数量`,
    b.`单价`,
    b.`明细项目费用总额`,
    b.`定价上限金额`,
    b.`自付比例`,
    b.`全自费金额` as `单项目全自费金额`,
    b.`超限价自费费用` as `单项目超限价自费费用`,
    b.`先行自付金额` as `单项目先行自付金额`,
    b.`符合范围金额` as `单项目符合范围金额`,
    b.`医保目录名称`,
    b.`开单医师姓名`,
    b.`受单医师姓名`
FROM treatment a
         JOIN detail b ON a.`结算id` = b.`结算id`;