mysql - SQL中的多个字段中的一个字段中插入值

Question

我有一个大表，大约有 200 个字段。当从一个字段创建维度表时，其中大约 100 个字段将被映射到一个字段。问题是我必须像 100 个插入语句一样逐个添加这 100 个字段的值。有没有像循环之类的东西可以更有效地实现这一目标？

代码示例如下:

    insert into DimTableA(visit_no, patient, facility, icd9, icd9_flag, ip_op)
    select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_18,'d',IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient'),
    from svch_dischs3_s D3, svch_diags_s D

    insert into DimTableA(visit_no, patient, facility, icd9, icd9_flag, ip_op)
    select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_19,'d',IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient'),
    from svch_dischs3_s D3, svch_diags_s D

    insert into DimTableA(visit_no, patient, facility, icd9, icd9_flag, ip_op)
    select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_20,'d',IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient'),
    from svch_dischs3_s D3, svch_diags_s D;

…………等等

唯一更改的字段名称是“icd9”输入(即 D.final_diagnosis_18、final_diagnosis_19、final_diagnosis_20 ....)

任何帮助将不胜感激，小伙子们。 :)

Answer 1

您可以使用交叉连接将这些列反透视到一组固定的数字，如下所示:

insert into DimTableA (
  visit_no,
  patient,
  facility,
  icd9,
  icd9_flag,
  ip_op
)
select
  D.registration,
  D3.med_number,
  D3.Hosp_Id,
  case N.number
    when 1 then D.final_diagnosis_18
    when 2 then D.final_diagnosis_19
    when 3 then D.final_diagnosis_20
    ...
  end,
  'd',
  IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient')
from
  svch_dischs3_s D3,
  svch_diags_s D,
  (
    select 1 as number union all
    select 2           union all
    select 3           union all
    ...  /* up to the number of columns to unpivot */
  ) N

你可以 create and populate a persistent numbers table而不是内联 View ，并在查询中使用该表的必要子集。在那种情况下，查询将像这样改变

...
from
  svch_dischs3_s D3,
  svch_diags_s D,
  numbers N
where N.number between 1 and … /* the number of columns to unpivot */