SQL

  1. Примеры
  2. `author` char(20) NOT NULL,
  3. `birthday` char(20) NOT NULL
  4. ('Иванов', '01.01.1950'),
  5. ('Петров', '31.12.1960'),
  6. ('Сидоров', '05.05.1965'),
  7. ('Синицын', '01.01.1970');
  8. `author` char(20) NOT NULL,
  9. `book` char(20) NOT NULL
  10. ('Воробьев', 'Книга 4'),
  11. ('Воробьев', 'Книга 5'),
  12. ('Воробьев', 'Книга 6'),
  13. ('Иванов', 'Книга 1'),
  14. ('Сидоров', 'Книга 2'),
  15. ('Сидоров', 'Книга 3');

Примеры

Каждый раз надоело искать эти примеры.. Благодаря им можно быстрее выполнить SQL задачу. Тут нет объяснений, только кодец.
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 .. х. Добавить пользователя, через ssh с правами на просмотр код. 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; .. Список прав CREATE — Позволяет пользователям создавать базы данных/таблицы SELECT — Разрешает делать выборку данных INSERT — Право добавлять новые записи в таблицы UPDATE — Позволяет изменять существующие записи в таблицах DELETE — Даёт право удалять записи из таблиц DROP — Возможность удалять записи в базе данных/таблицах аб. https://www.hostinger.ru/rukovodstva/kak-sozdat-polzovatelya-mysql-i-nastroit-prava-dostupa/ Подробная инструкция х. Сменить менеджера код. 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; .. х. Задачка Имеется список авторов (таблица A) и список книг (таблица B). Таблица A: Author Birthday Иванов 01.01.1950 Петров 31.12.1960 Сидоров 05.05.1965 Синицын 01.01.1970 Таблица B: Author Book Воробьев Книга 4 Воробьев Книга 5 Воробьев Книга 6 Иванов Книга 1 Сидоров Книга 2 Сидоров Книга 3 код. CREATE TABLE `authors` (

`author` char(20) NOT NULL,

`birthday` char(20) NOT NULL

); INSERT INTO authors VALUES

('Иванов', '01.01.1950'),

('Петров', '31.12.1960'),

('Сидоров', '05.05.1965'),

('Синицын', '01.01.1970');

CREATE TABLE `books` (

`author` char(20) NOT NULL,

`book` char(20) NOT NULL

); INSERT INTO books VALUES

('Воробьев', 'Книга 4'),

('Воробьев', 'Книга 5'),

('Воробьев', 'Книга 6'),

('Иванов', 'Книга 1'),

('Сидоров', 'Книга 2'),

('Сидоров', 'Книга 3');

.. Написать SQL-запрос для получения результирующего списка с полями Author, Birthday, Book. В списке должны присутствовать все авторы, которые встречаются и в таблице A и в таблице B, а также все имеющиеся сведения о них из таблиц A и B. Результат запроса: Author Birthday Book Воробьев Книга 4 Воробьев Книга 5 Воробьев Книга 6 Иванов 01.01.1950 Книга 1 Петров 31.12.1960 Сидоров 05.05.1965 Книга 2 Сидоров 05.05.1965 Книга 3 Синицын 01.01.1970 Решение код. SELECT books.author, birthday, book FROM books LEFT JOIN authors ON authors.author = books.author UNION SELECT authors.author, birthday, book FROM authors LEFT JOIN books ON authors.author = books.author ORDER BY author
SELECT line.author, birthday, book, Count(*) as quantity FROM ( SELECT books.author, birthday, book FROM books LEFT JOIN authors ON authors.author = books.author UNION SELECT authors.author, birthday, book FROM authors LEFT JOIN books ON authors.author = books.author ORDER BY author) as line GROUP BY line.author HAVING Count(*) > 1 SELECT line.author, birthday, book, Count(*) as quantity FROM ( SELECT books.author, birthday, book FROM books LEFT JOIN authors ON authors.author = books.author UNION SELECT authors.author, birthday, book FROM authors LEFT JOIN books ON authors.author = books.author ORDER BY author) as line GROUP BY line.name HAVING Count(*) > 1 with line as ( SELECT books.author, birthday, book FROM books LEFT JOIN authors ON authors.author = books.author UNION SELECT authors.author, birthday, book FROM authors LEFT JOIN books ON authors.author = books.author ORDER BY author ), line2 as ( SELECT line.author FROM line GROUP BY line.author HAVING Count(*) > 1 ) SELECT line.author, line.birthday, line.book FROM line right join line2 ON line2.author = line.author