Jump to content
ForBiLL
KAGATAN

SQL запросы

Recommended Posts

Задача:

Найти пользователей у кого моб. номер из 10 символов,начинается с 9 => добавить 7 в начале

 

Ответ:

UPDATE `users`
SET `users`.`sms_tel` = CONCAT('7',`users`.`sms_tel`)
WHERE LENGTH( `users`.`sms_tel` ) =10
AND `users`.`sms_tel` LIKE '9%'
ORDER BY `users`.`sms_tel` ASC

Share this post


Link to post
Share on other sites

Задача:
Перевести абонентов в субпровайдеры по нас. пункту
 
 Ответ:

Сначала удалим из других субпровайдеров на всякий случай:

DELETE FROM `usersgroups_users` 
WHERE `usersgroups_users`.`uid`
IN (
SELECT `usersall`.`uid` FROM `usersall`
LEFT JOIN `lanes_houses` ON `usersall`.`houseid` = `lanes_houses`.`houseid`
LEFT JOIN `lanes` ON `lanes_houses`.`laneid` = `lanes`.`laneid`
WHERE  `lanes`.`settlementid` = 1
)

Затем только добавляем:
 

INSERT INTO `mikbill`.`usersgroups_users`
(`uid`, `usersgroupid`)
(
SELECT `usersall`.`uid` , 3
FROM `usersall`
LEFT JOIN `lanes_houses` ON `usersall`.`houseid` = `lanes_houses`.`houseid`
LEFT JOIN `lanes` ON `lanes_houses`.`laneid` = `lanes`.`laneid`
WHERE `lanes`.`settlementid` = 1
);

Где:

WHERE `lanes`.`settlementid` = 1 

  - id населенного пункта (1)
 
а

SELECT `usersall`.`uid` , 3 

   - id субпровайдера (3)

Share this post


Link to post
Share on other sites

Задача:
Перевести абонентов одного субпровайдера к другому субпровайдеру по признаку нас. пункта
т.е. другими словами:

Всех абонентов определенного населенного пункта, если они были у других субпровайдеров, переместить указанному субпровайдеру.

 

Ответ:

UPDATE `usersgroups_users`
SET `usersgroups_users`.`usersgroupid` = 3
WHERE `usersgroups_users`.`uid`
IN (
SELECT `usersall`.`uid` FROM `usersall`
LEFT JOIN `lanes_houses` ON `usersall`.`houseid` = `lanes_houses`.`houseid`
LEFT JOIN `lanes` ON `lanes_houses`.`laneid` = `lanes`.`laneid`
WHERE  `lanes`.`settlementid` = 2
);

Где:

WHERE `lanes`.`settlementid` = 2 

- id населенного пункта (id2)

а

SET `usersgroups_users`.`usersgroupid` = 3

- id субпровайдера (id3) которому хотим переместить нас. пункт (id2)

Share this post


Link to post
Share on other sites

Задача:
Сгенерировать пароли абонентам у которых они отсутствую.
 

Логика генерации: получаем хеш рандомного числа. обрезаем его до 10 символов, заносим абоненту пароль
 

Ответ:
Символьно-цифровой :
Пример пароля: 9911e7bf87

UPDATE `users` SET `password` = substring(MD5(RAND()) from 1 for 10) WHERE `users`.`password` = '';

 
Цифровой:

Пример пароля: 1258005957

UPDATE `users` SET `password` = substring(FLOOR(RAND()* 9999999999999)from 1 for 10) WHERE `users`.`password` = '';

Share this post


Link to post
Share on other sites

Задача:

Есть Микбилл и Аббилс. Необходимо синхронизировать балансы из Абиллса в МБ.

 

Ответ:

UPDATE mikbill.users
LEFT JOIN abills.`users` ON ( mikbill.users.user = abills.users.id )
LEFT JOIN abills.`dv_main` ON ( abills.`users`.`uid` = abills.`dv_main`.`uid` )
LEFT JOIN abills.`users_pi` ON ( abills.`users`.`uid` = abills.`users_pi`.`uid` )
LEFT JOIN abills.`bills` ON ( abills.`users`.`uid` = abills.`bills`.`uid` )
SET mikbill.users.deposit = abills.bills.deposit
WHERE abills.users.id IS NOT NULL
AND abills.bills.deposit >0

