我被要求使用基本 SQL 對這些資料進行透視，但不知道如何回答。我用谷歌搜索了一些答案，發現你可以使用 MAX 或 SUM 和 CASE 運算式，但在面試結束時我問你如何解決這個問題，面試官說使用連接。誰能告訴我它是如何使用連接完成的？
開始表
| emp_id | col_id | col_desc | 態度值 | 月 |
|---|---|---|---|---|
| 1 | 1 | 薪水 | 2000 | 2010-05-09 |
| 1 | 2 | 獎金 | 0 | 2010-05-09 |
| 1 | 3 | 賠償 | 2000 | 2010-05-09 |
| 1 | 1 | 薪水 | 2000 | 2010-05-10 |
| 1 | 2 | 獎金 | 500 | 2010-05-10 |
| 1 | 3 | 賠償 | 2500 | 2010-05-10 |
| 2 | 1 | 薪水 | 1000 | 2010-05-09 |
| 2 | 2 | 獎金 | 500 | 2010-05-09 |
| 2 | 3 | 賠償 | 1500 | 2010-05-09 |
創建起始表的代碼
CREATE TABLE Employees(emp_id INT, col_id INT, col_desc NVARCHAR(MAX), attvalue INT, month DATE);
INSERT INTO Employees
VALUES
(1,1,'salary',2000,'2010-05-09'),
(1,2,'bonus',0,'2010-05-09'),
(1,3,'compensation',2000,'2010-05-09'),
(1,1,'salary',2000,'2010-05-10'),
(1,2,'bonus',500,'2010-05-10'),
(1,3,'compensation',2500,'2010-05-10'),
(2,1,'salary',1000,'2010-05-09'),
(2,2,'bonus',500,'2010-05-09'),
(2,3,'compensation',1500,'2010-05-09');
結果表
| emp_id | 月 | 薪水 | 獎金 | 賠償 |
|---|---|---|---|---|
| 1 | 2010-05-09 | 2000 | 0 | 2000 |
| 1 | 2010-05-10 | 2000 | 500 | 2500 |
| 2 | 2010-05-09 | 1000 | 500 | 1500 |
uj5u.com熱心網友回復：
下面是self join、case運算式和pivot方式
-- Self Join way
select s.emp_id, s.month,
s.attvalue as salary,
b.attvalue as bonus,
c.attvalue as compensation
from Employees s
inner join Employees b on s.emp_id = b.emp_id
and s.month = b.month
inner join Employees c on s.emp_id = c.emp_id
and s.month = c.month
where s.col_desc = 'salary'
and b.col_desc = 'bonus'
and c.col_desc = 'compensation'
order by s.emp_id, s.month
-- case expression way
select emp_id, month,
max(case when col_desc = 'salary' then attvalue else 0 end) as salary,
max(case when col_desc = 'bonus' then attvalue else 0 end) as bonus,
max(case when col_desc = 'compensation' then attvalue else 0 end) as compensation
from Employees
group by emp_id, month
order by emp_id, month
-- Pivot way
select *
from (
select emp_id, month, col_desc, attvalue
from Employees
) d
pivot
(
max(attvalue)
for col_desc in ([salary], [bonus], [compensation])
) p
order by emp_id, month
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/515047.html
標籤：sqlsql服务器加入枢
上一篇：基于a加入兩組時性能不佳