时间戳

首页 > 解决方案 > 如何通过连接从两个表中进行 SQL 选择并获取一个 json 对象

c# - 如何通过连接从两个表中进行 SQL 选择并获取一个 json 对象

问题描述

请我正在做一个项目，我有点陷入困境。

我的 SELECT 就是这么简单：

SELECT TOP 1 
  co.[contactid] AS [c.id], 
  co.[firstname] + ' ' + co.[lastname] AS [c.fullname], 
  f.[name] AS [p.name], 
  f.[username] AS [p.username], 
  f.[date] AS [p.startDate] 
FROM [dbo].[table1] co 
JOIN [dbo].[table2] f ON co.[contactid] = f.[contact_id] 
WHERE co.[lastname] LIKE 'last%' 
FOR JSON AUTO

我得到：

[{
"c.id": "1",
"c.fullname": "firstname lastname",
"f": [
  {
    "p.name": "name1",
    "p.username": "Username1",
    "p.startDate": "2015-06-15"
  }
]}]

我希望结果是：

[{
  "c.id": "1",
  "c.fullname": "firstname lastname",
  "p.name": "name1",
  "p.username": "Username1",
  "p.startDate": "2015-06-15"
}]

标签： c#sqlsql-server

解决方案

您可以使用以下代码：

SELECT *
FROM    
    (SELECT TOP 1 
         co.[contactid]  AS [c.id],
         co.[firstname] + ' ' + co.[lastname] AS [c.fullname],
         f.[name]        AS [p.name],
         f.[username]    AS [p.username],
         f.[date]        AS [p.startDate]
     FROM   
         [dbo].[table1] co
     JOIN 
         [dbo].[table2] f ON co.[contactid] = f.[contact_id]
     WHERE  
         co.[lastname] LIKE 'last%') a           
FOR JSON AUTO

推荐阅读