4 JOIN에서 1개의 테이블을 피벗해야 합니다.

4 JOIN에서 1개의 테이블을 피벗해야 합니다.

이것은 매우 구체적인 문제입니다. 제가 거기서 무엇을 하고 싶은지 이해해주셨으면 합니다.

(MariaDB)

내 테이블:

table_to teach:

티치아이드	가르치다	선생님.	....	....
1.0	서브j1-	Tname1
1.2	서브j2-	Tname1

table_message:

학생입니다	...	...
stname1	--	--
stname2	--	--
stname3	--	--
stname4	--	--
(x-stname의 양)	--	--

table_stpresents:

이것은 출석 기록이며 각 학생과 각 요일마다 한 줄씩 있습니다.학생이 하루 시작에 참석하는 경우, 학생은 해당 날짜에 예약된 각 수업에 참석한 것으로 예상됩니다.

학생입니다	날짜.	현재의.
stname1	날짜 1	네 -
stname2	날짜 1	아니 -
x개의 stname 양	날짜 1	-네 -
stname1	날짜 2	-아니-
stname2	날짜 2	-네 -
x개의 stname 양	날짜 2	-네 -

table_message:

학생 이름은 여기서 CSV(쉼표로 구분된 문자열) 값으로 참조되며, 매일 각 시간 테이블 클래스에 대해 행이 하나씩 있습니다.

날짜.	서브j1	서브j2	서브j3	stnames
날짜 1	서브j1-	서브j3-	서브j2-	stname1, stname2, stname5
날짜 X	서브j3-	서브j2-	서브j1-	stname1, stname4, stname3, stname5

예상 결과:

한 의 열이 ▁the▁for▁that▁for▁show▁row▁1▁has▁contains다▁that▁subject▁column▁and니▁one시표합▁each각을행나▁student하의▁each해대목과에가 포함된 행을 보여줍니다.LDUS학생이 참석한 날짜와 해당 학생에 해당하는 시간표 기록을 기준으로 해당 학생에게 과목이 강의된 최신 날짜.

테이블 열 개수는 다른 제목 그룹을 선택하여 변경됩니다(이 테이블은 사용자 지정 UI의 일부가 됨).

참고: 내 코드는 동적으로 SQL 코드를 생성할 수 있으므로 동적 SQL 솔루션이 필요하지 않습니다.

티치아이드	가르치다	선생님.	stname1	stname2	.....	stnameX
1.0	서브j1-	Tname1	-LDUS-	-LDUS-	-LDUS-
1.2	서브j2-	Tname2	-LDUS-	-LDUS-	-LDUS-

갱신하다

저는 몇 가지 예제 데이터를 준비했습니다. https://dbfiddle.uk/ ?rdbms=sysadb_10.3&sysdle=4bf254 2240eaa86e4f4624c1e376215a.

첫 번째 작업은 이 모든 테이블을 올바르게 결합하고 데이터를 정규화하는 것입니다.그런 다음 원본 데이터가 올바른지 확인한 후 행을 피벗하는 방법을 물어볼 수 있습니다.

소스 데이터는 두 가지 구조적 문제로 인해 복잡합니다.

1. 의 table_timetable테이블이 여러 열에 걸쳐 나열됩니다.이 데이터를 정규화하기 위해 각 주제 열에 대해 표를 한 번씩 쿼리하고 결과를 함께 결합할 수 있습니다.
2. 의 학생들table_timetable테이블은 CSV 문자열 값으로 저장됩니다. MariaDB에는 이러한 유형의 데이터를 쿼리하는 내장 함수가 없지만 CTE를 사용하여 이름을 개별 행으로 분할할 수 있습니다. 이 내용은 이 SO 질문에서 설명합니다. SQL(Maria DB) 분할 문자열을 쉼표로 구분하여 행으로 구분합니다.

집에 있는 모든 사람들에게, 이 두 가지 합병증 없이 비슷한 요구사항이 있다면, 이 단순화된 피들은 도움이 될 것입니다: https://dbfiddle.uk/ ?rdbms=sysadb_10.3&nbdle=9dfce7084321699d1262bbb50ca724d9e.

다음 쿼리는 레코드 집합을 정규화합니다.

