Select distinct Table_Description From F_User_AAA ------------------------------------------------- AAA BBB CCCselect Count(*) from F_User_BBB('20','AAA') --------------------------------------------- 2
create table tb(id int,name varchar(10)) insert into tb select 1,'a' insert into tb select 2,'b' insert into tb select 3,'aa' insert into tb select 4,'bb' create function fun_tb(@name varchar(10)) returns table as return select * from tb where name like '%'+@name+'%'select * from dbo.fun_tb('a')1 a 3 aa估计是表值函数
create table tb(id int,sort int,sortName varchar(10)) insert into tb select 1,'20','AAA' insert into tb select 2,'20','BBB' insert into tb select 3,'20','AAA' insert into tb select 4,'20','CCC'goIF EXISTS(SELECT * FROM sysobjects WHERE name = 'fun_AAA' AND type = 'TF') drop function fun_AAA go create function fun_AAA(@sort int) returns @table table( Id int, sort int, sortname varchar(10) ) with encryption begin Insert into @table select * from tb where sort=@sort return end goselect distinct sortname from dbo.fun_AAA('20') create table tb1(id int,sortName varchar(10),field1 varchar(20)) insert into tb1 select 1,'AAA','aaaa' insert into tb1 select 2,'BBB','adsfasd' insert into tb1 select 3,'AAA','asdfasd' insert into tb1 select 4,'AAA','adsfasd'goIF EXISTS(SELECT * FROM sysobjects WHERE name = 'fun_BBB' AND type = 'TF') drop function fun_BBB go create function fun_BBB(@sortname varchar(10)) returns @table table( Id int, sortname varchar(10), field1 varchar(20) ) with encryption begin Insert into @table select * from tb1 where sortname=@sortname return end goselect count(*) from dbo.fun_BBB('AAA') select distinct table_A.sortName, ( select Count(*) from fun_BBB(table_A.sortName) ) from fun_AAA('20') as table_A----------------------- AAA 3 BBB 1 CCC 0
Select distinct Table_Description From F_User_AAA
-------------------------------------------------
AAA
BBB
CCCselect Count(*) from F_User_BBB('20','AAA')
---------------------------------------------
2
insert into tb select 1,'a'
insert into tb select 2,'b'
insert into tb select 3,'aa'
insert into tb select 4,'bb'
create function fun_tb(@name varchar(10))
returns table
as
return select * from tb where name like '%'+@name+'%'select * from dbo.fun_tb('a')1 a
3 aa估计是表值函数
insert into tb select 1,'20','AAA'
insert into tb select 2,'20','BBB'
insert into tb select 3,'20','AAA'
insert into tb select 4,'20','CCC'goIF EXISTS(SELECT *
FROM sysobjects
WHERE name = 'fun_AAA'
AND type = 'TF')
drop function fun_AAA
go
create function fun_AAA(@sort int)
returns @table table(
Id int,
sort int,
sortname varchar(10)
) with encryption
begin
Insert into @table
select * from tb where sort=@sort
return
end
goselect distinct sortname from dbo.fun_AAA('20')
create table tb1(id int,sortName varchar(10),field1 varchar(20))
insert into tb1 select 1,'AAA','aaaa'
insert into tb1 select 2,'BBB','adsfasd'
insert into tb1 select 3,'AAA','asdfasd'
insert into tb1 select 4,'AAA','adsfasd'goIF EXISTS(SELECT *
FROM sysobjects
WHERE name = 'fun_BBB'
AND type = 'TF')
drop function fun_BBB
go
create function fun_BBB(@sortname varchar(10))
returns @table table(
Id int,
sortname varchar(10),
field1 varchar(20)
) with encryption
begin
Insert into @table
select * from tb1 where sortname=@sortname
return
end
goselect count(*) from dbo.fun_BBB('AAA')
select distinct table_A.sortName,
(
select Count(*) from fun_BBB(table_A.sortName)
)
from fun_AAA('20') as table_A-----------------------
AAA 3
BBB 1
CCC 0