Билет:
id_ticket |id_ticket_category |
----------|-------------------|
1 |8 |
2 |8 |
3 |13 |
Категория:
id_category |id_parent |label |id_category_type |
------------|----------|----------------------------------|-----------------|
1 |-1 |Demande d'information |1 |
2 |-1 |Réclamation |1 |
3 |1 |Autre |2 |
4 |1 |Mairie |2 |
5 |1 |Stationnement |2 |
6 |2 |Autre |2 |
8 |3 |Autre |3 |
9 |4 |Réservation de salle |3 |
10 |4 |Autre |3 |
11 |5 |Horaires de stationnement |3 |
12 |5 |Autre |3 |
13 |6 |Autre |3 |
16 |7 |Autre |3 |
Обратите внимание, что столбец id_ticket_category
в таблице Ticket
относится к id_category
в таблице Category
.
Я хочу вернуть count и concat
row, у которых есть id_ticket_category (id_category)
с элементом id_parentof.
Пример:
Для id_ticket_category = 8
я смотрю таблицу Category
id_category |id_parent |label |id_category_type |
------------|----------|----------------------------------|-----------------|
8 |3 |Autre |3 |
Я смотрю, что id_parent
не равен -1
id_category |id_parent |label |id_category_type |
------------|----------|----------------------------------|-----------------|
3 |1 |Autre |2 |
Я смотрю, что id_parent не равен -1
id_category |id_parent |label |id_category_type |
------------|----------|----------------------------------|-----------------|
1 |-1 |Demande d'information |1 |
id_parent
равно -1
я напечатать мой результат
Category |count|
-------------------------|-----|
Autre-Demande Information| 2 |
На самом деле у меня есть этот запрос:
SELECT CONCAT(c.label, '-', parents.label), c.id_parent
FROM Category c INNER JOIN
(SELECT id_category AS id, label FROM category c WHERE id_parent = -1) AS parents
ON parents.id = c.id_parent
WHERE c.id_category_type < 3
ORDER BY c.id_category_type;
так кто-нибудь знает, как улучшить это?
Похоже, вам нужно группировать билеты по категориям и отображать счет и путь. Это работает, если дерево категории имеет максимум 3 уровня:
SELECT
id_ticket_category,
CONCAT_WS(' > ', c3.label, c2.label, c1.label) AS 'category',
COUNT(*) AS 'count'
FROM ticket
LEFT JOIN category c1 ON ticket.id_ticket_category = c1.id_category
LEFT JOIN category c2 ON c1.id_parent = c2.id_category
LEFT JOIN category c3 ON c2.id_parent = c3.id_category
GROUP BY id_ticket_category, category
Выход:
+--------------------+---------------------------------------+-------+
| id_ticket_category | category | count |
+--------------------+---------------------------------------+-------+
| 8 | Demande d'information > Autre > Autre | 2 |
| 13 | Réclamation > Autre > Autre | 1 |
+--------------------+---------------------------------------+-------+
Преобразование в модель вложенного набора
Модель вложенного набора более эффективна при извлечении иерархических данных. Однако поддерживать это может быть сложно. Если вам интересно, сначала вам нужно преобразовать свои данные в это:
+-------------+-----------+---------------------------+------------------+------+------+
| id_category | id_parent | label | id_category_type | l | r |
+-------------+-----------+---------------------------+------------------+------+------+
| 1 | -1 | Demande d'information | 1 | 2 | 19 |
| 2 | -1 | Réclamation | 1 | 20 | 25 |
| 3 | 1 | Autre | 2 | 3 | 6 |
| 4 | 1 | Mairie | 2 | 7 | 12 |
| 5 | 1 | Stationnement | 2 | 13 | 18 |
| 6 | 2 | Autre | 2 | 21 | 24 |
| 8 | 3 | Autre | 3 | 4 | 5 |
| 9 | 4 | Réservation de salle | 3 | 8 | 9 |
| 10 | 4 | Autre | 3 | 10 | 11 |
| 11 | 5 | Horaires de stationnement | 3 | 14 | 15 |
| 12 | 5 | Autre | 3 | 16 | 17 |
| 13 | 6 | Autre | 3 | 22 | 23 |
| 16 | 7 | Autre | NULL | NULL | NULL |
+-------------+-----------+---------------------------+------------------+------+------+
Затем вы можете использовать этот запрос (который не имеет ограничений по глубине дерева категорий):
SELECT
id_ticket_category,
GROUP_CONCAT(DISTINCT CONCAT(parent.label, ' (', parent.id_category, ')') ORDER BY parent.l SEPARATOR ' > ') AS category,
COUNT(DISTINCT id_ticket) AS 'count'
FROM ticket
LEFT JOIN category ON ticket.id_ticket_category = category.id_category
LEFT JOIN category parent ON category.l BETWEEN parent.l AND parent.r
GROUP BY id_ticket_category
Выход:
+--------------------+---------------------------------------------------+-------+
| id_ticket_category | category | count |
+--------------------+---------------------------------------------------+-------+
| 8 | Demande d'information (1) > Autre (3) > Autre (8) | 2 |
| 13 | Réclamation (2) > Autre (6) > Autre (13) | 1 |
+--------------------+---------------------------------------------------+-------+
Вы можете создать следующую процедуру для выполнения задачи,
Ниже приведена процедура:
DROP PROCEDURE IF EXISTS getConcatRows;
DELIMITER //
CREATE PROCEDURE getConcatRows(IN id_tc int)
BEGIN
DECLARE id_parent_tc int;
SET id_parent_tc = id_tc;
DROP temporary table IF EXISTS temp_category;
# Create temporarty table to store hirarchy
create TEMPORARY table temp_category
AS
select 0 as id_category,category.label,-1 as id_tc from category limit 1;
WHILE id_parent_tc <> -1
DO
insert into temp_category select id_category,label,id_tc from category WHERE id_category=id_parent_tc;
SELECT id_parent into id_parent_tc FROM category WHERE id_category=id_parent_tc;
END WHILE;
# Subtract 1 since it will contain the total herarchy
select GROUP_CONCAT(distinct label),count(1)-1 from temp_category t where t.id_tc = id_tc group by id_tc;
END //
DELIMITER ;
CALL getConcatRows(8);
# Will return the required output
LEFT JOIN category c4 ON c3.id_parent = c4.id_category
и так далее.