Блог

Мне нужна помощь с запросом сводной таблицы. Мои таблицы выглядят следующим образом:

tbl_tnts_as_apts (главная таблица персонала)

 as_apts_id(pk)    | as_apts_nature  | as_apts_name  | as_guid (fk)
1                 | teaching staff  | name_1        | guid_1
2                 | nt staff        | name_2        | guid_2
 

пользователи (таблица пользователей)

 id(pk)   | username   | password   | user_type  | as_guid (fk)
9        | user_1     | pass_1     | T          | guid_1
10       | user_2     | pass_2     | NT         | guid_2
 

tblteacheratten_sam (таблица посещаемости)

     ta_id (pk) |  ta_tchr_id (fk)  | ta_date    | ta_clock_in_tm   | ta_clock_out_tm   | ta_as_guid (fk)
    1          |  9                | 2021-08-22 | 11:26:49 AM      | 11:38:31 PM       | guid_1
    2          |  9                | 2021-08-23 | 08:21:51 AM      | 05:30:16 PM       | guid_1
    3          |  9                | 2021-08-25 | 11:30:02 AM      | 04:30:00 PM       | guid_1
    4          |  9                | 2021-08-28 | 01:59:01 PM      | 08:22:14 PM       | guid_1
    5          | 10                | 2021-08-28 | 07:59:13 PM      | 08:00:22 PM       | guid_2
    6          | 9                 | 2021-08-29 | 10:15:41 AM      | 05:23:39 PM       | guid_1 
    7          | 9                 | 2021-08-30 | 11:31:00 PM      | 09:24:25 PM       | guid_1
    8          | 10                | 2021-08-30 | 11:31:22 PM      | 09:24:00  PM      |  guid_2
 

Я хочу, чтобы моя сводная таблица выглядела так:

     as_apts_name  | as_apts_nature | 1 | 2 | 3 | 4 | 5 |...........| 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 
    name_1        | teaching staff | A | A | A | A | A |...........| P  | P  | A  | P  | A  | A  | P  | P  | P  | A
    name_2        | nt staff       | A | A | A | A | A |...........| A  | A  | A  | A  | A   | A | P  | A  | P  | A
 

Что я сделал до сих пор:

 CREATE TEMPORARY TABLE IF NOT EXISTS tmpCalendar AS (select * from 
(select adddate('1970-01-01',t4*10000   t3*1000   t2*100   t1*10   t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2021-08-01' and '2021-08-31');
    
SET SESSION group_concat_max_len = 1000000; 
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('max(CASE WHEN ca.gen_date = ''',date_format(gen_date, '%Y-%m-%d'),''' THEN ''P'' END) AS `',date_format(gen_date, '%Y-%m-%d'), '`')) INTO @sql
FROM tmpCalendar
where gen_date>='2021-08-01'
  and gen_date <= '2021-08-31';

SET @sql 
  = CONCAT('SELECT ca.as_apts_name,
              ca.as_apts_nature,ca.as_guid
              ', @sql, ' 
            from
            (
              select c.gen_date, a.as_apts_name, a.as_apts_nature, a.as_guid
              from tmpCalendar c
              cross join tbl_tnts_as_apts a
            ) ca
            left join tblteacheratten_sam p
              on ca.as_guid = p.as_guid
            where ca.gen_date>=''2021-08-01''
              and ca.gen_date <= ''2021-08-31''
            group by p.ta_tchr_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 

Приведенный выше запрос MySQL не выполняется успешно, он показывает ошибку.

Пожалуйста, укажите мне правильное направление с помощью кода.