我有以下兩個表。每個Object可以有0-4個對應的Attributes，存放在Attributes表中。
物件
| ID | 標題 |
|---|---|
| 1 | Α |
| 2 | 測驗版 |
| 3 | 伽瑪 |
屬性
| ID | 物件ID | 屬性名稱 | 屬性值 |
|---|---|---|---|
| 1 | 1 | K1 | 12345 |
| 2 | 1 | K2 | 23456 |
| 3 | 2 | K1 | 34567 |
| 4 | 3 | K2 | 45678 |
| 5 | 3 | K3 | 56789 |
| 6 | 3 | K4 | 67890 |
現在我想要以下結果，其中為每個可能的屬性創建一個列，其中包含相應的屬性或 NULL。
屬性物件
| ID | 標題 | K1 | K2 | K3 | K4 |
|---|---|---|---|---|---|
| 1 | Α | 12345 | 23456 | ||
| 2 | 測驗版 | 34567 | |||
| 3 | 伽瑪 | 45678 | 56789 | 67890 |
我提出了兩個查詢，它們產生了想要的結果。我被迫使用的資料庫不支持 PIVOT 子句。在這種情況下，以下哪兩個對想要的結果更有效？
基于 SELECT 的查詢
SELECT ID, Title,
(SELECT AttributeValue FROM Attributes WHERE AttributeName = "K1" AND ObjectID = Objects.ID) AS K1,
(SELECT AttributeValue FROM Attributes WHERE AttributeName = "K2" AND ObjectID = Objects.ID) AS K2,
(SELECT AttributeValue FROM Attributes WHERE AttributeName = "K3" AND ObjectID = Objects.ID) AS K3,
(SELECT AttributeValue FROM Attributes WHERE AttributeName = "K4" AND ObjectID = Objects.ID) AS K4
FROM Objects;
基于JOIN的查詢
SELECT Objects.ID, Objects.Title, Q1.K1, Q2.K2, Q3.K3, Q4.K4
FROM (((Objects
LEFT JOIN (SELECT ObjectID, AttributeValue AS K1 FROM Attributes WHERE AttributeName = "K1")
AS Q1 ON Objects.ID = Q1.ObjectID)
LEFT JOIN (SELECT ObjectID, AttributeValue AS K2 FROM Attributes WHERE AttributeName = "K2")
AS Q2 ON Objects.ID = Q2.ObjectID)
LEFT JOIN (SELECT ObjectID, AttributeValue AS K3 FROM Attributes WHERE AttributeName = "K3")
AS Q3 ON Objects.ID = Q3.ObjectID)
LEFT JOIN (SELECT ObjectID, AttributeValue AS K4 FROM Attributes WHERE AttributeName = "K4")
AS Q4 ON Objects.ID = Q4.ObjectID;
uj5u.com熱心網友回復：
使用Pivot與Join
SELECT ID,Title,
K1,K2,K3,K4
FROM
(
SELECT o.ID,Title,AttributeName, AttributeValue
FROM Objects o
JOIN Attributes a ON o.ID = a.ObjectID
) AS SourceTable
PIVOT
(
AVG(AttributeValue)
FOR AttributeName IN (K1,K2,K3,K4)
) AS PivotTable;
db<>fiddle 中的演示
uj5u.com熱心網友回復：
使用conditional aggregation方法如下：
select o.id, o.title,
Max(case when AttributeName='K1' then AttributeValue end) K1,
Max(case when AttributeName='K2' then AttributeValue end) K2,
Max(case when AttributeName='K3' then AttributeValue end) K3,
Max(case when AttributeName='K4' then AttributeValue end) K4
from objects o
join attributes a on a.ObjectID = o.id
group by o.Id, o.title
uj5u.com熱心網友回復：
我提出了另一種選擇，使用IForCASE和 and似乎更有效GROUP BY，因此您不必旋轉任何東西。因為我不知道您如何處理這些值，所以我使用了SUM. 請參閱以下內容：
SELECT Title,
SUM(IF(AttributeName = 'K1', AttributeValue, 0)) AS K1,
SUM(IF(AttributeName = 'K2', AttributeValue, 0)) AS K2,
SUM(IF(AttributeName = 'K3', AttributeValue, 0)) AS K3,
SUM(IF(AttributeName = 'K4', AttributeValue, 0)) AS K4
FROM AttributedObjects AS AO
INNER JOIN Attributes AS A ON AO.ObjectID = O.ID
GROUP BY Title
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/396563.html