SQL Примеры

13.07.2021
Каждый раз надоело искать эти примеры.. Закинул их сюда. Благодаря им можно быстрее выполнить 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