MYSQL запросы

Материал из Wiki МИАЦ ВО
Версия от 15:59, 9 февраля 2017; Misha (обсуждение | вклад) (→‎UPDATE 2 таблиц)
Перейти к навигации Перейти к поиску

Найти одинаковые строки

select a.stb1, a.stb2, a.stb3, a.stb4, COUNT(*) AS col1 
from table1 a 
where a.stb1> 0 and a.ctb2 >= 1 
GROUP BY a.stb1, a.stb2, a.stb3, a.stb4 DESC HAVING COUNT(*) > 1 
ORDER BY col1;

блок where - не обязателен.

UPDATE 2 таблиц

UPDATE supply a 
INNER JOIN equipment b ON (a.IAR_NO= b.IAR_NO)
SET a.IAR_NO =  "9" , b.IAR_NO =  "9"
WHERE equipment.IAR_NO = 0 and supply.IAR_NO = 0;

update РМР из 1С

update registrmr2.e_cardrecord_tmp cr set cr.PositionType = (SELECT IF( EXISTS(select sp.id from s_positiontype sp WHERE cr.dop_kodEmployType=sp.ID_FROM_LPU ),(select sp.id from s_positiontype sp WHERE cr.dop_kodEmployType=sp.id_from_lpu),0)),spr_was_updated=1 WHERE cr.From_dop_data=1;
update registrmr2.e_cardrecord_tmp cr set cr.SubdivisionType = (SELECT IF( EXISTS(select sp.id from s_subdivision sp WHERE sp.id_from_lpu=cr.dop_unitorgkod AND cr.oid=sp.OID AND sp.IS_FEDERAL=0),(select sp.id from s_subdivision sp WHERE sp.id_from_lpu=cr.dop_unitorgkod AND cr.oid=sp.OID AND sp.IS_FEDERAL=0),0)),spr_was_updated=1 WHERE cr.From_dop_data=1;
update registrmr2.e_cardrecord_tmp cr set cr.Post = (SELECT IF( EXISTS(select sp.id from s_post sp WHERE sp.id_from_lpu=cr.dop_postkod AND cr.oid=sp.OID AND sp.IS_FEDERAL=0),(select sp.id from s_post sp WHERE sp.id_from_lpu=cr.dop_postkod AND cr.oid=sp.OID AND sp.IS_FEDERAL=0),0)),spr_was_updated=1 WHERE cr.From_dop_data=1;


INSERT INTO e_cardrecord (idpeople,OID,AdditionalLaborAgreement,Care,Conditions,DateBegin,DateEnd,IsActual,IsVacation,Military,OrderIn,OrderOut,Organization_OID,Organization_name,Population,PositionType,Post,PostType,Regime,SubdivisionName,SubdivisionType,TypeIn,TypeInAdd,TypeInCountry,TypeInGoIn,TypeInStudTarget,TypeInStudType,TypeInTerr,TypeOut,TypeOutCountry,TypeOutDel,TypeOutGoIn,TypeOutTerr,Wage,From_dop_data,Spr_was_updated,Dop_Typecontract,Dop_Numbercontract,Dop_Datecontract,Dop_KodEmployType,Dop_Unitorgkod,Dop_Postkod,Dop_Post,Dop_KodCategorypost,Dop_Categorypost,Dop_Ratebat,TIMEMODIFY)
  (SELECT cr1.idpeople,cr1.OID,cr1.AdditionalLaborAgreement,cr1.Care,cr1.Conditions,cr1.DateBegin,cr1.DateEnd,cr1.IsActual,cr1.IsVacation,cr1.Military,cr1.OrderIn,cr1.OrderOut,cr1.Organization_OID,cr1.Organization_name,cr1.Population,cr1.PositionType,cr1.Post,cr1.PostType,cr1.Regime,cr1.SubdivisionName,cr1.SubdivisionType,cr1.TypeIn,cr1.TypeInAdd,cr1.TypeInCountry,cr1.TypeInGoIn,cr1.TypeInStudTarget,cr1.TypeInStudType,cr1.TypeInTerr,cr1.TypeOut,cr1.TypeOutCountry,cr1.TypeOutDel,cr1.TypeOutGoIn,cr1.TypeOutTerr,cr1.Wage,cr1.From_dop_data,cr1.Spr_was_updated,cr1.Dop_Typecontract,cr1.Dop_Numbercontract,cr1.Dop_Datecontract,cr1.Dop_KodEmployType,cr1.Dop_Unitorgkod,cr1.Dop_Postkod,cr1.Dop_Post,cr1.Dop_KodCategorypost,cr1.Dop_Categorypost,cr1.Dop_Ratebat,cr1.TIMEMODIFY
FROM registrmr2.e_cardrecord_tmp AS cr1
WHERE (cr1.idpeople,cr1.OID,cr1.DateBegin,cr1.PositionType,cr1.Post) NOT IN (SELECT cr.idpeople,cr.OID,cr.DateBegin,cr.PositionType,cr.Post FROM registrmr2.e_cardrecord  cr 
));

