Перейти до вмісту
Пошук в
  • Детальніше...
Шукати результати, які ...
Шукати результати в ...

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


freelancer

Recommended Posts

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

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

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

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

phpMyAdmin

формат

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

2. сам скрипт

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

Змінено користувачем freelancer
  • +1 1
Надіслати
Поділитися на інших сайтах

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

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

Надіслати
Поділитися на інших сайтах

  • 3 weeks later...

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

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;"По умолчанию"

Надіслати
Поділитися на інших сайтах

  • 10 months later...
  • 1 year later...

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

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 выбрать нужный, к примеру язык по умолчанию.

  • +1 1
Надіслати
Поділитися на інших сайтах

  • 1 year later...

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

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
  • +1 1
Надіслати
Поділитися на інших сайтах


  • 4 months later...
  • 1 year later...

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

 

Например, увеличить на 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;

 

 

Надіслати
Поділитися на інших сайтах


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

Надіслати
Поділитися на інших сайтах


  • 4 weeks later...
On 10/1/2018 at 5:03 PM, dreemix said:

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

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

 

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

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

 

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

Змінено користувачем 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

1448456052_.JPG.256fa1d03dec0ae7c0d349435db0b48a.JPG

 

Надіслати
Поділитися на інших сайтах

  • 5 months later...

скрипт предназначен для поиска клиентов, имеющих 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

 

 

Змінено користувачем 100napb
Надіслати
Поділитися на інших сайтах

  • 3 months later...

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

Надіслати
Поділитися на інших сайтах


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

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

Добрый день.

Таблица: oc_product_special

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

Надіслати
Поділитися на інших сайтах

 

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

Таблица: oc_product_special

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

sql.jpg

Змінено користувачем Stariy71
опечатка
Надіслати
Поділитися на інших сайтах


4 hours ago, Stariy71 said:

 

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

sql.jpg

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

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

Надіслати
Поділитися на інших сайтах

  • 3 months later...

Добрый день. 

 

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

 

скопировать

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

 

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

 

UPDATE oc_product

SET jan = description

FROM oc_product_description
WHERE product_id = ''
Надіслати
Поділитися на інших сайтах


  • 3 weeks later...

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

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

 

Надіслати
Поділитися на інших сайтах


12 minutes ago, Alexale said:

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

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

oc_customer_reward

Надіслати
Поділитися на інших сайтах

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. Скриптом выше посчитать результаты и присвоить их уже клиентам в новую БД.

Надіслати
Поділитися на інших сайтах

  • 3 weeks later...

Створіть аккаунт або увійдіть для коментування

Ви повинні бути користувачем, щоб залишити коментар

Створити обліковий запис

Зареєструйтеся для отримання облікового запису. Це просто!

Зареєструвати аккаунт

Вхід

Уже зареєстровані? Увійдіть тут.

Вхід зараз
  • Зараз на сторінці   0 користувачів

    • Ні користувачів, які переглядиють цю сторінку
×
×
  • Створити...

Important Information

На нашому сайті використовуються файли cookie і відбувається обробка деяких персональних даних користувачів, щоб поліпшити користувальницький інтерфейс. Щоб дізнатися для чого і які персональні дані ми обробляємо перейдіть за посиланням . Якщо Ви натиснете «Я даю згоду», це означає, що Ви розумієте і приймаєте всі умови, зазначені в цьому Повідомленні про конфіденційність.