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

[freelancer]

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

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

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

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

phpMyAdmin

формат

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

2. сам скрипт

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

Edited March 7, 2013 by 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 )

Edited October 29, 2018 by 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

 

 

Edited April 1, 2019 by 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********
Но его нет. В чем прикол, я ик и не понял.

Edited July 12, 2019 by 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?