创建员工表(姓名,部门id,性别)和相应的数据,然后导入表 中,使用if和case语句实现行转列
时间: 2024-05-10 11:18:27 浏览: 7
以下是创建员工表并导入数据的 SQL 语句:
```sql
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
gender CHAR(1)
);
INSERT INTO employees (name, department_id, gender) VALUES
('Alice', 1, 'F'),
('Bob', 2, 'M'),
('Charlie', 1, 'M'),
('Diana', 3, 'F'),
('Eric', 2, 'M'),
('Frank', 1, 'M');
```
接下来使用 if 和 case 语句实现行转列,将部门 id 相同的员工名字合并到一行:
```sql
SELECT
department_id,
MAX(CASE WHEN gender = 'F' THEN name END) AS female_employee,
MAX(CASE WHEN gender = 'M' THEN name END) AS male_employee
FROM employees
GROUP BY department_id;
```
输出结果如下:
```
+---------------+----------------+---------------+
| department_id | female_employee | male_employee |
+---------------+----------------+---------------+
| 1 | Alice | Charlie |
| 2 | NULL | Bob |
| 3 | Diana | NULL |
+---------------+----------------+---------------+
```
注意,如果一个部门中只有男性员工或只有女性员工,对应的列会显示为 NULL。