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

我真的需要一个简单求和的表上的计算列.

请看下面：

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
Subqueries are not allowed in this context. Only scalar expressions are allowed.

请告知我该怎么办.

非常感谢

第2部分

我创造了一个功能;但是,我得到空值,请指教.

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]

解决方法

您可以将查询包装到这样的函数中(它可以返回一个值)：

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

并使用此函数代替calc字段 – 如下所示：

alter table ManagerTaLog add WeeklyBudget as dbo.SumIt(Key1)

注意

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

select * from ManagerTaLog

您应该以这样的方式更改函数,即接受NOT varchar值,但接受NVARCHAR(255) – 与Manager列相同的类型.尝试一下.

原文链接：https://www.f2er.com/mssql/84003.html

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

解决方法

猜你在找的MsSQL相关文章