我是 sql server 的新手，在使用 FOR XML 代碼時發現了一個問題。我的報表生成器使用的是 sql server 2014。
我有這樣的桌子：
| 場地 | 配飾 |
|---|---|
| 1.我 | AA |
| 1.我 | BB |
| 1.我 | 抄送 |
| 1.II | AA |
| 1.III | AA |
| 1.III | BB |
| 1.IV | AA |
| 1.IV | BB |
| 1.V | AA |
結果需要是：
| 場地 | 配飾 |
|---|---|
| 1.我 | AA,BB,CC |
| 1.II | AA |
| 1.III | AA,BB |
| 1.IV | AA,BB |
| 1.V | AA |
但相反，我有這個：
| 場地 | 配飾 |
|---|---|
| 1.我 | AA,BB,CC,AA,AA,BB,AA,BB,AA |
| 1.II | AA,BB,CC,AA,AA,BB,AA,BB,AA |
| 1.III | AA,BB,CC,AA,AA,BB,AA,BB,AA |
| 1.IV | AA,BB,CC,AA,AA,BB,AA,BB,AA |
| 1.V | AA,BB,CC,AA,AA,BB,AA,BB,AA |
對這種表分組使用 FOR XML 路徑是否合適？謝謝！
查詢是：
SELECT
Radi.Field,
(SELECT
Acce.AccessoryId ','
FROM
FieldAcce
INNER JOIN Radi ON FieldAcce.RadiSer = Radi.RadiSer
INNER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
WHERE
Radi.Id LIKE UPPER (@RNO)
AND Radi.CourseID LIKE @CourseID
AND Radi.PhaseId LIKE @PhaseID
ORDER BY
Radi.Field
FOR XML PATH('')) AS [Accessory]
FROM
FieldAcce
INNER JOIN Radi ON FieldAcce.RadiSer = Radi.RadiSer
INNER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
WHERE
Radi.Id LIKE UPPER (@RNO)
AND Radi.CourseID LIKE @CourseID
AND Radi.PhaseId LIKE @PhaseID
GROUP BY
Radi.Field
Acce.AccessoryId
ORDER BY
Radi.Field
新查詢如下（它在 STUFF() 部分未添加時有效）：
SELECT
r.Field,
STUFF(
(SELECT
', ' Acce.AccessoryId
FROM
Radi
INNER JOIN FieldAcce ON Radi.RadiSer = FieldAcce.RadiSer
LEFT OUTER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
WHERE
Radi.Id LIKE UPPER (@RNO)
AND Radi.CourseId LIKE @CourseID
AND LEFT (Radi.PlanSetupId,1) LIKE @PhaseID
AND r.Field = Radi.Field
ORDER BY
Radi.Field
FOR XML PATH(''))
), 1, 1, '') AS [Accessory]
FROM
Radi r
INNER JOIN FieldAcce ON r.RadiSer = FieldAcce.RadiSer
LEFT OUTER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
WHERE
Radi.Id LIKE UPPER (@RNO)
AND Radi.CourseId LIKE @CourseID
AND LEFT (Radi.PlanSetupId,1) LIKE @PhaseID
AND EXISTS
(SELECT
r.Field
FROM
Radi cry
WHERE
r.Field = cry.Field
AND cry.Field NOT LIKE N'APER%')
GROUP BY
r.Field
ORDER BY
r.Field
附件的樣本資料是
| 配飾 |
|---|
| EDW45IN |
| A10 |
| A06 |
| EDW60IN |
| EDW45OUT |
| NDS |
| A084 |
uj5u.com熱心網友回復：
子查詢應該參考主查詢Field和AccessoryId
SELECT
Radi.Field,
(SELECT
a.AccessoryId ','
FROM
FieldAcce fa
INNER JOIN Radi r ON fa.RadiSer = r.RadiSer
INNER JOIN Acce a ON fa.AcceSer = a.AcceSer
WHERE
r.Id LIKE UPPER (@RNO)
AND r.CourseID LIKE @CourseID
AND r.PhaseId LIKE @PhaseID
-- add the following 2 lines
AND r.Field = Radi.Field
AND a.AccessoryId = Acce.AccessoryId
ORDER BY
r.Field
FOR XML PATH('')) AS [Accessory]
FROM
FieldAcce
INNER JOIN Radi ON FieldAcce.RadiSer = Radi.RadiSer
INNER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
WHERE
Radi.Id LIKE UPPER (@RNO)
AND Radi.CourseID LIKE @CourseID
AND Radi.PhaseId LIKE @PhaseID
GROUP BY
Radi.Field,
Acce.AccessoryId
ORDER BY
Radi.Field
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/430492.html
標籤：xml 嵌套的 级联 sql-server-2014 合并
上一篇：在LinearLayout.addView()和Linearlayout.removeView()之后出現錯誤“指定的子級已經有父級”