MySQL如何形成查询

Question

我有 2 个正在使用的 mysql 表。

表 1 有员工信息列:

员工编号、出生日期、姓名、地址等

表 2 有薪酬信息列:employee_number(外键)、日期、奖金

我的老板要求一个屏幕来显示数据，比如在顶部水平列出日期，垂直列出员工:

        1/1/2000 | 1/1/2001 | 1/1/2002 | 1/1/2003
Bill    $500     | $600     | $700     | $900
Ferdie  $300     | $500     | $800     | $434
Tony    $450     | $234     | $432     | $343

设置查询格式以使数据集采用这种格式的最简单方法是什么？

Answer 1

您需要的是数据透视表。

MySQL 没有内置的方法来创建数据透视表，但您可以“手动”创建它:

SQL Fiddle

MySQL 5.6 架构设置:

create table employees(
  employee_number int primary key,
  name varchar(50)
);

create table payments(
  employee_number int,
  bonus_date date,
  bonus decimal(8,2)
);

insert into employees values
(1, 'Bill'), (2, 'Freddie'), (3, 'Tony');

insert into payments values
(1, '2000-1-1',500),(1, '2001-1-1',600),(1, '2002-1-1',700),(1, '2003-1-1',900),
(2, '2000-1-1',300),(2, '2001-1-1',500),(2, '2002-1-1',800),(2, '2003-1-1',434),
(3, '2000-1-1',450),(3, '2001-1-1',234),(3, '2002-1-1',432),(3, '2003-1-1',343);

查询 1:

-- 1. Prepare the column definition
select group_concat(distinct
           concat(
               "sum(case when bonus_date='", bonus_date, "' then bonus else 0 end) as `", bonus_date, "`"
           )
       )
into @sql
from payments

Results :无结果

查询 2:

-- 2. Write the full query
set @sql = concat("select e.employee_number, e.name, ", @sql, " from employees as e inner join payments as p on e.employee_number = p.employee_number group by e.employee_number")

Results :无结果

查询 3:

-- 3. Check the generated query (optional)
-- select @sql

Results :(取消上面查询的注释并检查结果)

查询 4:

-- 4. Create a prepared statement using the query you've just created
prepare stmt from @sql

Results :无结果

查询 5:

-- 5. Execute the prepared statement
execute stmt

Results :

| employee_number |    name | 2000-01-01 | 2001-01-01 | 2002-01-01 | 2003-01-01 |
|-----------------|---------|------------|------------|------------|------------|
|               1 |    Bill |        500 |        600 |        700 |        900 |
|               2 | Freddie |        300 |        500 |        800 |        434 |
|               3 |    Tony |        450 |        234 |        432 |        343 |

查询 6:

-- 6. When you're done, deallocate the prepared statement
deallocate prepare stmt

Results :无结果

---

您可能需要查看 my answer on a similar question .

希望这对您有所帮助。