Блог

Главная - Вопросы по программированию - MYSQL: добавьте некоторые данные в первую строку только на основе группы по идентификатору

MYSQL: добавьте некоторые данные в первую строку только на основе группы по идентификатору

#mysql #codeigniter #datatables #correlated-subquery

#mysql #codeigniter #таблицы данных #коррелированный-подзапрос

Вопрос:

У меня есть этот набор образцов данных:

Таблица invoice :

 --------------------------------------------------
| ID |     date     | invoice_number |   total   |
--------------------------------------------------
| 81 |  2017-03-24  |   0000000173   |  190.00   |
--------------------------------------------------

Таблица invoice_addon :

 ----------------------------------------------------------
| ID |  invoice_id  |       description       |  amount  |
----------------------------------------------------------
| 46 |      81      | Price Adjust. - Jumbo   |  -12.00  |
| 47 |      81      | Price Adjust. - Regular |  -12.00  |
----------------------------------------------------------

Таблица orders :

 ----------------------------------------------------------------------------------
| ID  |  invoice_id  |  box_name   |   size   |  price  |   tax   |  box_number  |
----------------------------------------------------------------------------------
| 177 |      81      | Jumbo Box   | 23x25x17 |  97.00  |  15.00  |  FCI107056   |
| 178 |      81      | Regular Box | 20x23x17 |  87.00  |  15.00  |  FCI107057   |
----------------------------------------------------------------------------------

Чего я хочу добиться:

 ----------------------------------------------------------------------------------------------------------------------------------
| trans_date  | inv_number |  box_name   |   size   |  gross   |  box_number  | others | description                     |  net  |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24  | 0000000173 | Jumbo Box   | 23x25x17 |  112.00  |  FCI107056   | -24.00 |  Price Adjust. - Jumbo -12.00   | 88.00 |
|             |            |             |          |          |              |        |  Price Adjust. - Regular -12.00 |       |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24  | 0000000173 | Regular Box | 20x23x17 |  102.00  |  FCI107057   |      0 |  NULL                           | 102.00|
----------------------------------------------------------------------------------------------------------------------------------

Мой текущий запрос:

 SELECT DATE(i.date) AS trans_date, i.invoice_number AS inv_number, o.box_name as box_name, 
o.size AS size, (SELECT Price   Tax FROM orders WHERE ID = o.ID) AS gross, 
o.box_number AS box_number,
SUM(a.amount) AS others, 
(SELECT GROUP_CONCAT(CONCAT(description, ' ', amount) SEPARATOR '<br />') FROM invoice_addon WHERE invoice_id = i.ID) AS description, 
(SUM(o.price   o.tax)   SUM(a.amount)) AS net 
FROM `invoice` i 
INNER JOIN orders o ON i.ID = o.invoice_id 
LEFT JOIN invoice_addon a ON i.ID = a.invoice_id 
WHERE i.ID = 81
GROUP BY o.ID

Проблема в результате в моем запросе заключается в том, others что description столбцы удваиваются. Это должно отображаться только в 1-й строке. Независимо от того, сколько ящиков связано в счете, они должны быть добавлены только в 1-й строке. Сеть также зависит от этих столбцов.

Что я получаю:

 ----------------------------------------------------------------------------------------------------------------------------------
| trans_date  | inv_number |  box_name   |   size   |  gross   |  box_number  | others | description                     |  net   |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24  | 0000000173 | Jumbo Box   | 23x25x17 |  112.00  |  FCI107056   | -24.00 |  Price Adjust. - Jumbo -12.00   | 200.00 |
|             |            |             |          |          |              |        |  Price Adjust. - Regular -12.00 |        |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24  | 0000000173 | Regular Box | 20x23x17 |  102.00  |  FCI107057   | -24.00 |  Price Adjust. - Jumbo -12.00   | 180.00 |
|             |            |             |          |          |              |        |  Price Adjust. - Regular -12.00 |        |
----------------------------------------------------------------------------------------------------------------------------------

Возможно ли это? Как я могу это сделать?
(Я делаю это в MySQL, используя CodeIgniter и DataTable)

Комментарии:

1. основная проблема заключается в том, что нет ссылки для invoice_addon , чтобы процедура знала, к какому ящику она относится. вам следует пересмотреть свой подход

2. @nbk это не имеет значения, если они отображаются только в первых данных. Всегда. Является invoice_id ли это общим для обеих orders таблиц и invoice_addon не может ли это произойти?

3. Поскольку у вас нет отношения, определенного для invoice_addon.id таким образом, ни один алгоритм не знает, куда он должен добавить налог на счет-фактуру, поэтому создайте ar-отношения, и все пойдет легко

Ответ №1:

Ну, вам нужны некоторые var , которые могут хранить текущий номер строки и проверять, равен ли он 1 . Что-то вроде этого:

 SET @row_number = 0;
SELECT DATE(i.date) AS trans_date, i.invoice_number AS inv_number, o.box_name as box_name, 
o.size AS size, (SELECT Price   Tax FROM orders WHERE ID = o.ID) AS gross, 
o.box_number AS box_number,
if((@row_number:=@row_number   1)=1,SUM(a.amount), null) AS others, 
if(@row_number=1,(SELECT GROUP_CONCAT(CONCAT(description, ' ', amount) 
SEPARATOR '<br />') FROM invoice_addon WHERE invoice_id = i.ID), null) AS description, 
(SUM(o.price   o.tax)   SUM(a.amount)) AS net 
FROM `invoice` i 
INNER JOIN orders o ON i.ID = o.invoice_id 
LEFT JOIN invoice_addon a ON i.ID = a.invoice_id  
WHERE i.ID = 81
GROUP BY o.ID

Комментарии:

1. Евгений спасибо за ваш ответ. Он работает для определенных данных ( i.ID = 81 ), но я бы хотел использовать его без where предложения, поэтому остальные и описание должны заполняться при каждом invoice_id появлении.

2. @elimariaaa это не было очевидно из вашего вопроса

3. @elimariaaa сохраните последний invoice_id в некоторой переменной и проверьте, равно ли оно новому

4. Евгений да, извините. Спасибо!