полезные SQL запросы

[freelancer]

тема представляет собой cookbook полезных запросов для OpenCart.

просьба придерживаться правил форума и писать только по делу. весь оффтоп будет удален, вопросы по скриптам задавать авторам в ЛС

запросы можно выполнять в

http://opencartforum...8-sql-executor/ (начиная с версии 2 там появился экспорт)

phpMyAdmin

формат

1. название, краткое описание(опционально)

2. сам скрипт

3. пример вывода

Змінено 7 березня 2013 користувачем freelancer

[freelancer]

экспорт заказов

SELECT o.`order_id` 'Заказ', firstname 'Имя', lastname 'Фамилия', email, telephone 'Телефон', GROUP_CONCAT(`name` SEPARATOR '; ') 'Товары', sum(o.total) 'Итог' FROM `order_product` op LEFT JOIN `order` o USING(order_id) GROUP BY order_id

Заказ;Имя;Фамилия;email;Телефон;Товары;Итог

1;seo;Виктория;[email protected];0672826701;"iPod Nano";120.0000

2;Евген;Абрамов;[email protected];567890;iPhone;129.0800

3;qwe;qweewq;[email protected];1234531;iPhone;129.0800

[fjeka]

Отличная вещь! Спасибо! Еще не хватает возможности сохранять запросы чтоб каждый раз не копипастить.

[freelancer]

выгрузка покупателей

SELECT customer_id 'id', firstname 'Имя', lastname 'Фамилия', email 'E-mail', telephone 'Телефон', fax 'Факс', newsletter 'Подписка', ip 'Ip', cg.name 'Группа'  FROM customer c LEFT JOIN сustomer_group cg USING(customer_group_id)

id;Имя;Фамилия;E-mail;Телефон;Факс;Подписка;Ip;Группа

1;ацуцауауц;weffweefw;[email protected];2234;;0;178.xxx.245;"По умолчанию"

2;111;111;[email protected];1111111111;;0;84xxx6.7;"По умолчанию"

3;Qurban;Ali;[email protected];9898989;;0;180.178.162.169;"По умолчанию"

4;awaro;awaro;[email protected];awaro;;0;95.xxx00.44;"По умолчанию"

[Nenomernoj2013]

UPDATE `product` SET points = round(price);

Установить кол-во бонусных баллов для покупки товара равное цене

[yarik]

Обновление названий товаров в Заказах, если они изменялись в Описании.

UPDATE order_product op, product_description pd SET op.name = pd.name WHERE op.product_id = pd.product_id AND pd.language_id = 1

language_id выбрать нужный, к примеру язык по умолчанию.

[rezhisser]

Найти заказы от не авторизованного покупателя (гостевые заказы)

SELECT order_id, oc_order.customer_id order_customer, oc_customer.customer_id customer_customer, oc_order.email
FROM oc_order
LEFT JOIN oc_customer ON oc_order.email = oc_customer.email
WHERE oc_order.customer_id <> oc_customer.customer_id
ORDER BY oc_order.email

Добавить гостевые заказы в историю Покупателя

UPDATE oc_order
LEFT JOIN oc_customer ON oc_order.email = oc_customer.email
SET oc_order.customer_id = oc_customer.customer_id
WHERE oc_order.customer_id <> oc_customer.customer_id

[Globomen]

Включить опцию "Отображать размерную сетку" во всех товарах:

update oc_product set popupsize='1'

 

[dreemix]

Для обновления цены и наличия кто-то может написать как выглядит запрос? Если имеет название или артикул товара

[SiteMix]

Не написано как нужно обновить цену.

 

Например, увеличить на 25% для товаров с product_id > 1111:

update oc_product set price = price * 1.25 where product_id > 1111;

 

Или установить количество 111 для товров с product_id > 1111:

update oc_product set quantity = 111 where product_id > 1111;

 

 

[dreemix]

Хотелось бы обновлять обновлять цены автоматом, например, такая возможность вроде как есть в программе датакол. Или исходя из справки через нее не получится?

[100napb]

On 10/1/2018 at 5:03 PM, dreemix said:

Хотелось бы обновлять обновлять цены автоматом, например, такая возможность вроде как есть в программе датакол. Или исходя из справки через нее не получится?

Можно и автоматом, причем только средствами mysql, даже без всяких cron \ php. Вы только определитесь, по какому событию\условию нужно вносить изменения?

 

В одном случае Вам стоит почитать про триггеры (настраивается на какое-то событие в БД; например, при изменении\добавлении строки в какой-либо таблице),

