SELECT
us.ID,
COUNT(order1.ID) AS ORDERS,
SUM(order1.PRICE) AS ORDERS_SUM,
el.NAME,
us.LOGIN,
us.ACTIVE,
us.LAST_NAME,
us.NAME,
us.SECOND_NAME,
prop.UF_CALL_COMMENT,
us.EMAIL,
us.PERSONAL_PHONE,
us.DATE_REGISTER
FROM b_user AS us
LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID
LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID
LEFT JOIN b_iblock_element AS el ON el.ID = prop.UF_MANAGER
WHERE
el.IBLOCK_ID = '11' AND
us.DATE_REGISTER >= '2021-07-01 00:00:00' AND
us.DATE_REGISTER < '2021-07-13 00:00:00'
GROUP BY us.ID
ORDER BY us.ID DESC
SELECT
us.ID,
COUNT(order1.ID) AS ORDERS,
SUM(order1.PRICE) AS ORDERS_SUM,
us.LOGIN,
us.ACTIVE,
us.LAST_NAME,
us.NAME,
us.SECOND_NAME,
prop.UF_CALL_COMMENT,
us.EMAIL,
us.PERSONAL_PHONE,
us.DATE_REGISTER
FROM b_user AS us
LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID
LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID
WHERE
us.DATE_REGISTER >= '2021-07-01 00:00:00' AND
us.DATE_REGISTER < '2021-07-13 00:00:00'
GROUP BY us.ID
ORDER BY us.ID DESC
LIMIT 100
SELECT
us.ID,
(SELECT COUNT(*) FROM b_sale_order AS order1 WHERE order1.USER_ID = us.ID) AS ORDERS,
us.LOGIN,
us.ACTIVE,
us.LAST_NAME,
us.NAME,
us.SECOND_NAME,
prop.UF_CALL_COMMENT,
us.EMAIL,
us.PERSONAL_PHONE,
us.DATE_REGISTER
FROM b_user AS us
LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID
WHERE
us.DATE_REGISTER >= '2021-07-01 00:00:00' AND
us.DATE_REGISTER < '2021-07-13 00:00:00'
GROUP BY us.ID
ORDER BY us.ID DESC
LIMIT 10
(SELECT COUNT(*) FROM b_sale_order AS order1 WHERE order1.USER_ID = us.ID) AS orders_kol
SELECT
us.ID,
order1.ID AS ID_order,
order1.PRICE,
us.LOGIN,
us.ACTIVE,
us.LAST_NAME,
us.NAME,
us.SECOND_NAME,
prop.UF_CALL_COMMENT,
us.EMAIL,
us.PERSONAL_PHONE,
us.DATE_REGISTER
FROM b_user AS us
LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID
LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID
WHERE
us.DATE_REGISTER >= '2021-07-01 00:00:00' AND
us.DATE_REGISTER < '2021-07-13 00:00:00'
GROUP BY us.ID
ORDER BY us.ID DESC
LIMIT 10
SELECT COUNT(*) TotalCount,
us.ID,
us.LOGIN,
us.ACTIVE,
us.LAST_NAME,
us.NAME,
us.SECOND_NAME,
prop.UF_CALL_COMMENT,
us.EMAIL,
us.PERSONAL_PHONE,
us.DATE_REGISTER,
order1.ID AS ID_order
FROM b_user AS us
LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID
LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID
WHERE
us.DATE_REGISTER >= '2021-07-01 00:00:00' AND
us.DATE_REGISTER < '2021-07-13 00:00:00'
GROUP BY us.ID
ORDER BY us.ID DESC
LIMIT 10
SELECT
us.ID,
us.LOGIN,
us.ACTIVE,
us.LAST_NAME,
us.NAME,
us.SECOND_NAME,
prop.UF_CALL_COMMENT,
us.EMAIL,
us.PERSONAL_PHONE,
us.DATE_REGISTER,
order1.ID AS ID_order
FROM b_user AS us
LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID
LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID
WHERE
us.DATE_REGISTER >= '2021-07-01 00:00:00' AND
us.DATE_REGISTER < '2021-07-13 00:00:00'
GROUP BY us.ID
ORDER BY us.ID DESC
LIMIT 10
SELECT
us.ID,
us.LOGIN,
us.ACTIVE,
us.LAST_NAME,
us.NAME,
us.SECOND_NAME,
prop.UF_CALL_COMMENT,
us.EMAIL,
us.PERSONAL_PHONE,
us.DATE_REGISTER
FROM b_user AS us
LEFT JOIN b_uts_user AS prop
ON
us.ID = prop.VALUE_ID
WHERE
us.DATE_REGISTER >= '2021-07-01 00:00:00' AND
us.DATE_REGISTER < '2021-07-13 00:00:00'
ORDER BY ID DESC
LIMIT 3
SELECT
t_el.ID,
t_el.NAME,
t_el.ACTIVE,
t_el.CODE,
t_pr.PRICE
FROM b_iblock_element AS t_el
LEFT JOIN b_catalog_price AS t_pr
ON
t_el.ID = t_pr.PRODUCT_ID
WHERE
t_el.IBLOCK_ID = '9' AND
t_el.ACTIVE = 'Y' AND
t_pr.PRICE IS null
LIMIT 3
CREATE TABLE `h7bot` (
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`id_u` char(255) NOT NULL,
`type_bot` char(50) NOT NULL,
`date_create` datetime NOT NULL,
`date_rq` datetime NOT NULL,
`agreement` char(1) NOT NULL,
`id_site` char(50) NOT NULL,
`input` char(50) NOT NULL
) ENGINE='MyISAM' COLLATE 'utf8_general_ci';
Задача
У товаров арманго есть свойства
На озоне == да
Иды товаров, у которых это проставлено, можно вычеслить так
SELECT `IBLOCK_ELEMENT_ID` FROM `b_iblock_element_property` WHERE `IBLOCK_PROPERTY_ID` = '709' AND `VALUE` = '158919'
Так же в свойстве есть ид 1С
Их можно просмотреть так
SELECT `VALUE` FROM `b_iblock_element_property` WHERE `IBLOCK_PROPERTY_ID` = '48' AND `DESCRIPTION` = 'Код'
Есть ещё табличка со складом, озона, просмотреть что-там есть можно так
SELECT * FROM `b_catalog_store_product` WHERE `STORE_ID` = '287' //PRODUCT_ID AMOUNT
Каким 1 запросом можно вывести список
ид товара, ид1с, количество
Вывести ид и ид1с
SELECT tb0.IBLOCK_ELEMENT_ID, tb1.VALUE FROM b_iblock_element_property AS tb0 INNER JOIN b_iblock_element_property AS tb1 ON
tb0.IBLOCK_PROPERTY_ID = '709' AND
tb0.VALUE = '158919' AND
tb0.IBLOCK_ELEMENT_ID = tb1.IBLOCK_ELEMENT_ID AND
tb1.IBLOCK_PROPERTY_ID = '48' AND
tb1.DESCRIPTION = 'Код'
LIMIT 50
SELECT
tb0.IBLOCK_ELEMENT_ID, tb1.VALUE, tb_store.AMOUNT
FROM b_iblock_element_property AS tb0
INNER JOIN b_iblock_element_property AS tb1
INNER JOIN b_catalog_store_product AS tb_store
ON
tb0.IBLOCK_PROPERTY_ID = '709' AND
tb0.VALUE = '158919' AND
tb0.IBLOCK_ELEMENT_ID = tb1.IBLOCK_ELEMENT_ID AND
tb1.IBLOCK_PROPERTY_ID = '48' AND
tb1.DESCRIPTION = 'Код' AND
tb_store.PRODUCT_ID = tb0.IBLOCK_ELEMENT_ID AND
tb_store.STORE_ID = '287'
LIMIT 50
mysql --user="root" --password="pass0"
// Создать пользователя
CREATE USER 'user1'@'%' IDENTIFIED BY 'pass1';
// Добавить право
GRANT SELECT ON * . * TO 'user1'@'%';
// Сразу несколько
GRANT SELECT, CREATE ON * . * TO 'user1'@'%';
// Удалить
REVOKE SELECT ON * . * TO 'user1'@'%';
// Готовое
mysql --user="root" --password="pass1"
CREATE USER 'user1'@'%' IDENTIFIED BY 'pass2';
CREATE DATABASE bd1;
USE bd1
GRANT CREATE, ALTER, SELECT, INSERT, UPDATE, DELETE ON bd1 . * TO user1;
UPDATE b_uts_user SET UF_MANAGER = '35521' WHERE UF_MANAGER = '34387' LIMIT 714;
SELECT * FROM `b_sale_basket` WHERE `ORDER_ID` IS NULL ORDER BY `ID` DESC LIMIT 50;
DELETE FROM `b_sale_basket` WHERE `ORDER_ID` IS NULL LIMIT 100;
SELECT
basket.ID AS ID,
basket.PRODUCT_ID AS PRODUCT_ID,
basket.NAME AS NAME,
basket.PRICE AS PRICE,
basket.QUANTITY AS QUANTITY,
basket.DATE_UPDATE AS DATE_UPDATE,
basket.DETAIL_PAGE_URL AS URL
FROM b_sale_fuser AS fus
INNER JOIN b_sale_basket AS basket
ON
fus.ID = basket.FUSER_ID
WHERE
fus.USER_ID = '13570' AND
basket.ORDER_ID IS NULL
SELECT
b.id, b.id_u, b.date_create, b.id_site, b.city_id,
cit.NAME AS city, cit.REGION,
b.phone, b.login, b.notif_action, b.memory1, b.memory2, b.memory3, b.memory4,
b.memory5, b.memory6, b.memory7
FROM h7bot AS b
INNER JOIN ipol_sdekcities AS cit
ON
b.city_id = cit.ID
SELECT
us.ID AS ID,
us.LAST_NAME,
us.NAME,
us.SECOND_NAME,
us.PERSONAL_PHONE,
us.EMAIL,
ROUND(SUM(basket.PRICE * basket.QUANTITY)) AS SUMM,
man.NAME
FROM b_user AS us
INNER JOIN b_uts_user AS uts
ON uts.VALUE_ID = us.ID
INNER JOIN b_iblock_element AS man
ON uts.UF_MANAGER = man.ID
INNER JOIN b_sale_fuser AS fus
ON fus.USER_ID = us.ID
INNER JOIN b_sale_basket AS basket
ON basket.FUSER_ID = fus.ID
WHERE
us.LAST_LOGIN >= '2022-01-01' AND
man.IBLOCK_ID = '11' AND
basket.ORDER_ID IS NULL
GROUP BY us.ID
ORDER BY SUMM DESC
LIMIT 1000;