with recursive cte as (
      select `Date`,subj1,subj2,subj3,'            ' as stname, concat(stnames, ',') as stnames, 1 as lev
      from table_timetable
      union all
      select `Date`,subj1,subj2,subj3,substring_index(stnames, ',', 1),
             substr(stnames, instr(stnames, ',') + 2), lev + 1
      from cte
      where stnames like '%,%'
     )
, bySubject as (
select `Date`,subj1 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj2 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj3 as subj, stname
from cte
where lev > 1
)
select `Date`,subj,stname
from bySubject

테스트 DB의 경우 다음과 같습니다.

|날짜 |subj |stname ||--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|stname1| ...

여기에서 피들을 참조하십시오. https://dbfiddle.uk/ ?rdbms=sysadb_10.3&fsdle=f31cab287fdd575054e3af457a9cf14d

마지막으로 이 데이터를 각 학생의 열로 피벗합니다.여기서 MariaDB는 실제로 우리를 실망시키고, 피벗에 대한 내장된 지원이 없으며, 사용자가 직접 결과를 수동으로 구성해야 합니다.더 많은 CTE를 사용할 수 있지만, 애플리케이션이 SQL을 구성할 수 있다면, 더 간단한 쿼리는 단순히CASE각 열을 구성하는 문:

with recursive cte as (
      select `Date`,subj1,subj2,subj3,'            ' as stname, concat(stnames, ',') as stnames, 1 as lev
      from table_timetable
      union all
      select `Date`,subj1,subj2,subj3,substring_index(stnames, ',', 1),
             substr(stnames, instr(stnames, ',') + 2), lev + 1
      from cte
      where stnames like '%,%'
     )
, bySubject as (
select `Date`,subj1 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj2 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj3 as subj, stname
from cte
where lev > 1
)
select teacher.`teach-id`, teach, teacher
    , MAX(CASE WHEN stname = 'stname1' THEN attendance.Date END) AS stname1
    , MAX(CASE WHEN stname = 'stname2' THEN attendance.Date END) AS stname2
    , MAX(CASE WHEN stname = 'stname3' THEN attendance.Date END) AS stname3
    , MAX(CASE WHEN stname = 'stname4' THEN attendance.Date END) AS stname4
    , MAX(CASE WHEN stname = 'stname5' THEN attendance.Date END) AS stname5
FROM bySubject tt
/*INNER JOIN table_students s ON tt.stname = s.student*/
INNER JOIN table_stpresents attendance ON tt.Date = attendance.Date AND tt.stname = attendance.student
INNER JOIN table_toteach teacher ON tt.subj = teacher.teach
WHERE attendance.present = '- yes -'
GROUP BY teacher.`teach-id`, teach, teacher
ORDER BY `teach-id`;

참고: 가입에 대해 의견을 제시했습니다.table_student해당 테이블의 정보가 필요하지 않기 때문에 실제로 이 출력에 정보를 포함하는 것은 어려울 수 있습니다. 열 이름을 해결해야 할 경우 응용 프로그램 논리에 맡기는 것이 좋습니다.
조인을 사용하여 필터 기준을 추가할 수 있으므로 탐색할 수 있도록 남겨 둡니다.

그 결과:

티치아이드	가르치다	선생님.	stname1	stname2	stname3	stname4	stname5
1.0	서브j1-	Tname1	2021-08-18	2021-08-18	2021-08-19	2021-08-19	2021-08-18
1.2	서브j2-	Tname2	2021-08-18	2021-08-18	2021-08-19	2021-08-19	2021-08-18
1.5	서브j3-	Tname3	2021-08-18	2021-08-18	2021-08-16	2021-08-19	2021-08-18
2.0	서브j4-	Tname4	2021-08-18	2021-08-18	2021-08-19		2021-08-18

은 이은더간형다니태입의 더 간단한 입니다.PIVOT코드에서 쿼리를 생성하는 경우 유지 관리하려면 각 학생 열에 대해 max 문을 반복하면 됩니다.

이 최종 솔루션은 다음 db<>dbms https://dbfiddle.uk/ ?rdbms=sysadb_10.3&dbdle=f07c943d3d92ea0de8d91021728ab7c0에 있습니다.

다른 방법은 피벗 인 마리아(Pivoting in MariaDB

언급URL : https://stackoverflow.com/questions/68831109/need-to-pivot-1-table-in-4-joins