Share this post


Link to post
Share on other sites

 

Задача:

Включить интернет всем пользователя субпровайдера id 11

 

Ответ:

UPDATE mikbill.users 
LEFT JOIN `usersgroups_users` ON `users`.`uid` =  `usersgroups_users`.`uid`
SET `users`.`blocked`= 0 
WHERE  `usersgroups_users`.`usersgroupid`= 11

Share this post


Link to post
Share on other sites

Задача:

На тарифе с типом "Плавающая дата" (`packets`.`fixed` = 11) была снята два раза за сутки абонплата: в 2017-10-15 00:00:32 и 2017-10-15 23:59:56. Необходимо вернуть средства абонентам через "перерасчет" с комментарием "Возврат средств"

 

 

Ответ:

 

INSERT INTO `bugh_plategi_stat` (`uid`, `date`, `who`, `bughtypeid`, `before_billing`, `summa`, `comment`)
  SELECT `users`.`uid`, NOW() AS 'date', NULL AS 'who',  10 AS 'bughtypeid', `users`.`deposit` AS  'before_billing',  
`bugh_plategi_stat`.`summa`, 'Возврат средств' AS 'comment'
  FROM `bugh_plategi_stat`
  LEFT JOIN  `users` ON  `bugh_plategi_stat`.`uid` =  `users`.`uid` 
LEFT JOIN  `packets` ON  `users`.`gid` =  `packets`.`gid` 
WHERE  `packets`.`fixed` = 11
AND  `bugh_plategi_stat`.`date` >  '2017-10-15 00:00:00'
AND  `bugh_plategi_stat`.`date` <  '2017-10-15 01:00:00';
UPDATE `users` 
LEFT JOIN  `bugh_plategi_stat` ON `bugh_plategi_stat`.`uid` =  `users`.`uid` 
LEFT JOIN  `packets` ON  `users`.`gid` =  `packets`.`gid` 
SET `users`.`deposit` = `users`.`deposit` + `bugh_plategi_stat`.`summa`
WHERE  `packets`.`fixed` = 11
AND  `bugh_plategi_stat`.`date` >  '2017-10-15 00:00:00'
AND  `bugh_plategi_stat`.`date` <  '2017-10-15 01:00:00';

Share this post


Link to post
Share on other sites

Задача: 
 
Всех абонентов у кого не выставлен субпровайдер перенести к субпровайдеру с ID 2
 
 
Ответ:

INSERT INTO `mikbill`.`usersgroups_users` (`uid`, `usersgroupid`) 
(SELECT `usersall`.`uid` , 2 
FROM `usersall`
 WHERE `usersall`.`uid` NOT IN (
SELECT `usersgroups_users`.`uid` FROM `usersgroups_users` ) 
);

Где:

