sql - 文本枢轴问题
问题描述
我正在使用的数据示例:
TABLE 1 TABLE 2 TABLE 3
PK | Col | Status | | PK | FK | | PK | FK | Description| Value
----------------------- ------------ ----------------------------------------
123 | Data | Active | |456 | 123 | | 301 | 456 | Status | Abandoned
|789 | 123 | | 302 | 456 | Material | Steel
|012 | 123 | | 303 | 456 | Feet | 10
| 304 | 789 | Status | Installed
| 305 | 789 | Material | Plastic
| 306 | 789 | Feet | 15
| 307 | 012 | Status | Removed
| 308 | 012 | Material | Steel
| 309 | 012 | Feet | 5
我需要返回:
t1.PK | t1.Col | t3.Status | t3.Material | t3.Feet
-------------------------------------------------------------
123 | Value | Abandoned | Steel | 10
我遇到的问题是 PIVOT 所需的聚合函数。我写的查询是:
SELECT *
FROM
(SELECT t1.PK, t1.Col, t3.Description, t3.Value
FROM table1 t1
JOIN table2 t2 on t1.pk = t2.fk
JOIN table3 t3 on t2.pk = t3.fk
WHERE t3.Description in ('Status', 'Material', 'Feet')) as SourceTable
PIVOT(MIN(Value) For Description in ([Status], [Material], [Feet])) as PivotTable
WHERE [Status] = 'Abandoned'
我遇到的问题是使用 MIN 将导致以下返回
t1.PK | t1.Col | t3.Status | t3.Material | t3.Feet
----------------------------------------------------
123 | Data | Abandoned | Plastic | 5
并且使用MAX
不会返回任何内容,因为我试图将状态限制为“已放弃”。
有一个更好的方法吗?我想我明白为什么它会返回这些值,我只是不确定如何更正。
解决方案
您可以使用条件聚合:
SELECT t1.PK, t1.Col,
MAX(CASE WHEN t3.Description = 'Status' THEN t3.Value END) as status,
MAX(CASE WHEN t3.Description = 'Material' THEN t3.Value END) as material,
MAX(CASE WHEN t3.Description = 'Feet' THEN t3.Value END) as feet
FROM table1 t1 JOIN
table2 t2
ON t1.pk = t2.fk JOIN
table3 t3
ON t2.pk = t3.fk
WHERE t3.Description in ('Status', 'Material', 'Feet')
GROUP BY t1.PK, t1.Col, t3.PK
HAVING MAX(CASE WHEN t3.Description = 'Status' THEN t3.Value END) = 'Abandoned';
推荐阅读
- git - 每个功能的分支、环境分支和 Azure 管道
- mysql - Sequelize Join table1 与 table1.column 中引用的 table[X]
- r - Using textstat_simil with a dictionary or globs in Quanteda
- python - boxsizers 没有正确显示 WX.PYTHON
- elasticsearch - ElasticSearch + Logstash 工作,但不显示任何数据
- javascript - Vue v-for loop - How To Target Component When Array is Filtered
- python-3.x - 从python 3中先前用户输入的结果生成用户输入
- javascript - 使用 redux observable 取消请求不起作用
- python-3.x - Simple Way for Modifying Attributes of Single nodes in Networkx 2.1+
- excel - Excel VBA referencing local sheet name