MYSQL запросы - История изменений

Misha: /* Всю строки ФИО в нижний регистр, а первый символ в верхний */

2024-12-16T13:19:11Z

Всю строки ФИО в нижний регистр, а первый символ в верхний

← Предыдущая версия		Версия от 16:19, 16 декабря 2024
Строка 18:		Строка 18:
	<pre>		<pre>
	UPDATE doctor_data a SET		UPDATE doctor_data a SET
	a.surname = CONCAT(UPPER(LEFT(a.surname, 1)), SUBSTRING~~(LOWER~~(a.surname), 2~~, CHAR_LENGTH(a.surname~~) ~~- 1~~)),		a.surname = CONCAT(UPPER(LEFT(a.surname, 1)), LOWER(SUBSTRING(a.surname, 2))),
	a.first_name = CONCAT(UPPER(LEFT(a.first_name, 1)), SUBSTRING~~(LOWER~~(a.first_name), 2~~, CHAR_LENGTH(a.first_name~~) ~~- 1~~)),		a.first_name = CONCAT(UPPER(LEFT(a.first_name, 1)), LOWER(SUBSTRING(a.first_name, 2))),,
	a.second_name = CONCAT(UPPER(LEFT(a.second_name, 1)), SUBSTRING~~(LOWER~~(a.second_name ), 2~~, CHAR_LENGTH(a.second_name~~) ~~- 1~~))		a.second_name = CONCAT(UPPER(LEFT(a.second_name, 1)), LOWER(SUBSTRING(a.second_name, 2))),
	WHERE a.id > 0		WHERE a.id > 0
	</pre>		</pre>

Misha: /* UPDATE 2 таблиц */

2024-12-16T13:08:53Z

UPDATE 2 таблиц

← Предыдущая версия		Версия от 16:08, 16 декабря 2024
Строка 14:		Строка 14:
	SET a.IAR_NO = "9" , b.IAR_NO = "9"		SET a.IAR_NO = "9" , b.IAR_NO = "9"
	WHERE equipment.IAR_NO = 0 and supply.IAR_NO = 0;</pre>		WHERE equipment.IAR_NO = 0 and supply.IAR_NO = 0;</pre>

			==Всю строки ФИО в нижний регистр, а первый символ в верхний==
			<pre>
			UPDATE doctor_data a SET
			a.surname = CONCAT(UPPER(LEFT(a.surname, 1)), SUBSTRING(LOWER(a.surname), 2, CHAR_LENGTH(a.surname) - 1)),
			a.first_name = CONCAT(UPPER(LEFT(a.first_name, 1)), SUBSTRING(LOWER(a.first_name), 2, CHAR_LENGTH(a.first_name) - 1)),
			a.second_name = CONCAT(UPPER(LEFT(a.second_name, 1)), SUBSTRING(LOWER(a.second_name ), 2, CHAR_LENGTH(a.second_name) - 1))
			WHERE a.id > 0
			</pre>

	==update РМР из 1С==		==update РМР из 1С==

Misha: /* Найти одинаковые строки */

2020-04-21T15:01:06Z

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

← Предыдущая версия		Версия от 18:01, 21 апреля 2020
Строка 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

Misha: /* Вывести поля таблицы */

2018-04-11T06:35:24Z

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

← Предыдущая версия		Версия от 09:35, 11 апреля 2018
Строка 110:		Строка 110:
	==Вывести поля таблицы==		==Вывести поля таблицы==
	<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>

Misha: /* UPDATE 2 таблиц */

2017-02-09T12:59:12Z

UPDATE 2 таблиц

← Предыдущая версия		Версия от 15:59, 9 февраля 2017
Строка 8:		Строка 8:
	блок where - не обязателен.		блок where - не обязателен.
	==UPDATE 2 таблиц==		==UPDATE 2 таблиц==
	<pre>UPDATE supply a INNER JOIN equipment b ON (a.IAR_NO= b.IAR_NO)		<pre>UPDATE supply a
			INNER JOIN equipment b ON (a.IAR_NO= b.IAR_NO)
	SET a.IAR_NO = "9" , b.IAR_NO = "9"		SET a.IAR_NO = "9" , b.IAR_NO = "9"
	WHERE equipment.IAR_NO = 0 and supply.IAR_NO = 0;</pre>		WHERE equipment.IAR_NO = 0 and supply.IAR_NO = 0;</pre>

Misha: /* Найти одинаковые строки */

2017-02-09T12:58:46Z

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

← Предыдущая версия		Версия от 15:58, 9 февраля 2017
Строка 7:		Строка 7:
	</pre>		</pre>
	блок where - не обязателен.		блок where - не обязателен.
			==UPDATE 2 таблиц==
			<pre>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;</pre>

	==update РМР из 1С==		==update РМР из 1С==

Misha: /* update РМР из 1С */

2016-03-30T10:53:29Z

update РМР из 1С

← Предыдущая версия		Версия от 13:53, 30 марта 2016
Строка 104:		Строка 104:
	TRUNCATE TABLE registrmr2.e_cardrecord_tmp;		TRUNCATE TABLE registrmr2.e_cardrecord_tmp;
	</pre>		</pre>
			==Вывести поля таблицы==
			<pre>SHOW COLUMNS FROM tabl</pre>

Dmitriy: /* Найти одинаковые строки */

2015-11-30T15:20:16Z

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

← Предыдущая версия		Версия от 18:20, 30 ноября 2015
Строка 7:		Строка 7:
	</pre>		</pre>
	блок where - не обязателен.		блок where - не обязателен.


			==update РМР из 1С==
			<pre>
			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;
			</pre>

Misha в 19:10, 3 ноября 2015

2015-11-03T19:10:36Z

← Предыдущая версия		Версия от 22:10, 3 ноября 2015
Строка 6:		Строка 6:
	ORDER BY col1;		ORDER BY col1;
	</pre>		</pre>
			блок where - не обязателен.

Misha: Новая страница: «==Найти одинаковые строки==

select a.stb1, a.stb2, a.stb3, a.stb4, COUNT(*) AS col1  from table1 a  where a.stb1> 0 and a.ctb2 >= 1  GRO…»

2015-11-03T07:28:14Z

Новая страница: «==Найти одинаковые строки== <pre>select a.stb1, a.stb2, a.stb3, a.stb4, COUNT(*) AS col1 from table1 a where a.stb1> 0 and a.ctb2 >= 1 GRO…»

Новая страница

==Найти одинаковые строки==
<pre>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;
</pre>