а в другом случае, если нужно вносить изменения по расписанию\по времени , то стоит почитать про события (CREATE EVENT syntax).

 

Например, у меня был такой костыль, который каждые полчаса мониторил новые отзывы и начислял авторам бонусные баллы, проверяя, при этом, что бы дважды бонусы не начислялись за повторный отзывы\ответы одному и тому же автору... или, помню было, автоматически менялись статусы заказов с проверкой всяких условий... и ни одной строчки на php )

Змінено 29 жовтня 2018 користувачем 100napb

[100napb]

по сабжу, в копилку полезных запросов:

 

Вот эти строчки красиво посчитают Вам количество и сумму заказов магазина в разрезе месяцев\годов. Можно потом в экселе диаграмму построить, если хотите.

Нужно только указать ВАШИ id-шники статусов заказов, которые запрос будет суммировать. В примере текста запроса ниже это 8, 9, 10, 11, 12 - замените их на свои. Например, это все id-шники оплаченных\отгруженных заказов, за которые вы УЖЕ получили денежку. Подсмотреть эти id-шники можно в таблице oc_order_status (префикс oc_ может отличаться)

 

Запрос

Spoiler

SELECT YEAR(`o`.`date_added`) AS `YEAR`,
       (CASE MONTH(`o`.`date_added`)
            WHEN 1 THEN 'Январь'
            WHEN 2 THEN 'Февраль'
            WHEN 3 THEN 'Март'
            WHEN 4 THEN 'Апрель'
            WHEN 5 THEN 'Май'
            WHEN 6 THEN 'Июнь'
            WHEN 7 THEN 'Июль'
            WHEN 8 THEN 'Август'
            WHEN 9 THEN 'Сентябрь'
            WHEN 10 THEN 'Октябрь'
            WHEN 11 THEN 'Ноябрь'
            WHEN 12 THEN 'Декабрь'
        END) AS `MONTH`,
       COUNT(0) AS `ORDERS_COUNT`,
       SUM(`o`.`total`) AS `TOTAL_SUM_WITH_SHIPPING`
FROM `oc_order` `o`
WHERE (`o`.`order_status_id` IN (8, 9, 10, 11, 12))
GROUP BY YEAR(`o`.`date_added`),
         MONTH(`o`.`date_added`)

Результат

Spoiler

 

[100napb]

скрипт предназначен для поиска клиентов, имеющих X и более заказ(ов) ранее чем N-месяцев назад
И НЕ
имеющих заказов после этого

этих клиентов можно и нужно стимулировать и выводить на новый заказ
например - сделать таргетированную расылку по email

Spoiler

/*############################################################################
скрипт предназначен для поиска клиентов, имеющих X и более заказ(ов) ранее чем N-месяцев назад
И НЕ
имеющих заказов после этого

этих клиентов можно и нужно стимулировать и выводить на новый заказ :)
например - сделать таргетированную расылку по email
############################################################################*/

-- указать интервал поиска заказов, кол-во месяцев
SET @interval = 3;
-- указать минимальное кол-во заказов клиента N-месяцев назад
SET @orders_count = 1;
-- указать статусы заказов, которые не будем учитывать; 0 - забытые корзинки; 9999 - просто для примера
SET @order_status_to_ignore = '0,9999';

SELECT SQL_NO_CACHE
  *
FROM (SELECT
    o.customer_id,
    o.lastname,
    o.email,
    COUNT(*) AS `orders_count_BEFORE_INTERVAL`,
    SUM(o.total) AS orders_sum_BEFORE_INTERVAL,
    MIN(o.date_added) first_order_date,
    MAX(o.date_added) AS last_order_date

  FROM oc_order o,
       oc_customer oc
  WHERE o.customer_id != 0
  AND oc.customer_id = o.customer_id
  AND NOT FIND_IN_SET(o.order_status_id, @order_status_to_ignore)
  AND o.date_added < (DATE_SUB(NOW(), INTERVAL @interval MONTH))

  AND NOT EXISTS (SELECT
      *
    FROM oc_order o2,
         oc_customer oc2
    WHERE o2.customer_id != 0
    AND oc2.customer_id = o2.customer_id
    AND o.customer_id = o2.customer_id
    AND NOT FIND_IN_SET(o2.order_status_id, @order_status_to_ignore)
    AND o2.date_added >= (DATE_SUB(NOW(), INTERVAL @interval MONTH))
    GROUP BY o2.customer_id)
  GROUP BY o.customer_id
  HAVING COUNT(*) >= @orders_count

  ORDER BY 5 DESC) t

 

 

