logo标签列表

如何在SQL Server 2008表中创建计算列

sqlsql-servert-sql
5

我需要在一个表格上添加一个简单求和的计算列。

请参考下面的内容：

SELECT   key3
         ,SUM(UTOTALWBUD)
FROM     CONTACT1
         INNER JOIN CONTACT2
            ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
WHERE    KEY1 = 'Client'
GROUP BY KEY3

我尝试通过添加以下内容创建计算列

ALTER TABLE ManagerTaLog
ADD         WeeklyBudget as (   SELECT
                                        key3
                                        ,SUM(UTOTALWBUD)
                            FROM        CONTACT1
                                        JOIN CONTACT2
                                            ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
                            WHERE       KEY1 = 'Client'
                            GROUP BY    KEY3)

我收到了如下错误信息：

Msg 1046, Level 15, State 1, Line 4
子查询在此上下文中不允许。只允许使用标量表达式。

请告诉我应该怎么处理。
非常感谢。
第二部分：
我创建了一个函数，但是返回空值，请指教。
CREATE FUNCTION [dbo].[SumIt](@Key3 varchar)
RETURNS TABLE 
AS
RETURN
(
SELECT      SUM(UTOTALWBUD)
FROM        CONTACT1
            JOIN CONTACT2
                ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
            JOIN Phone_List
                ON CONTACT1.KEY3 = Phone_List.[Manager ]
WHERE       KEY1 = 'Client'
            AND Phone_List.[Manager ] = @Key3
GROUP BY [Manager ]

)
END

GO

只需选择返回我希望添加到Phone_list表中的值的语句。
SELECT      [Manager ]
            ,SUM(UTOTALWBUD)
FROM        CONTACT1
            JOIN CONTACT2
                ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
            JOIN Phone_List
                ON CONTACT1.KEY3 = Phone_List.[Manager ]
WHERE       KEY1 = 'Client'
GROUP BY    [Manager ]

表定义

