SQL SERVER跨表计算列

       我们的表字段有时候要用到计算列，有时候计算列需要跨表计算，但是直接加入查询语句报错，我们可以使用函数来实现跨表计算列功能。

       测试数据：

--测试数据
if not object_id(N'user') is null
	drop table [user]
Go
Create table [user]([uid] int)
Insert [user]
select 1001 union all
select 1002 union all
select 1003 union all
select 1004 union all
select 1005
GO
if not object_id(N'adver') is null
	drop table adver
Go
Create table adver([aid] int,[uid] int)
Insert adver
select 101,1001 union all
select 102,1001 union all
select 103,1002 union all
select 104,1001 union all
select 105,1003 union all
select 106,1005 union all
select 107,1003 union all
select 108,1003 union all
select 109,1005 union all
select 110,1003 union all
select 111,1004 union all
select 112,1004
Go
--测试数据结束

       如果我们这样加计算列，会报错：

ALTER table [user]
add sumclo AS (SELECT COUNT(1) FROM dbo.adver WHERE dbo.adver.uid = dbo.[user].uid)

       我们可以换个角度，用函数实现：

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]

       这样我们就是实现了跨表的计算列。