출처: http://dev.mysql.com/tech-resources/articles/wizard/page4.html
MySQL에서 엑셀의 피벗테이블같이 사용하고 싶을 때 유용하다.
Some help from SQL itself
The Wizard smiled. "If you like to, you're welcome. However, you
could get some help from the database engine itself, provided that you
ask nicely." And while he was speaking he typed a very cryptic
statement:
mysql> SELECT CONCAT(', SUM(IF(dept = "',dept,'", 1,0)) AS `',dept,'`')
-> FROM departments;
CONCAT(', SUM(IF(dept = "',dept,'", 1,0)) AS `',dept,'`') |
, SUM(IF(dept = "Development", 1,0)) AS `Development` |
, SUM(IF(dept = "Personnel", 1,0)) AS `Personnel` |
, SUM(IF(dept = "Research", 1,0)) AS `Research` |
, SUM(IF(dept = "Sales", 1,0)) AS `Sales` |
, SUM(IF(dept = "Training", 1,0)) AS `Training` |
5 rows in set (0.00 sec)
|
"You know," he went on, "you can also use SQL to produce SQL code.
This is one of the cases. You have in front of you the list of columns
that you should include in your query. Now, with some cut-and-paste, we
could get the result you want. Here."
mysql> SELECT location
-> , SUM(IF(dept = "Development", 1,0)) AS `Development`
-> , SUM(IF(dept = "Personnel", 1,0)) AS `Personnel`
-> , SUM(IF(dept = "Research", 1,0)) AS `Research`
-> , SUM(IF(dept = "Sales", 1,0)) AS `Sales`
-> , SUM(IF(dept = "Training", 1,0)) AS `Training`
-> , COUNT(*) AS total
-> FROM locations INNER JOIN employees USING (loc_code)
-> INNER JOIN departments USING (dept_code)
-> GROUP BY location;
location |
Development |
Personnel |
Research |
Sales |
Training |
total |
Boston |
2 |
0 |
0 |
1 |
0 |
3 |
Cagliari |
0 |
0 |
3 |
0 |
0 |
3 |
London |
0 |
1 |
0 |
0 |
1 |
2 |
Manchester |
0 |
0 |
0 |
2 |
0 |
2 |
Marseille |
2 |
0 |
0 |
1 |
0 |
3 |
Milano |
0 |
0 |
0 |
1 |
1 |
2 |
New York |
3 |
0 |
0 |
1 |
0 |
4 |
Paris |
1 |
0 |
0 |
1 |
0 |
2 |
Roma |
0 |
1 |
0 |
1 |
1 |
3 |
9 rows in set (0.36 sec)
|
The wizard was now unstoppable. He had reached the stage where he simply couldn't help giving away his knowledge.
"Before we go on," he said, taking possession of my keyboard, "let's
see how this same method can do more than counting. Let's replace those
1s with a numeric field, and do real summing up."
mysql> SELECT location
-> , SUM(IF(dept = "Development", salary,0)) AS `Development`
-> , SUM(IF(dept = "Personnel", salary,0)) AS `Personnel`
-> , SUM(IF(dept = "Research", salary,0)) AS `Research`
-> , SUM(IF(dept = "Sales", salary,0)) AS `Sales`
-> , SUM(IF(dept = "Training", salary,0)) AS `Training`
-> , SUM(salary) AS total
-> FROM locations INNER JOIN employees USING (loc_code)
-> INNER JOIN departments USING (dept_code)
-> GROUP BY location;
location |
Development |
Personnel |
Research |
Sales |
Training |
total |
Boston |
11900 |
0 |
0 |
5950 |
0 |
17850 |
Cagliari |
0 |
0 |
16800 |
0 |
0 |
16800 |
London |
0 |
5700 |
0 |
0 |
5700 |
11400 |
Manchester |
0 |
0 |
0 |
11550 |
0 |
11550 |
Marseille |
11150 |
0 |
0 |
5800 |
0 |
16950 |
Milano |
0 |
0 |
0 |
5550 |
4900 |
10450 |
New York |
17850 |
0 |
0 |
6100 |
0 |
23950 |
Paris |
5700 |
0 |
0 |
5400 |
0 |
11100 |
Roma |
0 |
5000 |
0 |
5500 |
5100 |
15600 |
9 rows in set (0.00 sec)
|
"Don't forget to change also the total field. A simple SUM without IF, and your total is ready."
I was looking at the screen, which was showing what seemed to be the
complete solution to my problem, but the wizard was shaking his head.
"As a matter of fact," he was saying, there is something that we can
improve here. We have two queries, in which we are reading the departments
table. So we are reading it twice twice. The whole process could be
improved, by querying for department code the first time, and omitting
the join with departments the second time."
mysql> SELECT CONCAT(', SUM(IF(dept_code = "',dept_code,'", 1,0)) AS `',dept,'`')
-> FROM departments;
|
"Here. Let's get the columns once more. Good. And there it is. This one looks better."
mysql> SELECT location
-> , SUM(IF(dept_code = "1", 1,0)) AS `Personnel`
-> , SUM(IF(dept_code = "2", 1,0)) AS `Training`
-> , SUM(IF(dept_code = "3", 1,0)) AS `Research`
-> , SUM(IF(dept_code = "4", 1,0)) AS `Sales`
-> , SUM(IF(dept_code = "5", 1,0)) AS `Development`
-> , COUNT(*) AS total
-> FROM locations INNER JOIN employees USING (loc_code)
-> GROUP BY location;
|
He changed the previous two SQL statements, executed them, with some
cut-and-pasting in the middle, and got exactly the same result. Now he
was explaining me why he did it. "The first query is scanning all the
departments table, and we know that its results will be used to build
the second query containing the employees table, which has already a
department code. Therefore, we can skip the join with departments, since
the only purpose of that join was to get the names of the departments."
"I see" was the only comment I could offer, Since I was
overwhelmed by his continuous insight. I got the idea, and I couldn't
help thinking that he must have done that before. All those pieces of
information were coming just too fast for me. Luckily, all logs were on,
so I knew that I would be able to get all the statements back when he
would leave. Which was not the case yet. The wizard was now ready to
give me his philosophical view of cross tabulating.