时间戳

首页 > 解决方案 > 在表格显示中显示 SQL SELECT 行

sql-server - 在表格显示中显示 SQL SELECT 行

问题描述

我的数据库结构和示例数据：

CREATE TABLE [dbo].[users] (
    [user_id] [bigint] IDENTITY(1,1) NOT NULL,
    [user_name] [nvarchar](50) NULL,
    [first_name] [nvarchar](50) NULL,
    [last_name] [nvarchar](50) NULL,
    [id_number] [nvarchar](50) NULL,
    CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
    (
        [user_id] ASC
    )
)

insert into users (user_name, first_name, last_name, id_number)
select 'user1','John','Brown',7707071231
union all
select 'user2','Mary','Jane',7303034432
union all
select 'user3','Peter','Pan',5503024441


CREATE TABLE [dbo].[quiz_results] (
    [result_id] [bigint] IDENTITY(1,1) NOT NULL,
    [quiz_id] [bigint] NOT NULL,
    [user_id] [bigint] NOT NULL,
    [grade] [bigint] NULL,
    CONSTRAINT [PK_quizresults] PRIMARY KEY CLUSTERED 
    (
        [result_id] ASC
    )
)

insert into quiz_results (quiz_id, user_id, grade)
select 1,1,88
union all
select 2,1,84
union all
select 3,1,33
union all
select 1,2,65

此查询为我提供了 user_id = 1 的测验结果：

SELECT
    users.first_name + ' ' + users.last_name + ' (' + users.id_number + ')' AS student_name,
    quiz.quiz_name,
    quiz_results.grade
FROM quiz_results 
INNER JOIN quiz ON quiz_results.quiz_id = quiz.quiz_id
INNER JOIN users ON quiz_results.user_id = users.user_id
WHERE users.user_id = 12345

像这样：

+-------------------------+-----------+-------+
|      student_name       | quiz_name | grade |
+-------------------------+-----------+-------+
| John Brown (7707071231) |  quiz a   |  88   |
| John Brown (7707071231) |  quiz b   |  84   |
| John Brown (7707071231) |  quiz c   |  33   |
+-------------------------+-----------+-------+

但我不希望每行都显示 student_name。我想要这个输出：

+-------------------------+
| John Brown (7707071231) | 
+-------------------------+
|   quiz a    |     88    |
|   quiz b    |     84    |
|   quiz c    |     33    |
+-------------------------+

student_name 在第一行，然后是每个测验结果的一行 - 我特别希望 student_name 在第一行。

查询将永远只针对一个学生姓名。本质上，我想直接在 SQL 中生成一个“证书”。

将数据转换为该格式的最佳 SQL 方法是什么？CTE 或 STUFF() 命令会起作用吗？或者，还有更好的方法？

标签： sql-serversql-server-2016

解决方案

这根本无法在 SQL 中完成。
SQL 只能返回标量值或结果集（表格数据），而结果集不支持“列跨度”——所以唯一的方法是在表示层中——但是你可以在 SQL Server 中做一些事情来制作你在表示层的工作更轻松。

一种选择是创建一个存储过程，它将学生姓名作为输出参数返回，测验成绩作为结果集：

CREATE PROCEDURE GetQuizResultByUserId
(
    @UserId int,
    @UserName nvarchar(154) OUTPUT 
)
AS

-- it's 154 because    50     +  1  +     50    +  2   +     50     + 1
SELECT @UserName = first_name + ' ' + last_name + ' (' + id_number + ')'
FROM users
WHERE user_id = @UserId


SELECT
quiz.quiz_name,
quiz_results.grade
FROM quiz_results 
INNER JOIN quiz ON quiz_results.quiz_id = quiz.quiz_id
WHERE quiz_results.user_id = @UserId

GO

由于这是 2016 版本，另一种选择是使用以下For Json子句将结果作为 Json 返回：

SELECT first_name + ' ' + last_name + ' (' + id_number + ')' As UserName,
       (
          SELECT quiz.quiz_name,
                 quiz_results.grade
          FROM quiz_results 
          INNER JOIN quiz ON quiz_results.quiz_id = quiz.quiz_id
          WHERE quiz_results.user_id = @UserId
          FOR JSON AUTO
       ) As quizResult
FROM users
WHERE user_id = @UserId
FOR JSON AUTO

结果是以下json：

[
  {
    "UserName": "John Brown (7707071231)",
    "quizResult": [
      {
        "quiz_name": "quiz a",
        "grade": 88
      },
      {
        "quiz_name": "quiz b",
        "grade": 84
      },
      {
        "quiz_name": "quiz c",
        "grade": 33
      }
    ]
  }
]

推荐阅读