SQL Server:如何查询作为Json对象的所有行到其他列旁边的数据? [英] SQL Server: How to query data all rows as Json object into next to other columns?

问题描述

我有这样的数据:

我想这样查询结果:

I want to query result like this:

这是我的代码

SELECT
     PML_CODE
    ,PML_NAME_ENG
    ,(
        SELECT
              PML_ID
             ,PML_NO
             ,PML_CODE
             ,PML_NAME_ENG
             ,PML_FORMULA
        FROM DSP.PARAMET_LIST AS A WITH(NOLOCK)
        WHERE A.PML_ID = B.PML_ID 
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS BR_OBJECT
FROM DSP.PARAMET_LIST AS B WITH(NOLOCK)

我的代码可以满足我的需求，但是我想知道是否有更好，更快的方式编写此查询?

My code works for what I want, but I want to know if there is a better, faster way to write this query?

推荐答案

下次，请不要发布图片，而应尝试创建一些DDL，并在其中填充示例数据并说明您自己的尝试和预期的输出.这使我们更容易理解和回答您的问题.

Next time please do not post pictures, but rather try to create some DDL, fill it with sample data and state your own attempts and the expected output. This makes it easier for us to understand and to answer your issue.

您可以这样尝试:

DECLARE @tbl TABLE(PML_ID BIGINT, PML_NO INT, PML_CODE VARCHAR(10), PML_NAME_ENG VARCHAR(10), PML_FORMULA VARCHAR(10));
INSERT INTO @tbl VALUES
 (2017102600050,1,'KHR','Riel','01')
,(2017102600051,2,'USD','Dollar','02')
,(2017102600052,3,'THB','Bath','05')

SELECT
     PML_CODE
    ,PML_NAME_ENG
    ,BR_OBJECT
FROM @tbl
CROSS APPLY(
    SELECT
    (
            SELECT
                  PML_ID
                 ,PML_NO
                 ,PML_CODE
                 ,PML_NAME_ENG
                 ,PML_FORMULA
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )) AS A(BR_OBJECT);

与您自己的方法的最大区别是，我使用已经使用的列作为CROSS APPLY，而不是调用相关子查询.

The big difference to your own approach is that I use a CROSS APPLY using the columns we have already instead of calling a correlated sub-query.

这篇关于SQL Server:如何查询作为Json对象的所有行到其他列旁边的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！