MySQL 특정 칼럼의 행 비교후 개수 카운트

MySQL 특정 칼럼의 행 비교후 개수 카운트

테이블을 정렬하여 테이블을 새로 생성하여 만든 테이블이 test_sort 테이블이다.

테이블의 특정 칼럼(serviceNo)이 중복된 값을 가지고 있다. 중복된 숫자를 카운트로 좀 나타내고 싶다.

어떻게 해야 할까?

결론부터 얘기하지면 아래 모습으로 결과를 얻고 싶다.

위아래 칼럼이 서로 같은 경우 개수를 증가하게 표시를 하고 싶다.

select uid, Name, serviceNo, if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1) as checking, openDate, closeDate, (@serviceNo_prev:=serviceNo) as serviceNo_prev from test_sort limit 20;

일단 전부를 다 표시하는 것보다 20개만 발췌하여 결과값을 얻고 싶었다.

checking 이란 칼럼은 실제 test_sort 테이블에는 존재하지 않는 칼럼이라는 것은 위의 그림에서 확인할 수 있다.

그럼 이런 결과를 도출할 수 있을까?

사용자변수를 이용하여 1행, 2행, 3행 이렇게 비교를 하는 것이다.

SELECT / FROM / WHERE 구조의 의미는 다 아는 것이지만 다시 한번 살펴보자.

가장 먼저 FROM 테이블명에서 값을 가져오고, 그 다음에 WHERE 에서 조건에 맞는 것만 가져온다.

그 다음에 SELECT 구문에서 조회(출력)한다.

1행에서 @serviceNo_prev:=serviceNo 에는 serviceNo 값을 할당한다.

하지만 if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1)이 먼저 실행된다.

@serviceNo_prev 라는 사용자변수에는 값이 할당되지 않았다.

따라서 첫행에서는 값이 서로다르다. 따라서 @rownum:=1 이 선택된다.

2행에서는 if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1)

2행의 serviceNo 값과 @serviceNo_prev 에는 1행의 serviceNo 를 할당했으므로 서로 동일하다.

따라서 @rownum:=@rownum+1 즉, 2가 된다.

이런 식으로 처리하여 행간에 값을 비교하여 checking 칼럼을 생성한다.

그런데 보여주고자 하는 값이 uid, Name, serviceNo, checking, openDate, closeDate 이고 serviceNo_prev 는 아니라고 하면 어떻게 해야 할까?

윗 그림 전체를 임시테이블로 생각하면 된다. 즉 실제 물리적인 테이블에 저장된 것이라고 보고

테이블에서 원하는 필드만 조회(출력)해서 보여주면 된다.

select uid, Name, serviceNo, checking, openDate, closeDate from (select uid, Name, serviceNo, if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1) as checking, openDate, closeDate, (@serviceNo_prev:=serviceNo) as serviceNo_prev from test_sort limit 20) a;

이렇게 이해하면 쉽게 이해가 될 것이다.

/* 테이블 구조만 복사 */

CREATE TABLE `생성될 테이블명` LIKE `구조를 복사할 테이블`;

/* 구조와 내용을 복사 */

CREATE TABLE `생성될 테이블명` AS SELECT * FROM `구조를 복사할 테이블`;

테이블 구조만 복사해서 새로운 테이블을 생성해보자.

테이블 구조에 필요한 칼럼이 하나 빠져있다. checking 칼럼을 추가한다.

- 지정 칼럼 뒤에 : ALTER TABLE 테이블명 ADD COLUMN 칼럼명 칼럼타입 AFTER 칼럼이름
- 제일 앞에        : ALTER TABLE 테이블명 ADD COLUMN 칼럼명 칼럼타입 FIRST
- 마지막에 추가  : ALTER TABLE 테이블명 ADD COLUMN 칼럼명 칼럼타입

칼럼 type 이 default 가 NULL 이라서 default 를 1로 변경하고자 한다.

/* 컬럼 타입 수정 */
alter table 테이블명 modify 컬럼명 varchar(14);
alter table test_fin modify checking int(2) not null default '1';

alter table test_fin modify checking tinyint(1) not null default '1';

공간을 가장 작게 차지하는 tinyint 로 변경해봤다.

이제 새로 만든 테이블에 위에서 작업한 내용을 복사해서 테이브를 집어넣어 보자.

INSERT INTO 테이블명 (칼럼1, 칼럼2, ...)

SELECT 구문

을 사용하면 된다.

insert into test_fin (uid, Name, serviceNo, checking, openDate, closeDate)
select uid, Name, serviceNo, checking, openDate, closeDate from (select uid, Name, serviceNo, if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1) as checking, openDate, closeDate, (@serviceNo_prev:=serviceNo) as serviceNo_prev from test_sort) a;

제대로 데이터가 입력되었는지 확인해보니 정상적으로 입력되었다.

테이블 작업을 해서 새로운 테이블에 칼럼을 추가한 결과를 저정했다.

위 작업을 하면서 결과가 제대로 나오기 위해서는 EditPlus 에서 내용을 입력한 후 한줄로 처리한 다음에 복사하여 붙여넣기를 할 경우에만 정상적으로 값이 나왔다.

두줄로 분리하여 작업을 한 경우에는 사용자변수가 제대로 처리가 안되었다.