MySQL 经典写法

# MySQL 经典写法
## 员工商品统计表（动态行转列）
### 题目
**数据表说明**
```
--员工表
emp(eid,ename) --eid 员工编号,ename 员工姓名
--商品表
wares(wid,wname) --wid 商品编号,wname 商品名称
--业绩表
sc(eid,wid,sv) --eid 员工编号, wid 商品编号,sv 销售额
```
**要求**
以商品编号为列，员工编号为行，输出所有销售额记录，按员工编号排序

**员工表**
```sql
CREATE TABLE `emp` (
`eid` INT DEFAULT NULL,
`ename` VARCHAR(100) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `emp`(`eid`,`ename`) VALUES
(1,'张三'),
(2,'李四'),
(3,'王五'),
(4,'刘六');
```

**商品表**
```sql
CREATE TABLE `wares` (
`wid` VARCHAR(20) DEFAULT NULL,
`wname` VARCHAR(100) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `wares`(`wid`,`wname`) VALUES
('001','电脑'),
('002','手机'),
('003','茶杯'),
('004','电视'),
('005','餐巾纸');
```

**业绩表**
```sql
CREATE TABLE `sc` (
`eid` INT DEFAULT NULL,
`wid` VARCHAR(20) DEFAULT NULL,
`sv` DECIMAL(10,2) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `sc`(`eid`,`wid`,`sv`) VALUES
(3,'002',121.21),
(2,'001',88.12),
(2,'005',33.12),
(1,'002',901.10),
(3,'003',100.11),
(1,'002',888.88);
```

### 涉及知识点
#### 行转列
```sql
select t2.eid,t2.ename,
MAX(case when t3.wname = '电脑' then t1.sv else 0 end) as '电脑',
MAX(case when t3.wname = '手机' then t1.sv else 0 end) as '手机',
MAX(case when t3.wname = '茶杯' then t1.sv else 0 end) as '茶杯',
MAX(case when t3.wname = '电视' then t1.sv else 0 end) as '电视',
MAX(case when t3.wname = '餐巾纸' then t1.sv else 0 end) as '餐巾纸'
from sc t1
left join emp t2 on t1.eid = t2.eid
left join wares t3 on t3.wid = t1.wid
group by t2.eid,t2.ename
order by t2.eid
```

#### 动态执行sql
```sql
SET @sql = 'select * from emp';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
```
```sql
SET @_sql = 'select ? + ?';
SET @a = 5;
SET @b = 6;
PREPARE stmt FROM @_sql; // 预定义sql
EXECUTE stmt USING @a,@b; // 传入两个会话变量来填充sql中的 ?
DEALLOCATE PREPARE stmt; // 释放连接
```

#### selet into @sql 赋值
```sql
set @allname = null;
select group_concat(ename) into @allname from emp;
select @allname;
```

### 解决方案
**思路**
先汇总统计，然后动态行转列，拼接sql，动态执行sql

**解决sql**
```sql
set @sql = null;
select group_concat(
concat('MAX(case when t3.wname = ''',wname,''' then t1.sum_sv else 0 end) as ''',wname,'''')
) into @sql from wares;
set @sql = concat('select t2.eid,t2.ename,',@sql,'
from (
select eid,wid,sum(sv) as sum_sv from sc group by eid,wid
) t1
left join emp t2 on t1.eid = t2.eid
left join wares t3 on t3.wid = t1.wid
group by t2.eid,t2.ename
order by t2.eid');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
```

**注意事项**
* 1、销售总额，要先进行业绩表内分组汇总；
* 2、`group_concat` 函数会自动添加逗号，无需手动添加；

**执行结果**

 

统计库内所有表及行数（预估行数）

SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'paedb_main'
ORDER BY table_rows DESC