sql - 子查询返回所有结果
问题描述
下面的查询为我提供了一个嵌套的用户 json 对象和分配给他们的绘图，但子查询返回所有用户的所有绘图，而不是该行中的用户。我想我在子查询中缺少 WHERE 子句，但不太确定要使用什么。
SELECT (
SELECT
d.user_id,
u.name as name,
(SELECT
d.user_id,
s.status_name,
d.last_name,
d.loan_number,
d.request_date,
d.date_funded
FROM Draws d
Left Join Users u
on d.user_id = u.id
Left Join DrawStatus s
on s.id = d.status_id
WHERE u.name = name
GROUP BY u.name, d.assignee_id, s.status_name, d.last_name, d.loan_number, d.request_date, d.date_funded
For JSON PATH) Draws
FROM Draws d
Left Join Users u
on d.assignee_id = u.id
GROUP BY u.name, d.user_id
FOR JSON PATH) Processor
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
每个处理器的绘制计数都是正确的，但是每个 Draw 数组都在拉入所有绘制，而不仅仅是这一行的绘制。
{
"Processor":[
{
"assignee_id":26,
"name":"Processor One",
"draw_count":7,
"Draws":[
{
"assignee_id":26,
"status_name":"Change Orders Signed",
"last_name":"Smith",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Elevation Certificate",
"last_name":"Troy",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Inspection",
"last_name":"Rameson",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Lien Releases",
"last_name":"Hartley",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Payment Affidavit\/Notarized AIA",
"last_name":"DaSilva",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Survey",
"last_name":"Gasser",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Title Update",
"last_name":"Sherman",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":28,
"status_name":"Inspection",
"last_name":"Valcruse",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":28,
"status_name":"Payment Affidavit\/Notarized AIA",
"last_name":"Tommy",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":28,
"status_name":"Title Update",
"last_name":"Ronnie",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":30,
"status_name":"Elevation Certificate",
"last_name":"Franklin",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":30,
"status_name":"Lien Releases",
"last_name":"Bamwell",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":30,
"status_name":"Survey",
"last_name":"Roscoe",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
}
]
},
{
"assignee_id":28,
"name":"Processor 2",
"draw_count":3,
"Draws":[
{
"assignee_id":26,
"status_name":"Change Orders Signed",
"last_name":"Smith",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Elevation Certificate",
"last_name":"Troy",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Inspection",
"last_name":"Rameson",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Lien Releases",
"last_name":"Hartley",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Payment Affidavit\/Notarized AIA",
"last_name":"DaSilva",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Survey",
"last_name":"Gasser",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Title Update",
"last_name":"Sherman",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":28,
"status_name":"Inspection",
"last_name":"Valcruse",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":28,
"status_name":"Payment Affidavit\/Notarized AIA",
"last_name":"Tommy",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":28,
"status_name":"Title Update",
"last_name":"Ronnie",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":30,
"status_name":"Elevation Certificate",
"last_name":"Franklin",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":30,
"status_name":"Lien Releases",
"last_name":"Bamwell",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":30,
"status_name":"Survey",
"last_name":"Roscoe",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
}
]
},
{
"assignee_id":30,
"name":"Processor 3",
"draw_count":3,
"Draws":[
{
"assignee_id":26,
"status_name":"Change Orders Signed",
"last_name":"Smith",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Elevation Certificate",
"last_name":"Troy",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Inspection",
"last_name":"Rameson",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Lien Releases",
"last_name":"Hartley",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Payment Affidavit\/Notarized AIA",
"last_name":"DaSilva",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Survey",
"last_name":"Gasser",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":26,
"status_name":"Title Update",
"last_name":"Sherman",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":28,
"status_name":"Inspection",
"last_name":"Valcruse",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":28,
"status_name":"Payment Affidavit\/Notarized AIA",
"last_name":"Tommy",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":28,
"status_name":"Title Update",
"last_name":"Ronnie",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":30,
"status_name":"Elevation Certificate",
"last_name":"Franklin",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":30,
"status_name":"Lien Releases",
"last_name":"Bamwell",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
},
{
"assignee_id":30,
"status_name":"Survey",
"last_name":"Roscoe",
"loan_number":"123456",
"request_date":"2020-03-11T00:00:00",
"date_funded":"2020-03-11T00:00:00"
}
]
}
]
}
解决方案
由于您在内部和外部查询中使用相同的表别名，因此很难确定，但我认为您没有从内部到外部设置的相关性。除非它是子句中的不合格name列。u.name = name但我认为这可能存在问题，因为在评估内部查询期间还无法识别别名，所以它可能只是将其读取为相等u.name = u.name而不是过滤（这就是你的意思看到了，我猜。）
无论如何，是缺乏（也许是有意义的）相关性让你绊倒。这能行吗？但是您必须稍微尝试一下，这取决于您的数据以及哪些连接和关联在您的数据中真正有意义。
SELECT (
SELECT
d2.user_id
,u2.name AS name
,(
SELECT d.user_id
,s.status_name
,d.last_name
,d.loan_number
,d.request_date
,d.date_funded
FROM Draws d
LEFT JOIN Users u
ON d.user_id = u.id
LEFT JOIN DrawStatus s
ON s.id = d.status_id
WHERE u.name = name
AND d.user_id = d2.user_id --< Added. Plus added 2s to the outer aliases.
GROUP BY u.name
,d.assignee_id
,s.status_name
,d.last_name
,d.loan_number
,d.request_date
,d.date_funded
FOR JSON PATH
) Draws
FROM Draws d2
LEFT JOIN Users u2
ON d2.assignee_id = u2.id
GROUP BY u2.name
,d2.user_id
FOR JSON PATH
) Processor
FOR JSON PATH
,WITHOUT_ARRAY_WRAPPER
推荐阅读
- amazon-web-services - 快速查看 MLOps 监控
- javascript - 在列表中找到字典的最短方法是什么？
- react-native - 图像未在物理设备中以 react-native 显示
- python - 如何根据字符串列表启动 Python pandas 数据帧？
- android - Android - 特定视图或位置的上方/底部弹出窗口
- amazon-web-services - 每当我编辑解析器时，AppSync 控制台的更新速度非常慢
- angular - Jest 无法使用 ts-gaussian 导入在 Angular 包格式项目中运行测试？
- python - 使用 Pandas 进行分组时按条件选择值
- python - 如果值包含列表中的字符串，如何为 Python 数据框单元格着色
- series - IMDBPY - 如何从一集中获得系列？