Задать вопрос
  • Как составить SQL запрос для отчета с разным количеством строк по категории?

    если исходную таблицу можно трансформировать, то я добавил столбец item_id и получил естественный первичный ключ (department_id, type_id, item_id). Столбец item идёт в довесок и, в общем-то, не нужен.
    данные для воспроизведения:
    CREATE TABLE `items` (
      `department_id` int(10) unsigned NOT NULL,
      `type_id` int(10) unsigned NOT NULL,
      `item_id` int(10) unsigned NOT NULL,
      `item` varchar(255) NOT NULL,
      PRIMARY KEY (`department_id`,`type_id`,`item_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    INSERT INTO `items` (`department_id`, `type_id`, `item_id`, `item`) VALUES
    (1,	1,	1,	'1_1_0001'),
    (1,	1,	2,	'1_1_0002'),
    (1,	2,	1,	'1_2_0001'),
    (1,	3,	1,	'1_3_0001'),
    (1,	3,	2,	'1_3_0002'),
    (1,	3,	3,	'1_3_0003'),
    (1,	3,	4,	'1_3_0004'),
    (1,	3,	5,	'1_3_0005'),
    (2,	1,	1,	'2_1_0001'),
    (2,	2,	1,	'2_2_0001'),
    (2,	3,	1,	'2_3_0001');


    Запрос для решения задачи:
    SELECT t.department_id, 
    IFNULL( t1.item, '' ) as type_1,
    IFNULL( t2.item, '' ) as type_2,
    IFNULL( t3.item, '' ) as type_3,
    IFNULL( t4.item, '' ) as type_4
    FROM `items` t
    LEFT JOIN `items` t1 ON ( t.department_id=t1.department_id AND t.item_id=t1.item_id AND t1.type_id=1 )
    LEFT JOIN `items` t2 ON ( t.department_id=t2.department_id AND t.item_id=t2.item_id AND t2.type_id=2 )
    LEFT JOIN `items` t3 ON ( t.department_id=t3.department_id AND t.item_id=t3.item_id AND t3.type_id=3 )
    LEFT JOIN `items` t4 ON ( t.department_id=t4.department_id AND t.item_id=t4.item_id AND t4.type_id=4 )
    GROUP BY t.department_id, t.item_id

    Я добавил 4-й столбец для иллюстрации NULL . Таким образом, есть 2 подхода:
    1) перед конструированием знать уникальные значения столбца type_id. Тогда надо добавить в таблицу ещё один индекс ( просто INDEX) чисто по этому столбцу, для скорости предварительного запроса.
    2) предполагать максимальный type_id и тупо строить по диапазону 1..type_id_max
    Ответ написан
    4 комментария
  • Как составить SQL запрос для отчета с разным количеством строк по категории?

    @alexalexes
    При помощи SQL вы получаете исходные данные, в виде первой таблицы.
    Далее работаете только кодом PHP.
    Вторая таблица - это у вас несколько матриц, по количеству подразделений.
    Одна матрица n*m это:
    n - макс. индекс предмета 000n в пределах одного подразделения.
    m - макс. индекс видов предметов во всей исходной выборке.
    Позиция в матрице i,j:
    i - это 000i
    j - индекс вида предмета.

    Для вывода результата вам нужно получить структуру:
    $result =
    [
      1 =>  // department_id
      [
         1 /* 000i индекс */ => [1 /*индекс вида предмета*/ => '1_1_0001', 2 => '1_2_0001', 3 => '1_3_0001' /* непосредственно данные */],
         2 /* 000i индекс */ => [1 /*индекс вида предмета*/ => '1_1_0002', 2 => '1_2_0002', 3 => '1_3_0002' /* непосредственно данные */],
         3 /* 000i индекс */ => [1 /*индекс вида предмета*/ => '1_1_0003', 3 => '1_3_0003' /* непосредственно данные */],
         ...
      ],
      2 =>
      ...
    ];

    Вариант 2, динамически в PHP создать запрос:
    select t.department_id,
              t1.item as type_1,
              t2.item as type_2,
              ...
    from t
    left join (select t.department_id, t.item from t where t.type_id = 1) t1 on  t1.department_id = t.department_id
    left join (select t.department_id, t.item from t where t.type_id = 2) t2 on  t2.department_id = t.department_id
    ...
    where 1 = 1
        and (SUBSTRING_INDEX(t.item, '_', -1) + 0 = SUBSTRING_INDEX(t1.item, '_', -1) + 0 or t1.item is null)
        and (SUBSTRING_INDEX(t.item, '_', -1) + 0 = SUBSTRING_INDEX(t2.item, '_', -1) + 0 or t2.item is null)
       ...
      -- тут нужно получить равенство всех субиндексов 0000i от t.item сравнить со всеми tn.item
    order by  t.department_id, SUBSTRING_INDEX(t.item, '_', -1) + 0
    Ответ написан
    2 комментария