gpt4 book ai didi

sql - 如何根据映射表选择列名?

转载 作者:行者123 更新时间:2023-12-05 01:11:55 25 4
gpt4 key购买 nike

您好,我想做的是:我有 2 个表 第一个是

Person Table
PID | Name | Surname | Area1 | Area2 | Area3 | OwnerID |
1 | John | Doe | 5 | 6 | 8 | 1 |
2 | Jack | Danniel | 8 | 2 | 4 | 2 |

第二个是

Area Table
AID | Value | Name | OwnerID |
1 | Java | Area1 | 1 |
2 | Orac | Area2 | 1 |
3 | Delp | Area1 | 2 |
4 | Css | Area3 | 1 |

如果我想选择 Owner1 ,如何查询这样的结果?

PID | Name | Surname | Java  | Orac  | Css   | OwnerID |
1 | John | Doe | 5 | 6 | 8 | 1 |

最佳答案

WITH A AS (
SELECT PID, Name Name1, Surname, Area, _Area, OwnerID OwnerID1
from Person A
unpivot (_Area FOR Area IN (Area1, Area2, Area3)) C
)

select PID, Name1, Surname, sum(Java) JAVA, sum(Orac) Orac, sum(CSS) CSS, OwnerID
from A
inner join Area B
on Area = B.Name
and A.OwnerID1 = B.OwnerID
pivot (sum(_Area) for Value in (JAVA, ORAC, CSS)) C
where OwnerID1 = 1
group by PID, Name1, Surname, OwnerID

SQL Fiddle

关于sql - 如何根据映射表选择列名?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18667818/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com