sql - sql server-一对多查询到一列

Question

在我们的数据库中，有一个带有控方ID和案例代码的控方案例表。还有另一个法庭更新表（这是一个一对多的关系，因为一个起诉案件可以有多个法庭更新）
我试图创建一个报告来显示案件及其多个法院的更新，我不知道如何编写一个查询，该查询包含以下列-诉讼案件代码和法院更新。
这是起诉表的样本

╔════╦═════════════════════╗
║ID ║ ProsecutionCaseCode ║
╠════╬═════════════════════╣
║ 1 ║CASE158SG ║
╠════╬═════════════════════╣
║ 2 ║CASE688ZY ║
╠════╬═════════════════════╣
║ 3 ║CASE999SS ║
║ ║ ║
╚════╩═════════════════════╝
这是法院更新表示例（CUID是法院更新ID）

╔════╦══════════════╦═════════════════════════════════════╗
║CUid║ ProsecutionID║ UPDATE ║
╠════╬════════════ ═╬═════════════════════════════════════╣
║ 1 ║ 1 ║ 14th May - Judge wore pink ║
╠════╬══════════════╬═════════════════════════════════════╣
║ 2 ║ 1 ║ 13th May - Judge wore black ║
╠════╬══════════════╬═════════════════════════════════════╣
║ 3 ║ 1 ║ 12th May - Judge wore orange ║
╠════╬══════════════╬═════════════════════════════════════╣
║ 4 ║ 2 ║ 29th November - Judge was a no-show ║
╠════╬══════════════╬═════════════════════════════════════╣
║ 5 ║ 3 ║ 19th January - Judge is cute lol ║
╠════╬══════════════╬═════════════════════════════════════╣
║ 6 ║ 3 ║ 1st January - Judge was mean ║
╚════╩══════════════╩═════════════════════════════════════╝
这是我试图创建的报告

╔════╦═════════════════════╦════════════════════════════════════════╗
║SNO ║ ProsecutionCaseCode ║ COURT UPDATE ║
╠════╬═════════════════════╬════════════════════════════════════════╣
║ 1 ║CASE158SG ║ 14th May - Judge wore pink ║
║ ║ ║ 13th May - Judge wore black ║
║ ║ ║ 12th May - Judge wore orange ║
╠════╬═════════════════════╬════════════════════════════════════════╣
║ 2 ║CASE688ZY ║ 29th November - Judge was a no-show ║
╠════╬═════════════════════╬════════════════════════════════════════╣
║ 3 ║CASE999SS ║ 19th January - Judge is cute lol ║
║ ║ ║ 1st January - Judge was mean ║
╚════╩═════════════════════╩════════════════════════════════════════╝

Answer 1

你可以使用下面的代码

select u.ProsecutionID as sno,p.ProsecutionCaseCode  ,u.UPDATE as [COURT UPDATE] from prosecution as p inner join Update as u on p.id=u.ProsecutionID
order by u.ProsecutionID,u.CUid