DELETE FROM registrmr2.e_cardrecord_tmp
WHERE (idpeople,OID,DateBegin,PositionType,Post) IN (SELECT cr.idpeople,cr.OID,cr.DateBegin,cr.PositionType,cr.Post FROM registrmr2.e_cardrecord  cr WHERE cr.spr_was_updated=1);

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2, cr.AdditionalLaborAgreement=(IF (cr1.AdditionalLaborAgreement>0 AND cr1.AdditionalLaborAgreement IS NOT null,cr1.AdditionalLaborAgreement,cr.AdditionalLaborAgreement)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post ;
  ;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Care=(IF (cr1.Care>0 AND cr1.Care IS NOT null,cr1.Care,cr.Care)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post ;
  ;


update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Conditions=(IF (cr1.Conditions>0 AND cr1.Conditions IS NOT null,cr1.Conditions,cr.Conditions)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post ;
  ;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.IsActual=(IF (cr1.IsActual>0 AND cr1.IsActual IS NOT null,cr1.IsActual,cr.IsActual)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post ;
  ;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.IsVacation=(IF (cr1.IsVacation>0 AND cr1.IsVacation IS NOT null,cr1.IsVacation,cr.IsVacation)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post ;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Military=(IF (cr1.Military>0 AND cr1.Military IS NOT null,cr1.Military,cr.Military)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post ;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.SubdivisionName=(IF (length(cr1.SubdivisionName)>0 AND cr1.SubdivisionName IS NOT null,cr1.SubdivisionName,cr.SubdivisionName)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.SubdivisionType=(IF (cr1.SubdivisionType>0 AND cr1.SubdivisionType IS NOT null,cr1.SubdivisionType,cr.SubdivisionType)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Wage=(IF (cr1.Wage>0 AND cr1.Wage IS NOT null,cr1.Wage,cr.Wage)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Dop_Typecontract=(IF (LENGTH(cr1.Dop_Typecontract)>0 AND cr1.Dop_Typecontract IS NOT null,cr1.Dop_Typecontract,cr.Dop_Typecontract)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Dop_Numbercontract=(IF (LENGTH(cr1.Dop_Numbercontract)>0 AND cr1.Dop_Numbercontract IS NOT null,cr1.Dop_Numbercontract,cr.Dop_Numbercontract)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Dop_Datecontract=(IF (LENGTH(cr1.Dop_Datecontract)>0 AND cr1.Dop_Datecontract IS NOT null,cr1.Dop_Datecontract,cr.Dop_Datecontract)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Dop_KodEmployType=(IF (cr1.Dop_KodEmployType IS NOT null,cr1.Dop_KodEmployType,cr.Dop_KodEmployType)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Dop_Unitorgkod=(IF (cr1.Dop_Unitorgkod>0 AND cr1.Dop_Unitorgkod IS NOT null,cr1.Dop_Unitorgkod,cr.Dop_Unitorgkod)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Dop_Postkod=(IF (cr1.Dop_Postkod>0 AND cr1.Dop_Postkod IS NOT null,cr1.Dop_Postkod,cr.Dop_Postkod)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Dop_Post=(IF (LENGTH(cr1.Dop_Post)>0 AND cr1.Dop_Post IS NOT null,cr1.Dop_Post,cr.Dop_Post)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.Dop_Ratebat=(IF (cr1.Dop_Ratebat IS NOT null,cr1.Dop_Ratebat,cr.Dop_Ratebat)) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

update registrmr2.e_cardrecord AS cr, registrmr2.e_cardrecord_tmp AS cr1
set cr.From_dop_data=2,cr.TIMEMODIFY=(NOW()) where
cr1.idpeople=cr.idpeople AND cr1.OID=cr.OID AND cr.DateBegin=cr1.DateBegin AND cr.PositionType=cr1.PositionType AND cr.post=cr1.post;

TRUNCATE TABLE registrmr2.e_cardrecord_tmp;

Вывести поля таблицы

SHOW COLUMNS FROM tabl
Источник — https://miac.volmed.org.ru/wiki/index.php?title=MYSQL_запросы&oldid=4026