关于给现有表添加自动计算数据列的问题......

[Wangwang21333]

现有两个表，一个叫user，一个叫adver。是一对多的关系，一个user有好多adver，然后我想在user里加一个列叫做adver的总数（自动计算），可以怎么实现这个功能呢？
（自己尝试过 alter table xx add xxx as 0+(select count(*) from aa where id =aa.id) 表示不能用 .. .. ... .... ）
具体测试数据如下：
user表（uid是主键）：
uid
1001
1002
1003
1004
1005

adver表（aid是主键，uid是外键）：
aid uid
101 1001
102 1001
103 1002
104 1001
105 1003
106 1005
107 1003
108 1003
109 1005
110 1003
111 1004
112 1004

最后想实现在user加入一列自动匹配计算adver中的相应行数（sumclo），得到新的user表如下：
uid sumclo
1001 3
1002 1
1003 4
1004 2
1005 2

[二月十六]

新建函数：

CREATE FUNCTION dbo.Get_RowCount (@uid INT)
RETURNS INT
AS
BEGIN
    DECLARE @rowcount INT;
    SELECT @rowcount = COUNT(1)
    FROM adver
    WHERE adver.uid = @uid;
    RETURN @rowcount;
END;
GO

添加计算列：

ALTER table [user]
add sumclo AS dbo.Get_RowCount(uid)

查看结果：

Select * from [user]

[卖水果的net]

计算列不支持跨表。 可以考虑用 触发器实现。

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('view_user') IS NOT NULL DROP VIEW view_user
GO
IF OBJECT_ID('dbo.[user]') IS NOT NULL 
	DROP TABLE dbo.[user]
GO
CREATE TABLE dbo.[user](
[uid] INT PRIMARY KEY
)
GO
IF OBJECT_ID('dbo.[adver]') IS NOT NULL 
	DROP TABLE dbo.[adver]
GO
CREATE TABLE dbo.[adver](
[aid] INT PRIMARY KEY
,[uid] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[user] VALUES(N'1001')
INSERT INTO dbo.[user] VALUES(N'1002')
INSERT INTO dbo.[user] VALUES(N'1003')
INSERT INTO dbo.[user] VALUES(N'1004')
INSERT INTO dbo.[user] VALUES(N'1005')
SET NOCOUNT ON
INSERT INTO dbo.[adver] VALUES(N'101',N'1001')
INSERT INTO dbo.[adver] VALUES(N'102',N'1001')
INSERT INTO dbo.[adver] VALUES(N'103',N'1002')
INSERT INTO dbo.[adver] VALUES(N'104',N'1001')
INSERT INTO dbo.[adver] VALUES(N'105',N'1003')
INSERT INTO dbo.[adver] VALUES(N'106',N'1005')
INSERT INTO dbo.[adver] VALUES(N'107',N'1003')
INSERT INTO dbo.[adver] VALUES(N'108',N'1003')
INSERT INTO dbo.[adver] VALUES(N'109',N'1005')
INSERT INTO dbo.[adver] VALUES(N'110',N'1003')
INSERT INTO dbo.[adver] VALUES(N'111',N'1004')
INSERT INTO dbo.[adver] VALUES(N'112',N'1004')
GO

IF OBJECT_ID('view_user') IS NOT NULL DROP VIEW view_user
GO
CREATE VIEW view_user
WITH SCHEMABINDING
AS
SELECT a.[uid],COUNT_BIG(*) AS sumclo
FROM dbo.adver AS a
GROUP BY a.[uid]
GO
CREATE UNIQUE CLUSTERED INDEX ix_view_user ON view_user(uid)
GO

--索引视图的好处是它本身相当于一张表，查询时不需要重新统计，效率高
--如果 user 表 在 adver 中一定有记录（1对1或多，而不是1对0或多），
--可以把 user 表放到索引视图中，就更方便了。
SELECT * FROM dbo.[user] AS a LEFT JOIN view_user AS b ON a.uid=b.uid

[Wangwang21333]

引用 2 楼 卖水果的net 的回复:

计算列不支持跨表。 可以考虑用 触发器实现。

嗷嗷！！是这样呀！！真的很感谢呀！！！！

[Wangwang21333]

引用 1 楼 二月十六 的回复:

新建函数：

CREATE FUNCTION dbo.Get_RowCount (@uid INT)
RETURNS INT
AS
BEGIN
    DECLARE @rowcount INT;
    SELECT @rowcount = COUNT(1)
    FROM adver
    WHERE adver.uid = @uid;
    RETURN @rowcount;
END;
GO

添加计算列：

ALTER table [user]
add sumclo AS dbo.Get_RowCount(uid)

查看结果：

Select * from [user]

感谢！！！

[Wangwang21333]

引用 3 楼 吉普赛的歌 的回复:

USE tempdb
GO
IF OBJECT_ID('view_user') IS NOT NULL DROP VIEW view_user
GO
IF OBJECT_ID('dbo.[user]') IS NOT NULL 
	DROP TABLE dbo.[user]
GO
CREATE TABLE dbo.[user](
[uid] INT PRIMARY KEY
)
GO
IF OBJECT_ID('dbo.[adver]') IS NOT NULL 
	DROP TABLE dbo.[adver]
GO
CREATE TABLE dbo.[adver](
[aid] INT PRIMARY KEY
,[uid] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[user] VALUES(N'1001')
INSERT INTO dbo.[user] VALUES(N'1002')
INSERT INTO dbo.[user] VALUES(N'1003')
INSERT INTO dbo.[user] VALUES(N'1004')
INSERT INTO dbo.[user] VALUES(N'1005')
SET NOCOUNT ON
INSERT INTO dbo.[adver] VALUES(N'101',N'1001')
INSERT INTO dbo.[adver] VALUES(N'102',N'1001')
INSERT INTO dbo.[adver] VALUES(N'103',N'1002')
INSERT INTO dbo.[adver] VALUES(N'104',N'1001')
INSERT INTO dbo.[adver] VALUES(N'105',N'1003')
INSERT INTO dbo.[adver] VALUES(N'106',N'1005')
INSERT INTO dbo.[adver] VALUES(N'107',N'1003')
INSERT INTO dbo.[adver] VALUES(N'108',N'1003')
INSERT INTO dbo.[adver] VALUES(N'109',N'1005')
INSERT INTO dbo.[adver] VALUES(N'110',N'1003')
INSERT INTO dbo.[adver] VALUES(N'111',N'1004')
INSERT INTO dbo.[adver] VALUES(N'112',N'1004')
GO

IF OBJECT_ID('view_user') IS NOT NULL DROP VIEW view_user
GO
CREATE VIEW view_user
WITH SCHEMABINDING
AS
SELECT a.[uid],COUNT_BIG(*) AS sumclo
FROM dbo.adver AS a
GROUP BY a.[uid]
GO
CREATE UNIQUE CLUSTERED INDEX ix_view_user ON view_user(uid)
GO

--索引视图的好处是它本身相当于一张表，查询时不需要重新统计，效率高
--如果 user 表 在 adver 中一定有记录（1对1或多，而不是1对0或多），
--可以把 user 表放到索引视图中，就更方便了。
SELECT * FROM dbo.[user] AS a LEFT JOIN view_user AS b ON a.uid=b.uid

感谢！！！