在我们的数据库中，有一个带有控方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 ║ ╚════╩═════════════════════╩════════════════════════════════════════╝
最佳答案
你可以使用下面的代码
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
关于sql - sql server-一对多查询到一列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47396953/