纵表和横表的相互转换

原创已于 2023-03-24 14:13:07 修改 · 408 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #spring cloud

于 2023-03-24 14:05:11 首次发布

文章展示了两个数据库表，一个是`material_info_1678183618765`，存储物资基本信息，包括ID、类型、属性值等；另一个是`material_dict_data`，包含物资字典数据，如代码、标签、类型等。表中各字段均有详细注释。此外，还提供了一个SQL查询示例，将物资信息从竖表转为横表，以便于数据分析。

竖表结构

CREATE TABLE "public"."material_info_1678183618765" (
"id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
"mat_id" varchar(255) COLLATE "pg_catalog"."default",
"material_type" varchar(255) COLLATE "pg_catalog"."default",
"data_id" varchar(255) COLLATE "pg_catalog"."default",
"values" varchar(255) COLLATE "pg_catalog"."default",
"remark" varchar(255) COLLATE "pg_catalog"."default",
"create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
"create_by" varchar(64) COLLATE "pg_catalog"."default",
"update_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
"update_by" varchar(64) COLLATE "pg_catalog"."default",
"del_flag" char(1) COLLATE "pg_catalog"."default",
"tenant_id" int8
)
;

ALTER TABLE "public"."material_info_1678183618765"
OWNER TO "postgres";

COMMENT ON COLUMN "public"."material_info_1678183618765"."id" IS '主键';

COMMENT ON COLUMN "public"."material_info_1678183618765"."mat_id" IS '物资表主键';

COMMENT ON COLUMN "public"."material_info_1678183618765"."material_type" IS '物资类型';

COMMENT ON COLUMN "public"."material_info_1678183618765"."data_id" IS '物资属性id';

COMMENT ON COLUMN "public"."material_info_1678183618765"."values" IS '物资属性值';

COMMENT ON COLUMN "public"."material_info_1678183618765"."remark" IS '备注';

COMMENT ON COLUMN "public"."material_info_1678183618765"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."material_info_1678183618765"."create_by" IS '创建人';

COMMENT ON COLUMN "public"."material_info_1678183618765"."update_time" IS '修改时间';

COMMENT ON COLUMN "public"."material_info_1678183618765"."update_by" IS '修改人';

COMMENT ON COLUMN "public"."material_info_1678183618765"."del_flag" IS '是否删除（0-否；1-是）';

COMMENT ON COLUMN "public"."material_info_1678183618765"."tenant_id" IS '租户ID';

数据字典表结构

CREATE TABLE "public"."material_dict_data" (
"material_code" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
"material_sort" int4,
"material_label" varchar(100) COLLATE "pg_catalog"."default",
"material_value" varchar(100) COLLATE "pg_catalog"."default",
"material_type" varchar(100) COLLATE "pg_catalog"."default",
"is_import" char(1) COLLATE "pg_catalog"."default",
"status" char(1) COLLATE "pg_catalog"."default",
"create_by" varchar(64) COLLATE "pg_catalog"."default",
"create_time" timestamp(6),
"update_by" varchar(64) COLLATE "pg_catalog"."default",
"update_time" timestamp(6),
"remark" varchar(500) COLLATE "pg_catalog"."default",
"del_flag" char(1) COLLATE "pg_catalog"."default",
"is_public" char(1) COLLATE "pg_catalog"."default",
"input_type" varchar(64) COLLATE "pg_catalog"."default",
"is_validate" char(1) COLLATE "pg_catalog"."default",
"tenant_id" int8,
"dict_type" json
)
;

ALTER TABLE "public"."material_dict_data"
OWNER TO "postgres";

COMMENT ON COLUMN "public"."material_dict_data"."material_code" IS '主键';

COMMENT ON COLUMN "public"."material_dict_data"."material_sort" IS '排序值';

COMMENT ON COLUMN "public"."material_dict_data"."material_label" IS '物资标签';

COMMENT ON COLUMN "public"."material_dict_data"."material_value" IS '物资键值';

COMMENT ON COLUMN "public"."material_dict_data"."material_type" IS '物资类型';

COMMENT ON COLUMN "public"."material_dict_data"."is_import" IS '是否导入字段（0-否；1-是）';

COMMENT ON COLUMN "public"."material_dict_data"."status" IS '状态（0-启用；1-停用）';

COMMENT ON COLUMN "public"."material_dict_data"."create_by" IS '创建人';

COMMENT ON COLUMN "public"."material_dict_data"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."material_dict_data"."update_by" IS '修改人';

COMMENT ON COLUMN "public"."material_dict_data"."update_time" IS '修改时间';

COMMENT ON COLUMN "public"."material_dict_data"."remark" IS '备注';

COMMENT ON COLUMN "public"."material_dict_data"."del_flag" IS '是否删除（0-否；1-是）';

COMMENT ON COLUMN "public"."material_dict_data"."is_public" IS '是否公共配置（0-否；1-是）';

COMMENT ON COLUMN "public"."material_dict_data"."input_type" IS '输入类型';

COMMENT ON COLUMN "public"."material_dict_data"."is_validate" IS '是否验证（0-否；1-是）';

COMMENT ON COLUMN "public"."material_dict_data"."tenant_id" IS '租户ID';

COMMENT ON COLUMN "public"."material_dict_data"."dict_type" IS '下拉列表字典类型';

COMMENT ON TABLE "public"."material_dict_data" IS '物资参数表';

竖表转横表

SELECT
   i.mat_id,
   ( string_agg ( CASE d.material_value WHEN 'oldMatNo' THEN i.values ELSE '' END,'' ) ) AS oldMatNo,
   ( string_agg ( CASE d.material_value WHEN 'enterPort' THEN i.values ELSE '' END,'' ) ) AS enterPort,
   ( string_agg ( CASE d.material_value WHEN 'needPrint' THEN i.values ELSE '' END,'' ) ) AS needPrint,
   ( string_agg ( CASE d.material_value WHEN 'centraPurch' THEN i.values ELSE '' END,'' ) ) AS centraPurch,
   ( string_agg ( CASE d.material_value WHEN 'qualityIO' THEN i.values ELSE '' END,'' ) ) AS qualityIO,
   ( string_agg ( CASE d.material_value WHEN 'purTeam' THEN i.values ELSE '' END,'' ) ) AS purTeam,
   ( string_agg ( CASE d.material_value WHEN 'purCycle' THEN i.values ELSE '' END,'' ) ) AS purCycle,
   ( string_agg ( CASE d.material_value WHEN 'blueprint' THEN i.values ELSE '' END,'' ) ) AS blueprint
FROM
   material_info_1678183618765 i
LEFT JOIN material_dict_data d on d.material_code = i.data_id and d.material_type = i.material_type
GROUP BY
   i.mat_id