[스크랩] ▣ [MySQL] 급수별 연령대별 통계내는 SQL

일단 임시테이블을 만들고 연령대별로 데이타를 넣은 다음
그 임시테이블을 SELECT 하는 방식입니다.
연령대별(20대, 30대...) 모으는 조건에 대한 tip 이라고나 할까요..

drop table if exists agegrade;

create temporary table agegrade(
  age CHAR(4),
grade1 int,
grade2 int,
grade3 int);

INSERT INTO agegrade SELECT
'20대',
sum(if(grade = '고급',1,0))AS grade1,
sum(if(grade = '중급',1,0))AS grade1,
sum(if(grade = '초급',1,0))AS grade1
FROM member
where (year(now()) - (mid(jumin,1,2) + 1900)) BETWEEN 20 and 29;

INSERT INTO agegrade SELECT
'30대',
sum(if(grade = '고급',1,0))AS grade1,
sum(if(grade = '중급',1,0))AS grade1,
sum(if(grade = '초급',1,0))AS grade1
FROM member
where (year(now()) - (mid(jumin,1,2) + 1900)) BETWEEN 30 and 39;

INSERT INTO agegrade SELECT
'40대',
sum(if(grade = '고급',1,0))AS grade1,
sum(if(grade = '중급',1,0))AS grade1,
sum(if(grade = '초급',1,0))AS grade1
FROM member
where (year(now()) - (mid(jumin,1,2) + 1900)) BETWEEN 40 and 49;

INSERT INTO agegrade SELECT
'50대',
sum(if(grade = '고급',1,0))AS grade1,
sum(if(grade = '중급',1,0))AS grade1,
sum(if(grade = '초급',1,0))AS grade1
FROM member
where (year(now()) - (mid(jumin,1,2) + 1900)) BETWEEN 50 and 59;

INSERT INTO agegrade SELECT
'60대',
sum(if(grade = '고급',1,0))AS grade1,
sum(if(grade = '중급',1,0))AS grade1,
sum(if(grade = '초급',1,0))AS grade1
FROM member
where (year(now()) - (mid(jumin,1,2) + 1900)) BETWEEN 60 and 69;

SELECT * FROM agegrade;

출처 : SQL with neXPice

글쓴이 : database 원글보기

메모 :