时间戳

首页 > 解决方案 > 计算列

sql - 计算列

问题描述

我有 2 个表（用户和报告）。用户存储一些用户信息，包括他们的 ID 报告存储系统中可用的报告列表，其中所有者字段显示哪个用户拥有报告。我有一个视图，其中包含用户表中的某些列。我想在视图中有一个额外的计算列，显示用户拥有多少报告。

类似于：SELECT owner, count(*) as ReportsOwned FROM Reports GROUP BY owner；

这是视图：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Users_SPO] AS
SELECT DisplayName AS Name,
       ManagedBy AS [Managed By],
       CanBeDeleted AS [Can Be Deleted],
       PrimarySmtpAddress AS Email,
       TeamsEnabled AS [Team Enabled],
       NumberOfChats AS Chats,
       NumberOfConversations AS Conversations,
       SPOActivity AS [SPO Activity],
       SPOStatus AS [SPO Status],
       Notes AS Description,
       WhenCreated AS [Creation Date],
       WhenChanged AS [Change Date],
       ExternalDirectoryObjectId
  FROM dbo.Users
GO

标签： sqltsql

解决方案

这是一个猜测，因为您没有定义两个表，也没有定义它们之间的任何链接。我希望你能在某个地方有一个领域。

CREATE VIEW [dbo].[Users_SPO] AS SELECT DisplayName AS Name, ManagedBy AS [Managed By], CanBeDeleted AS [Can Be Deleted], PrimarySmtpAddress AS Email, TeamsEnabled AS [Team Enabled], NumberOfChats AS Chats, NumberOfConversations AS Conversations, SPOActivity AS [SPO Activity], SPOStatus AS [SPO Status], Notes AS Description, WhenCreated AS [Creation Date], WhenChanged AS [Change Date], ExternalDirectoryObjectId, (SELECT count(*) FROM Reports rp where rp.owner = displayname) as ReportsOwned FROM dbo.Users GO

推荐阅读