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