MYSQL запросы

Материал из Wiki МИАЦ ВО
Версия от 18:01, 21 апреля 2020; Misha (обсуждение | вклад) (→‎Найти одинаковые строки)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к навигации Перейти к поиску

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

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 
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

Статистика количества дней ожидания для записи через интернет

За полгода с учетом Листов ожидания. 

SELECT 
sum(if (t.dw in (0,1), 1,0)) as d01,
sum(if (t.dw in (2,3), 1,0)) as d23,
sum(if (t.dw in (4,5), 1,0)) as d45,
sum(if (t.dw in (6,7), 1,0)) as d67,
sum(if (t.dw in (8,9), 1,0)) as d89,
sum(if (t.dw in (10,11), 1,0)) as d1011,
sum(if (t.dw in (12,13), 1,0)) as d1213,
sum(if (t.dw in (14,15), 1,0)) as d1415,
sum(if (t.dw in (16,17), 1,0)) as d1617,
sum(if (t.dw in (18,19), 1,0)) as d1819,
sum(if (t.dw in (20,21), 1,0)) as d2021,
sum(if (t.dw in (22,23), 1,0)) as d2223,
sum(if (t.dw in (24,25), 1,0)) as d2425,
sum(if (t.dw in (26,27), 1,0)) as d2627
from zapis z
LEFT JOIN (
	SELECT if( c.time_insert is NULL , TO_DAYS(a.dateP) - TO_DAYS(a.timeInsert),  TO_DAYS(b.dateP) - TO_DAYS(c.time_insert)) as dw, a.id
	from zapis a 
	left JOIN lk_wl_zap b on (a.RunOtd = b.id_otd and a.idLPU = b.idlpu and a.idProfil = b.id_profil and a.idPrsnl = b.id_prsnl and a.dateP = b.dateP and a.timeP= b.timeP and b.`status` = 2)
	left JOIN lk_wait_list c on (c.id = b.id_wl)
	where a.dateP > '2017-06-01' and a.dateP < '2018-01-01' and a.`status`=0) as t on (t.id = z.id)
Источник — https://miac.volmed.org.ru/wiki/index.php?title=MYSQL_запросы&oldid=4455