... (SELECT `usersall`.`uid` , 2 ...

- id субпровайдера (id2)

Share this post


Link to post
Share on other sites

Задача:

Сменить всем процедурам и функциям владельца на mikbill@localhost

 

Ответ:

UPDATE `mysql`.`proc` p SET p.definer = 'mikbill@localhost' WHERE p.db = 'mikbill';

Share this post


Link to post
Share on other sites

Задача:

Пользователям у которым пустое поле sms телефон заполнить из поля моб. телефон

 

Ответ:

 

UPDATE `users`
SET `users`.`sms_tel` = `users`.`mob_tel`
WHERE `users`.`sms_tel` = ''  OR `users`.`sms_tel` IS NULL;

Share this post


Link to post
Share on other sites

Задача:

Привести номера абонентов в международный формат 38050... т.е. если 050 то добавить спереди 38

 

Ответ:

 

UPDATE `users`
SET `users`.`sms_tel` = CONCAT('38',`users`.`sms_tel`)
WHERE LENGTH( `users`.`sms_tel` ) =10
AND `users`.`sms_tel` LIKE '0%' 

Share this post


Link to post
Share on other sites

Задача: 

 

Всех абонентов у кого выставлен субпровайдер перенести к основному провайдеру

 

 

Ответ:

DELETE FROM `usersgroups_users` 
WHERE `usersgroups_users`.`usersgroupid`  = 2

Где:

... `usersgroups_users`.`usersgroupid`  = 2
- id субпровайдера (id2)

Share this post


Link to post
Share on other sites

Задача: 

 

Найти дубликаты мак-адресов

 

Ответ:

SELECT  `users`.`uid` ,  `users`.`user` ,  `users`.`fio` ,  `users`.`local_mac` 
FROM  `users` 
WHERE  `users`.`local_mac` <>  '' 
AND `users`.`local_mac` IS NOT NULL 
AND `users`.`local_mac` <> 'NULL'
AND  `users`.`local_mac` 
IN (
SELECT  `users`.`local_mac` 
FROM  `users` 
GROUP BY  `users`.`local_mac` 
HAVING COUNT(  `users`.`local_mac` ) >1
)

Share this post


Link to post
Share on other sites

Задача: 

 

Дать полные права для роли Администратора. 

 

Ответ:

UPDATE `stuff_aclrole` SET `type` = '1' WHERE `aclid` =1
где:

 

`aclid` =1 
  - ID редактируемой роли

Share this post


Link to post
Share on other sites

Задача: 
 
Обновить телефоны в таблицах абонентов из бекапных таблиц. 
 
Ответ:

UPDATE users 
  LEFT JOIN users_copy ON (users.uid = users_copy.uid)
  SET users.phone = users_copy.phone , users.mob_tel = users_copy.mob_tel, users.sms_tel = users_copy.sms_tel
WHERE users_copy.uid IS NOT NULL;

UPDATE usersblok 
  LEFT JOIN usersblok_copy ON (usersblok.uid = usersblok_copy.uid)
  SET usersblok.phone = usersblok_copy.phone , usersblok.mob_tel = usersblok_copy.mob_tel, usersblok.sms_tel = usersblok_copy.sms_tel
WHERE usersblok_copy.uid IS NOT NULL;

UPDATE usersdel
  LEFT JOIN usersdel_copy ON (usersdel.uid = usersdel_copy.uid)
  SET usersdel.phone = usersdel_copy.phone , usersdel.mob_tel = usersdel_copy.mob_tel, usersdel.sms_tel = usersdel_copy.sms_tel
WHERE usersdel_copy.uid IS NOT NULL;


UPDATE usersfreeze
  LEFT JOIN usersfreeze_copy ON (usersfreeze.uid = usersfreeze_copy.uid)
  SET usersfreeze.phone = usersfreeze_copy.phone , usersfreeze.mob_tel = usersfreeze_copy.mob_tel, usersfreeze.sms_tel = usersfreeze_copy.sms_tel
WHERE usersfreeze_copy.uid IS NOT NULL; 

где:
 usersfreeze_copy, usersdel_copy, users_copy, usersblok_copy - таблицы из которых восстанавливаем 

 

Share this post


Link to post
Share on other sites

Задача:

Найти дубликаты уидов в таблицах

Ответ:

SELECT `usersall`.`uid`, `usersall`.`user`, `usersall`.`status`
FROM `usersall`
INNER JOIN(
    SELECT `uid`, `user`, `status`
    FROM `usersall`
    GROUP BY uid
    HAVING COUNT(*) > 1
) AS tmp ON usersall.uid = tmp.uid

 

Share this post


Link to post
Share on other sites

Задача:

Сгенерировать логин с лидирующими нулями + уид

UID =7 тогда  логин  0007

Ответ:

UPDATE `users` SET `user` = LPAD(`uid`, 4, '0');

 


 

 

Share this post


Link to post
Share on other sites

Задача:
Перевести абонентов из одного субпровайдера к другому
 
 Ответ:

UPDATE `usersgroups_users`
SET `usersgroups_users`.`usersgroupid` = 2
WHERE `usersgroups_users`.`usersgroupid` = 1

 

где:

`usersgroups_users`.`usersgroupid` = 1  

ID 1  - на который переводим

 

`usersgroups_users`.`usersgroupid` = 2;

ID 2  - c которого переводим

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...