作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我无法使它正常工作。我有4个表格:“产品”,“供应商”,“X_Product_Suppliers”和“注释”。我想全部查询它们,并使用以下查询将它们放入JSON:
WITH Products (Id, Name, Price) As (
SELECT 1, 'First Product', 10
), Suppliers (Id, Name) As (
SELECT 1, 'Factory1' UNION ALL
SELECT 2, 'Factory2'
), Comments (Id, [Text], ProductId) As (
SELECT 1, 'Good Product', 1 UNION ALL
SELECT 2, 'Fantastic!' , 1
), X_Product_Supplier (ProductId, SupplierId) As (
SELECT 1, 1 UNION ALL
SELECT 1, 2
)
SELECT Products.*, Suppliers.*, Comments.* FROM Products
LEFT OUTER JOIN X_Product_Supplier ON X_Product_Supplier.ProductId = Products.Id
LEFT OUTER JOIN Suppliers ON X_Product_Supplier.SupplierId = Suppliers.Id
LEFT OUTER JOIN Comments ON Comments.ProductId = Products.Id
FOR JSON AUTO
{
"Id":1,
"Name":"First Product",
"Price":"10",
"Suppliers":[
{
"Id":1,
"Name":"Factory1",
"Comments":[ //THIS SHOULD BE UNDER PRODUCT, NOT SUPPLIER
{
"Id":1,
"Text":"Good Product",
"ProductId":1
},
{
"Id":2,
"Text":"Fantastic!",
"ProductId":1
}
]
},
{
"Id":2,
"Name":"Factory2",
"Comments":[ //THIS IS NOW DUPLICATE
{
"Id":1,
"Text":"Good Product",
"ProductId":1
},
{
"Id":2,
"Text":"Fantastic!",
"ProductId":1
}
]
}
]
}
{
"Id":1,
"Name":"First Product",
"Price":"10",
"Suppliers":[
{
"Id":1,
"Name":"Factory1"
},
{
"Id":2,
"Name":"Factory2"
}
],
"Comments":[
{
"Id":1,
"Text":"Good Product",
"ProductId":1
},
{
"Id":2,
"Text":"Fantastic!",
"ProductId":1
}
]
}
最佳答案
如果有人在寻找答案,这是我写的简单查询。根据您的架构更改查询,它应提供适当的结构化结果。
SELECT Products.*,
(SELECT Suppliers.*
FROM Suppliers
WHERE Suppliers.Id = Products.SuppliersId
FOR JSON AUTO) As Suppliers,
(SELECT Comments.*
FROM Comments
WHERE Comments.ProductId = Products.Id
FOR JSON AUTO) As Comments
FROM Products
FOR JSON AUTO
关于sql-server - 我该如何通过2个连接来进行 "FOR JSON",我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43094351/
我是一名优秀的程序员,十分优秀!