CREATE TABLE [dbo].[CONTACT1](
    [ACCOUNTNO] [varchar](20) NOT NULL,
    [COMPANY] [varchar](40) NULL,
    [CONTACT] [varchar](40) NULL,
    [LASTNAME] [varchar](15) NULL,
    [DEPARTMENT] [varchar](35) NULL,
    [TITLE] [varchar](35) NULL,
    [SECR] [varchar](20) NULL,
    [PHONE1] [varchar](25) NOT NULL,
    [PHONE2] [varchar](25) NULL,
    [PHONE3] [varchar](25) NULL,
    [FAX] [varchar](25) NULL,
    [EXT1] [varchar](6) NULL,
    [EXT2] [varchar](6) NULL,
    [EXT3] [varchar](6) NULL,
    [EXT4] [varchar](6) NULL,
    [ADDRESS1] [varchar](40) NULL,
    [ADDRESS2] [varchar](40) NULL,
    [ADDRESS3] [varchar](40) NULL,
    [CITY] [varchar](30) NULL,
    [STATE] [varchar](20) NULL,
    [ZIP] [varchar](10) NOT NULL,
    [COUNTRY] [varchar](20) NULL,
    [DEAR] [varchar](20) NULL,
    [SOURCE] [varchar](20) NULL,
    [KEY1] [varchar](20) NULL,
    [KEY2] [varchar](20) NULL,
    [KEY3] [varchar](20) NULL,
    [KEY4] [varchar](20) NULL,
    [KEY5] [varchar](20) NULL,
    [STATUS] [varchar](3) NOT NULL,
    [NOTES] [text] NULL,
    [MERGECODES] [varchar](20) NULL,
    [CREATEBY] [varchar](8) NULL,
    [CREATEON] [datetime] NULL,
    [CREATEAT] [varchar](5) NULL,
    [OWNER] [varchar](8) NOT NULL,
    [LASTUSER] [varchar](8) NULL,
    [LASTDATE] [datetime] NULL,
    [LASTTIME] [varchar](5) NULL,
    [U_COMPANY] [varchar](40) NOT NULL,
    [U_CONTACT] [varchar](40) NOT NULL,
    [U_LASTNAME] [varchar](15) NOT NULL,
    [U_CITY] [varchar](30) NOT NULL,
    [U_STATE] [varchar](20) NOT NULL,
    [U_COUNTRY] [varchar](20) NOT NULL,
    [U_KEY1] [varchar](20) NOT NULL,
    [U_KEY2] [varchar](20) NOT NULL,
    [U_KEY3] [varchar](20) NOT NULL,
    [U_KEY4] [varchar](20) NOT NULL,
    [U_KEY5] [varchar](20) NOT NULL,
    [recid] [varchar](15) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



CREATE TABLE [dbo].[Phone_List](
    [Manager ] [nvarchar](255) NULL,
    [SalesCode] [nvarchar](255) NULL,
    [Email] [nvarchar](255) NULL,
    [PayrollCode] [nvarchar](255) NULL,
    [Mobile] [nvarchar](255) NULL,
    [FName] [nchar](20) NULL,
    [idd] [tinyint] NULL,
    [OD] [varchar](20) NULL,
    [WeeklyBudget]  AS ([dbo].[SumIt]([manager]))
) ON [PRIMARY]
2
你的子查询返回了两个值 - 这将如何存储在单个计算列中？！？！？ - marc_s
请展示表的定义。您确定Key3有一个值吗？ - Lieven Keersmaekers
1
不确定是否相关，但是你调用函数时使用的是([dbo].[SumIt]([manager]))而不是([dbo].[SumIt]([manager ]))（注意多余的空格）。 - Lieven Keersmaekers
6个回答
8
您可以像这样将查询包装到函数中（它必须返回一个值）： 

CREATE FUNCTION dbo.SumIt(@Key1 varchar(max))
returns float
as
begin
  return (select sum(UTOTALWBUD) from
   CONTACT1 inner join
   CONTACT2 on
   CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO
   where KEY1=@key1
   group by KEY3)
END

请改用此函数并在计算字段中使用，类似于这样：
alter table ManagerTaLog add WeeklyBudget as dbo.SumIt(Key1)

注意

对于这样的查询，它将成为性能杀手：

select * from ManagerTaLog

你应该修改函数，使其接受 NVARCHAR(255) 类型的值，而不是 不 接受 varchar 值，与 Manager 列相同。请尝试。
1

你不能使用返回多个值的子查询作为计算列表达式。

你可以使用返回单个值的表达式，或者将代码封装在存储函数中，或者创建一个视图（带有JOIN或子查询），将这个逻辑组合成可用的内容。

1

在我看来，这是一种错误的方法。你必须在联系人表上使用触发器，并在这些触发器中更新每周预算。

1
如果解决返回两个值的问题，一个解决方案是实现一个函数来进行计算并将其用于计算列。
一些考虑：
  • 我假设需要传递给函数的是 Key3，并添加了一个where子句以返回给定 Key3 值的每周预算。
  • 当您添加计算列时，该函数将针对结果集中每个记录执行。
脚本 
CREATE FUNCTION dbo.fn_ManagerTaLogWeeklyBudget(@Key3 INTEGER) RETURNS INTEGER AS 
BEGIN
  select sum(UTOTALWBUD) from
  CONTACT1 inner join
  CONTACT2 on
  CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO
  where KEY1='Client'
        AND KEY3 = @Key3
  group by KEY3)

END
GO

ALTER TABLE dbo.ManagerTaLog
ADD WeeklyBudget AS dbo.fn_ManagerTaLogWeeklyBudget(Key3)
感谢您的帖子。我已经创建了函数并修改了表格，然而结果却给了我空值。 - BI Dude
@user995506 - 你的问题上我20小时前发表的评论怎么样了？ - Lieven Keersmaekers
0
CREATE FUNCTION [dbo].[SumIt](@Key3 varchar)
RETURNS TABLE 
AS
RETURN
(
SELECT      SUM(ISNULL(UTOTALWBUD,0))
FROM        CONTACT1
            JOIN CONTACT2
                ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
            JOIN Phone_List
                ON CONTACT1.KEY3 = Phone_List.[Manager ]
WHERE       KEY1 = 'Client'
            AND Phone_List.[Manager ] = @Key3
GROUP BY [Manager ]

)
END

GO

希望这可以帮助到您。请注意，这不是一个完整的解决方案，但是它可能会帮助您特定的情况。由于函数返回多个值，您可以使用表值函数或以一种将仅返回一个值的方式修改查询。但是，如果列可为空，请不要忘记添加ISNULL。

0

你可以在表格中拥有计算列，但这将在表格的所有行中呈现（并计算）。

你在选择中尝试做的事情被称为聚合。尝试这样：

select key3, sum(UTOTALWBUD) as WeeklyBudget from
CONTACT1 inner join CONTACT2 on CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO
where KEY1='Client' 
group by key3
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接