[스크랩] ▣ [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;