MySQL:用新行扩展表，包括两个表数据

Question

我的 table 足以满足我在项目开始时所需的内容，但现在我需要扩展它。

表s_ogr

id | ad | numara | yurt_id | egitim_id | yetkili_id | yetkili_ad

当前表s_kontrol

id | ogr_id | tur_id | durum_id | tarih | egitim_id | yurt_id

需要什么s_kontrol_k

id | ogr_id | ogr_numara |ogr_ad | tur_id | durum_id | tarih | egitim_id | yurt_id

行说明 ad = name 、ogr_id 为 student_id 、yurt_id是 department_id

我的情况； s_kontrol 充满了超过 24000 条数据。我需要它来扩展 s_kontrol_k ，但是新的两个字段应该从 s_ogr 表中通过匹配获取s_ogr 和 s_kontrol 的操作(s_ogr.id=s_kontrol.ogr_id 和 s_ogr.yurt_id =s_kontrol.yurt_id)。我发现我的错误是没有将 ogr_id 作为学生的 id 添加到 s_kontrol

我需要什么；一个有用的查询，从源复制数据，但如何用正确的值填充第三个表中的新字段

INSERT INTO s_kontrol_k ( id ,  ogr_id ,  tur_id ,  durum_id ,  tarih ,  egitim_id ,  yurt_id )

SELECT  id ,  ogr_id ,  tur_id ,  durum_id ,  tarih ,  egitim_id ,  yurt_id  FROM s_kontrol 

我遇到问题的查询就是我需要一个简单表格的原因

SELECT s_kontrol.ogr_id,
s_ogr.ad, 
s_kontrol.tur_id, 
s_kontrol.durum_id,
SUM(case s_kontrol.durum_id WHEN 1 THEN 1 else 0 end) var,
SUM(case s_kontrol.durum_id WHEN 2 THEN 1 else 0 end) gorevli,
SUM(case s_kontrol.durum_id WHEN 3 THEN 1 else 0 end) yok,
SUM(case s_kontrol.durum_id WHEN 4 THEN 1 else 0 end) izinli,
SUM(case s_kontrol.durum_id WHEN 5 THEN 1 else 0 end) hatimde 
FROM s_kontrol, s_ogr 
WHERE s_kontrol.ogr_id=s_ogr.numara 
AND s_kontrol.yurt_id=s_ogr.yurt_id 
and s_ogr.yurt_id=? 
and tarih BETWEEN ? and ? 
and tur_id IN (?) 
GROUP BY s_kontrol.ogr_id

第二个

select deneme.ogr_id,
deneme.ad,
sum(case deneme.tur_id WHEN 1 THEN YUZDE else 0 END) sabah ,
sum(case deneme.tur_id WHEN 2 THEN YUZDE else 0 END) ogle,
sum(case deneme.tur_id WHEN 4 THEN YUZDE else 0 END) aksam,
sum(case deneme.tur_id WHEN 5 THEN YUZDE else 0 END) yatsi,
sum(case deneme.tur_id WHEN 6 THEN YUZDE else 0 END) sohbet,
sum(case deneme.tur_id WHEN 7 THEN YUZDE else 0 END) muhtelif 
FROM (
      SELECT s_kontrol.ogr_id, 
      s_ogr.ad,tur_id, 
      CEILING(((SUM(case s_kontrol.durum_id WHEN 1 THEN 1 else 0 end)
      +SUM(case s_kontrol.durum_id WHEN 2 THEN 1 else 0 end)
      +SUM(case s_kontrol.durum_id WHEN 3 THEN 1 else 0 end)
      +SUM(case s_kontrol.durum_id WHEN 4 THEN 1 else 0 end)
      +SUM(case s_kontrol.durum_id WHEN 5 THEN 1 else 0 end) )
      -(SUM(case s_kontrol.durum_id WHEN 3 THEN 1 else 0 end)))
      /(SUM(case s_kontrol.durum_id WHEN 1 THEN 1 else 0 end)
      +SUM(case s_kontrol.durum_id WHEN 2 THEN 1 else 0 end)
      +SUM(case s_kontrol.durum_id WHEN 3 THEN 1 else 0 end)
      +SUM(case s_kontrol.durum_id WHEN 4 THEN 1 else 0 end)
      +SUM(case s_kontrol.durum_id WHEN 5 THEN 1 else 0 end) ) 
      * 100) YUZDE 
      FROM s_kontrol, s_ogr 
      WHERE s_kontrol.ogr_id=s_ogr.numara 
      and s_ogr.yurt_id=? 
      and tarih BETWEEN ? and ? 
      and tur_id IN(1,2,3,4,5,6,7) 
      GROUP BY s_kontrol.ogr_id,s_ogr.ad, tur_id 
     ) deneme 
group by ogr_id,ad order by 1

Answer 1

您可以尝试如下所示的操作。

INSERT INTO s_kontrol_k 
( id ,  ogr_id , ogr_ad , tur_id ,  durum_id ,  tarih ,  egitim_id ,  yurt_id )

SELECT  o.id ,  o.ogr_id ,  o.ogr_ad , 
s.tur_id ,  s.durum_id ,  s.tarih ,  s.egitim_id ,  s.yurt_id  
FROM 
s_kontrol s inner join s_ogr o on o.yurt_id =s.yurt_id;


INSERT INTO s_kontrol_k (
id, ogr_id, ogr_numara, ogr_ad, tur_id, 
durum_id, tarih, egitim_id, yurt_id) 
SELECT 
s_kontrol.id, 
s_ogr.id, 
s_kontrol.ogr_id, 
s_ogr.ad, 
s_kontrol.tur_id, 
s_kontrol.durum_id, 
s_kontrol.tarih, 
s_kontrol.egitim_id, 
s_kontrol.yurt_id FROM 
s_kontrol inner join  
s_ogr WHERE 
s_ogr.yurt_id = s_kontrol.yurt_id 
and s_ogr.numara = s_kontrol.ogr_id