Объединить таблицы в Mysql рекурсивным способом

Question

Объединить таблицы в Mysql рекурсивным способом

У меня есть Mysql БД с заказами. Каждый заказ имеет один или несколько счетов поставщиков (tbl vendorinvoices), а также один или несколько счетов клиентов (tbl cinvoices).

Я хотел бы перечислить все заказы, а также все счета поставщиков и клиентов. Это должно быть сделано каким-то рекурсивным способом, но я не могу понять, как это сделать точно.

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

Есть ли способ лучше?

Стол заказов

CREATE TABLE `inquery` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`clientid` int(11) DEFAULT NULL,
`description` text,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=424 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

Таблица накладных поставщика

CREATE TABLE `vendorinvoices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`invoicenumber` varchar(255) DEFAULT NULL,
`inquery` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=146 DEFAULT CHARSET=latin1

Таблица счетов клиентов

CREATE TABLE `invoice` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer` varchar(255) DEFAULT NULL,
`inqueryid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=294 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

Желаемый результат

|---------------|---------------------|-------------------|
| Inquery id    | Client invoice  id  | Vendor invoice id |
|---------------|---------------------|-------------------|
| 1             | 1                   | 1                 |
|               | 2                   | 2                 |
|---------------|---------------------|-------------------|
| 2             | 3                   |                   |
|---------------|---------------------|-------------------|
| 3             | 4                   | 3                 |
|               | 5                   |                   |
|               | 6                   |                   |
|---------------|---------------------|-------------------|

1

mysql php recursive-query

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Это боль в MySQL, потому что он не поддерживает row_number() или CTE. Тем не менее, вы можете сделать это с переменными, UNION ALLи агрегация:

select inquiryid, max(clientinvoiceid) as clientinvoiceid,
max(vendorinvoiceid) as vendorinvoiceid
from ((select inquiryId, id as clientinvoiceid, NULL as vendorinvoiceid,
(@rnc := if(@ic = inquiryId, @rnc + 1,
if(@ic := inquiryId, 1, 1)
)
) as seqnum
from cinvoices cross join
(select @ic := -1, @rnc := 0) as params
order by inquiryId
) union all
(select inquiryId, NULL as clientinvoiceid, id as vendorinvoiceid,
(@rnv := if(@iv = inquiryId, @rnv + 1,
if(@iv := inquiryId, 1, 1)
)
) as seqnum
from vendorinvoices cross join
(select @iv := -1, @rnv := 0) as params
order by inquiryId
)
) cv
group by inquiryid, seqnum;

Это перечисляет запросы для клиентов и поставщиков — отдельно. Затем он агрегирует inquiryid и перечисляемое значение.

0