Змінено 1 квітня 2019 користувачем 100napb

[Stariy71]

Доброго времени суток коллеги. Вопрос, где и в какой таблице/поле смотреть акционные цены по всем товарам. 
ocStore 2.3.0.2.3
скуль 5.5.62
Надо написать запрос на удаление всех акций с ценой = 0 из карточек товара, а я никак сам товар найти не могу, oc_product в БД нет. Уже и дамп шерстил и в phpmyadmin искал. Нету. Описание/схему структуры sql OcStore 2.3.0.2.3 не нарыл, грохнул почти день, как только не формулировал вопрос.
Выручайте братцы.

[mpn2005]

41 минуту назад, Stariy71 сказал:

Доброго времени суток коллеги. Вопрос, где и в какой таблице/поле смотреть акционные цены по всем товарам. 

Добрый день.

Таблица: oc_product_special

Запрос: DELETE FROM `oc_product_special` WHERE price = 0

[Stariy71]

 

3 часа назад, mpn2005 сказал:

Таблица: oc_product_special

Спасибо огромное, но если честно я так и не понял почему нет в списке таблиц скуля. Прикладываю скрин, дальше по алфавиту должно идти сочетание oc_p********
Но его нет. В чем прикол, я так и не понял.

Змінено 12 липня 2019 користувачем Stariy71
опечатка

[100napb]

4 hours ago, Stariy71 said:

 

Спасибо огромное, но если честно я так и не понял почему нет в списке таблиц скуля. Прикладываю скрин, дальше по алфавиту должно идти сочетание oc_p********
Но его нет. В чем прикол, я так и не понял.

в самом верху этого списка таблиц в интерфейса phpmyadmin есть пагинация страниц

нужная Вам таблица просто на 2й \ следующей странице.

[roman409]

Добрый день. 

 

Помогите сформировать простой запрос - нужно: 

 

скопировать

значения из колонки description таблицы oc_product_description
в значения колонки jan таблицы oc_product
с учетом id для каждого товара соответсвенно.

 

что-то в этом духе:

 

UPDATE oc_product

SET jan = description

FROM oc_product_description
WHERE product_id = ''

[Alexale]

Всем доброго времени суток.

Подскажите пожалуйста, в какой таблице искать баланс бонусных баллов у покупателей? Дело в том, что сменил версию, установил новую БД, сменил  шаблон. Покупателей импортировал со старой БД, они могут зайти теперь без проблем, но в личном кабинете у них вся информация обнулилась. С моей стороны, как продавца это не красиво получается. Хочу найти все баллы покупателей в старой БД и позачислять им на счет.

 

[100napb]

12 minutes ago, Alexale said:

Всем доброго времени суток.

Подскажите пожалуйста, в какой таблице искать баланс бонусных баллов у покупателей? Дело в том, что сменил версию, установил новую БД, сменил  шаблон. Покупателей импортировал со старой БД, они могут зайти теперь без проблем, но в личном кабинете у них вся информация обнулилась. С моей стороны, как продавца это не красиво получается. Хочу найти все баллы покупателей в старой БД и позачислять им на счет.

oc_customer_reward

[Alexale]

Я видел данную таблицу. Там только операции нашел, зачисления и расход, а вот именно баланс определенного покупателя не могу найти. 

[100napb]

5 hours ago, Alexale said:

а вот именно баланс определенного покупателя не могу найти.

а его нет в готовом виде. надо считать исходя из истории\операций\значений строк в данной таблице для каждого клиента.

 

не проверял запрос, т.к. нет бонусных баллов под рукой, но посчитать можно как-то так, например:

Spoiler

SELECT
  cr.customer_id,
  c.firstname,
  c.lastname,
  c.email,
  c.telephone,
  SUM(points) AS `total_points`
FROM oc_customer_reward cr
  JOIN oc_customer c
    ON cr.customer_id = c.customer_id
GROUP BY cr.customer_id

в принципе, все достаточно просто. со старой БД развернуть в новую БД или рядом с ней две временные таблицы, содержащие oc_customer_reward и oc_customer. Скриптом выше посчитать результаты и присвоить их уже клиентам в новую БД.

[Alexale]

Спасибо, советы помогли, все получилось 

[kredit24]

Подскажите, можно ли запросом изменить кол-во товара в существующих опциях, например с нуля до 100?