MYSQL запросы: различия между версиями

Материал из Wiki МИАЦ ВО
Перейти к навигации Перейти к поиску
(→‎UPDATE 2 таблиц)
(→‎Найти одинаковые строки)
 
(не показана 1 промежуточная версия этого же участника)
Строка 3: Строка 3:
 
from table1 a  
 
from table1 a  
 
where a.stb1> 0 and a.ctb2 >= 1  
 
where a.stb1> 0 and a.ctb2 >= 1  
−
GROUP BY a.stb1, a.stb2, a.stb3, a.stb4 DESC HAVING COUNT(*) > 1  
+
GROUP BY a.stb1, a.stb2, a.stb3, a.stb4  
 +
HAVING COUNT(*) > 1  
 
ORDER BY col1;  
 
ORDER BY col1;  
 
</pre>
 
</pre>
 
блок where - не обязателен.
 
блок where - не обязателен.
 +
 
==UPDATE 2 таблиц==
 
==UPDATE 2 таблиц==
 
<pre>UPDATE supply a  
 
<pre>UPDATE supply a  
Строка 110: Строка 112:
 
==Вывести поля таблицы==
 
==Вывести поля таблицы==
 
<pre>SHOW COLUMNS FROM tabl</pre>
 
<pre>SHOW COLUMNS FROM tabl</pre>
 +
==Статистика количества дней ожидания для записи через интернет==
 +
За полгода с учетом Листов ожидания.
 +
<pre>
 +
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)
 +
</pre>

Текущая версия на 18:01, 21 апреля 2020

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

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