Jump to content
  • entries
  • comments
  • views

Полезные MySQL запросы

Sign in to follow this  


#1 Добавление случайным 1000 товарам акционной цены (случайная скидка от 2 до 25%) со случайным временным интервалом (от 5 до 30 дней)

  @discount_from = 2, 
  @discount_to = 25, 
  @days_from = 5, 
  @days_to = 30;

INSERT INTO product_special (product_id, customer_group_id, price, date_start, date_end)
SELECT product_id, '1', (price - price / 100 * ROUND(RAND() * (@discount_to - @discount_from) + @discount_from)), NOW(), DATE_ADD(NOW(), INTERVAL ROUND(RAND() * (@days_to - @days_from) + @days_from) DAY) FROM product ORDER BY RAND() LIMIT 1000;


  • +1 6
Sign in to follow this  


Recommended Comments

SELECT COUNT(p2c.product_id), c.category_id, cd.name
FROM oc_category c
LEFT JOIN oc_product_to_category p2c ON c.category_id=p2c.category_id
LEFT JOIN oc_category_description cd ON
c.category_id= cd.category_id and cd.language_id =1
GROUP by c.category_id
HAVING COUNT(p2c.product_id) <1

Получить категории без товаров

Share this comment

Link to comment
11 часов назад, chukcha сказал:

Получить категории без товаров

Второй вариант

SELECT c.category_id, cd.name
FROM category c
LEFT JOIN product_to_category p2c ON c.category_id=p2c.category_id
LEFT JOIN category_description cd ON c.category_id = cd.category_id
WHERE p2c.category_id IS NULL AND cd.language_id = 1


Share this comment

Link to comment

#3. Выводим дубли seo url псевдонимов

SELECT keyword, GROUP_CONCAT(`query` SEPARATOR ',') AS queries FROM url_alias [seo_url > OpenCart 3] GROUP BY keyword HAVING COUNT(*) > 1

Формат вывода:


keyword									queries

lenovo-ideapad-g575g-59-313661			product_id=30153,product_id=36677
lenovo-ideapad-g580a-59-331305			product_id=34948,product_id=36681
lenovo-ideapad-g580a-59-334648			product_id=34955,product_id=36913
lenovo-ideapad-s110-white-59366619		product_id=48671,product_id=48670
lenovo-ideapad-z580a-59-333632			product_id=36685,product_id=34951
lenovo-ideapad-z580a-59-334147			product_id=34954,product_id=36689
lg-a09aw1					product_id=17823,product_id=33922
lg-ms-1949g					product_id=35740,product_id=21386
logicpower					manufacturer_id=245,manufacturer_id=1330
lux						manufacturer_id=1,manufacturer_id=249


  • +1 1

Share this comment

Link to comment
You are posting as a guest. If you have an account, please sign in.
Add a comment...

×   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.

  • Recently Browsing   0 members

    No registered users viewing this page.


Important Information

On our site, cookies are used and personal data is processed to improve the user interface. To find out what and what personal data we are processing, please go to the link. If you click "I agree," it means that you understand and accept all the conditions specified in this Privacy Notice.