전제 조건:
PIVOT() 함수와 같이 한 테이블의 행이 열로 변환되는 데이터베이스 및 일부 관련 테이블을 생성해야 합니다. 다음 SQL 문을 실행하여 ' 유니DB ' 라는 이름의 세 테이블을 생성합니다. 재학생 ',' 과정 ' 그리고 ' 결과 '. 재학생 그리고 결과 테이블은 일대다 관계로 관련되며 과정 그리고 결과 여기서 테이블은 일대다 관계로 연결됩니다. CREATE 문 결과 테이블에는 필드에 대한 두 개의 외래 키 제약 조건이 있습니다. std_id , 그리고 코스 아이디 .
데이터베이스 생성
UNIDB를 사용하십시오.
CREATE TABLE 학생(
IDINT 기본 키,
이름 varchar(오십)NULL이 아님,
부서 VARCHAR(열 다섯)NULL이 아님);
CREATE TABLE 과정(
코스 ID VARCHAR(스물)기본 키,
이름 varchar(오십)NULL이 아님,
신용 SMALLINT NOT NULL);
테이블 생성 결과(
std_id INT NOT NULL,
코스 ID VARCHAR(스물)NULL이 아님,
mark_type VARCHAR(스물)NULL이 아님,
SMALLINT NOT NULL 표시,
외래 키(std_id)참조 학생(ID),
외래 키(코스 아이디)참조 코스(코스 아이디),
기본 키(std_id, 코스 ID, 마크 유형));
일부 레코드 삽입 학생, 과정 및 결과 테이블. 테이블 생성 시 설정한 제한 사항에 따라 테이블에 값을 삽입해야 합니다.
INSERT INTO 학생 가치
( '1937463','하퍼 리','CSE'),
( '1937464',가르시아 마르케스,'CSE'),
( '1937465','포스터, E.M.','CSE'),
( '1937466','랄프 엘리슨','CSE');
INSERT INTO 코스 값
( 'CSE-401','객체 지향 프로그래밍',삼),
( 'CSE-403','데이터 구조',2),
( 'CSE-407','유닉스 프로그래밍',2);
결과 값에 삽입
( '1937463','CSE-401','내부 시험',열 다섯),
( '1937463','CSE-401','중간 고사',스물),
( '1937463','CSE-401','최종 시험',35),
( '1937464','CSE-403','내부 시험',17),
( '1937464','CSE-403','중간 고사',열 다섯),
( '1937464','CSE-403','최종 시험',30),
( '1937465','CSE-401','내부 시험',18),
( '1937465','CSE-401','중간 고사',2. 3),
( '1937465','CSE-401','최종 시험',38),
( '1937466','CSE-407','내부 시험',스물),
( '1937466','CSE-407','중간 고사',22),
( '1937466','CSE-407','최종 시험',40);
여기, 결과 테이블에 동일한 값이 여러 개 포함되어 있습니다. std_id , mark_type 그리고 코스 아이디 각 행의 열. 보다 체계적인 형식으로 데이터를 표시하기 위해 이러한 행을 이 테이블의 열로 변환하는 방법은 이 자습서의 다음 부분에 나와 있습니다.
CASE 문을 사용하여 행을 열로 회전:
다음의 간단한 SELECT 문을 실행하여 모든 레코드를 표시합니다. 결과 테이블.
선택하다*결과에서;
출력은 세 과목의 세 가지 시험 유형에 대한 네 학생의 점수를 보여줍니다. 따라서 의 값 std_id , 코스 아이디 그리고 mark_type 다른 학생, 코스 및 시험 유형에 대해 여러 번 반복됩니다.
CASE 문을 사용하여 SELECT 쿼리를 보다 효율적으로 작성할 수 있다면 출력을 더 읽기 쉽게 할 수 있습니다. CASE 문을 사용하는 다음 SELECT는 행의 반복 값을 열 이름으로 변환하고 테이블의 내용을 사용자가 더 이해하기 쉬운 형식으로 표시합니다.
SELECT result.std_id, result.course_id,최대(CASE WHEN result.mark_type ='내부 시험'THEN result.marks END) '내부 시험',
최대(CASE WHEN result.mark_type ='중간 고사'THEN result.marks END) '중간 고사',
최대(CASE WHEN result.mark_type ='최종 시험'THEN result.marks END) '최종 시험'
결과에서
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
위의 명령문을 실행하면 이전 출력보다 더 읽기 쉬운 다음 출력이 나타납니다.
CASE 및 SUM()을 사용하여 행을 열로 회전:
테이블에서 모든 학생의 각 과정의 총 수를 계산하려면 집계 함수를 사용해야 합니다. 합집합() 그룹화 기준 std_id 그리고 코스 아이디 CASE 문으로. 다음 쿼리는 이전 쿼리를 SUM() 함수와 GROUP BY 절로 수정하여 생성합니다.
SELECT result.std_id,result.course_id,최대(CASE WHEN result.mark_type ='내부 시험'THEN result.marks END) '내부 시험',
최대(CASE WHEN result.mark_type ='중간 고사'THEN result.marks END) '중간 고사',
최대(CASE WHEN result.mark_type ='최종 시험'THEN result.marks END) '최종 시험',
합집합(결과.마크) 같이총
결과에서
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
출력에는 다음이라는 새 열이 표시됩니다. 총 그것은 각 특정 학생이 취득한 각 과정의 모든 시험 유형의 점수 합계를 표시하는 것입니다.
여러 테이블에서 행을 열로 회전:
앞의 두 쿼리는 결과 테이블. 이 테이블은 다른 두 테이블과 관련이 있습니다. 이것들은 재학생 그리고 과정 . 학생 id 대신 학생 이름을 표시 하고 코스 id 대신 코스 이름을 표시 하려면 세 개의 관련 테이블을 사용 하 여 SELECT 쿼리를 작성 해야 합니다. 재학생 , 과정 그리고 결과 . 다음 SELECT 쿼리는 FORM 절 뒤에 3개의 테이블 이름을 추가하고 WHERE 절에 적절한 조건을 설정하여 3개의 테이블에서 데이터를 검색하고 이전 SELECT 쿼리보다 더 적절한 출력을 생성하여 생성됩니다.
SELECT 학생.이름같이 ``학생 이름``, 코스 이름같이 ``코스명``,최대(CASE WHEN result.mark_type ='내부 시험'THEN result.marks END) '씨티',
최대(CASE WHEN result.mark_type ='중간 고사'THEN result.marks END) '미드',
최대(CASE WHEN result.mark_type ='최종 시험'THEN result.marks END) '결정적인',
합집합(결과.마크) 같이총
FROM 학생, 과정, 결과
WHERE result.std_id = Students.id 및 result.course_id=courses.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
위의 쿼리를 실행하면 다음 출력이 생성됩니다.
결론:
MySQL에서 Pivot() 함수를 지원하지 않고 Pivot() 함수의 기능을 구현하는 방법은 더미 데이터를 사용하여 이 기사에 나와 있습니다. 독자들이 이 기사를 읽은 후 SELECT 쿼리를 사용하여 행 수준 데이터를 열 수준 데이터로 변환할 수 있기를 바랍니다.