以下是小编帮大家整理的crossapply和表值函数进行关联查询，本文共4篇，欢迎大家收藏分享。
篇1：crossapply和表值函数进行关联查询
目前场景是有一个兴趣爱好类型表和一个员工爱好表，员工爱好表中的爱好一列，如果这个员工有多个爱好，那么就将这些爱好放到一条记录当中，爱好之间用逗号隔开，现在需求是要将每个员工的这些爱好分隔成一条条记录查询出来，从而可以例如做员工爱好统计等应用，
爱好类型表 t_a
员工爱好表 t_b
需要的结果是
创建一个分隔字符串的表值函数
create function [dbo].[splitIDs]( @Ids nvarchar(1000), @split_char nvarchar(10))returns @t_id TABLE (id bigint)asbegin declare @i int,@j int,@l int,@v bigint; set @i = 0; set @j = 0; set @l = len(@Ids); while(@j < @l) begin set @j = charindex(@split_char,@Ids,@i+1); if(@j = 0) set @j = @l+1; set @v = cast(SUBSTRING(@Ids,@i+1,@j-@i-1) as bigint); INSERT INTO @t_id VALUES(@v) set @i = @j; end return;endGO采用下面的语句即可得到期望的结果
select aid = t_a.id ,aname = t_a.name ,bid = t_b.id,t_b.namefrom t_bcross apply splitIDs(a_ids,',') tbl_IdsINNER JOIN t_a ON tbl_Ids .id = t_a.id
篇2：用SQL进行函数查询数据库教程
Oracle 9i提供了很多函数可以用来辅助数据查询，
用SQL进行函数查询数据库教程
。接下来我们介绍常用的函数功能及使用方法。4.5.1 【ceil】函数
在【命令编辑区】输入“select mgr, mgr/100,ceil(mgr/100) from scott.emp;”，然后单击【执行】按钮，出现如图4.29所示的结果。
【参见光盘文件】：\第4章\4.5\451.sql。
【ceil】函数用法：ceil（n），取大于扔谑值n的最小整数。
4.5.2 【floor】函数
在【命令编辑区】输入“select mgr, mgr/100,floor(mgr/100) from scott.emp;”，然后单击【执行】按钮，出现如图4.30所示的结果。
【参见光盘文件】：\第4章\4.5\452.sql。
【floor】函数用法：floor（n），取小于等于数值n的最大整数。
4.5.3 【mod】函数
在【命令编辑区】输入“select mgr, mod(mgr,1000), mod(mgr,100), mod(mgr,10) from scott.emp;”，然后单击【执行】按钮，出现如图4.31所示的结果。
【参见光盘文件】：\第4章\4.5\453.sql。
【mod】函数用法：mod（m,n），取m整除n后的余数。
4.5.4 【power】函数
在【命令编辑区】输入“select mgr, power(mgr,2),power(mgr,3) from scott.emp;”，然后单击【执行】按钮，出现如图4.32所示的结果。
【参见光盘文件】：\第4章\4.5\454.sql。
【power】函数用法：power（m,n），取m的n次方。
4.5.5 【round】函数
在【命令编辑区】输入“select mgr, round(mgr/100,2),round(mgr/1000,2) from scott.emp;”，然后单击【执行】按钮，出现如图4.33所示的结果。
【参见光盘文件】：\第4章\4.5\455.sql。
【round】函数用法：round（m,n），四舍五入，保留n位。
4.5.6 【sign】函数
在【命令编辑区】输入“select mgr, mgr-7800,sign(mgr-7800) from scott.emp;”，然后单击【执行】按钮，出现如图4.34所示的结果，
【参见光盘文件】：\第4章\4.5\456.sql。
【sign】函数用法：sign（n）。n>0，取1；n=0，取0；n<0，取-1。
4.5.7 【avg】函数
在【命令编辑区】输入“select avg(mgr)平均薪水 from scott.emp;”，然后单击【执行】按钮，出现如图4.35所示的结果。
【参见光盘文件】：\第4章\4.5\457.sql。
【avg】函数用法：avg（字段名），求平均值。要求字段为数值型。
4.5.8 【count】函数
（1）在【命令编辑区】输入“select count(*) 记录总数 from scott.emp;”，然后单击【执行】按钮，出现如图4.36所示的结果。
【参见光盘文件】：\第4章\4.5\458-1.sql。
（2）在【命令编辑区】输入“select count(distinct job ) 工作类别总数 from scott.emp;”，然后单击【执行】按钮，出现如图4.37所示的结果。
【参见光盘文件】：\第4章\4.5\458-2.sql。
【count】函数用法：count（字段名）或count（*），统计总数。
4.5.9 【min】函数
在【命令编辑区】输入“select min(sal) 最少薪水 from scott.emp;”，然后单击【执行】按钮，出现如图4.38所示的结果。
【参见光盘文件】：\第4章\4.5\459.sql。
【min】函数用法：min（字段名），计算数值型字段最小数。
4.5.10 【max】函数
在【命令编辑区】输入“select max(sal) 最高薪水 from scott.emp;”，然后单击【执行】按钮，出现如图4.39所示的结果。
【参见光盘文件】：\第4章\4.5\4510.sql。
【max】函数用法：max（字段名），计算数值型字段最大数。
4.5.11 【sum】函数
在【命令编辑区】输入“select sum(sal) 薪水总和 from scott.emp;”，然后单击【执行】按钮，出现如图4.40所示的结果。
【参见光盘文件】：\第4章\4.5\4511.sql。
【sum】函数用法：sum（字段名），计算数值型字段总和。
通过上面4类查询实例的学习，读者可以举一反三，灵活运用。用SQL进行数据的查询就介绍到这里，下面学习如何录入数据。
篇3：用SQL进行单表查询数据库教程
单表查询是相对多表查询而言的，指从一个数据表中查询数据，
用SQL进行单表查询数据库教程
。4.2.1 查询所有的记录
在【命令编辑区】执行输入“select * from scott.emp”，然后单击【执行】按钮，出现如图4.3所示的emp数据表所有记录。
【参见光盘文件】：\第4章\4.2\421.sql。
select * from 数据表，这里的“*”代表数据表中所有的字段。
4.2.2 查询所有记录的某些字段
在【命令编辑区】输入“select empno,ename,job from scott.emp”，然后单击【执行】按钮，将显示emp数据表的empno、ename和job字段，如图4.4所示。
【参见光盘文件】：\第4章\4.2\422.sql。
select 字段名1, 字段名2,…… from 数据表，将显示某些特定的字段，注意这里的字段名之间的逗号是英文状态下的逗号。
4.2.3 查询某些字段不同记录
在图4.4所示的job字段中，可以发现有相同的数据，为了查询有多少种不同的job，在【命令编辑区】输入“select distinct job from scott.emp”，然后单击【执行】按钮，出现如图4.5所示的结果。
【参见光盘文件】：\第4章\4.2\423.sql。
select distinct 字段名 from 数据表，这里的“distinct”保留字指在显示时去除相同的记录，与之对应的是“all”将保留相同的记录，默认为“all”。
4.2.4 单条件的查询
（1）在【命令编辑区】输入“select empno,ename,job from scott.emp where job=’MANAGER’”，然后单击【执行】按钮，出现如图4.6所示的字符型字段条件查询的结果，查询的是job为MANAGER的记录。
【参见光盘文件】：\第4章\4.2\424-1.sql。
（2）在【命令编辑区】输入“select empno,ename,sal from scott.emp where sal<=2500”，然后单击【执行】按钮，出现如图4.7所示的数字型字段条件查询的结果，查询的是满足sal小于等于2500的记录。
【参见光盘文件】：\第4章\4.2\424-2.sql。
where可以指定查询条件，如果是指定字符型字段查询条件，形式为字段名 运算符 '字符串'；如果是指定数字型字段查询条件，形式为字段名 运算符 '字符串'。 单条件查询使用的比较运算符如表4.1所示。
【参见光盘文件】：\第4章\4.2\table41.sql。
表4.1 比较运算符名称实例=(等于)select * from scott.emp where job=’MANAGER’;select * from scott.emp where sal=1100;!= (不等于)select * from scott.emp where job!=’MANAGER’;select * from scott.emp where sal!=1100;^=(不等于)select * from scott.emp where job^=’MANAGER’;select * from scott.emp where sal^=1100;(不等于)select * from scott.emp where job’MANAGER’;select * from scott.emp where sal1100;<(小于)select * from scott.emp where sal<;select * from scott.emp where job<’MANAGER’;>(大于)select * from scott.emp where sal>2000;select * from scott.emp where job>’MANAGER’;<=(小于等于)select * from scott.emp where sal<＝2000;select * from scott.emp where job<＝’MANAGER’;>=(大于等于)select * from scott.emp where sal>=2000;select * from scott.emp where job>=’MANAGER’;in(列表)select * from scott.emp where sal in (2000,1000,3000);select * from scott.emp where job in (’MANAGER’,’CLERK’);not in(不在列表)select * from scott.emp where sal not in (2000,1000,3000);select * from scott.emp where job not in (’MANAGER’,’CLERK’);between(介于之间)select * from scott.emp where sal between 2000 and 3000;select * from scott.emp where job between ’MANAGER’ and ’CLERK’;not between (不介于之间)select * from scott.emp where sal not between 2000 and 3000;select * from scott.emp where job not between ’MANAGER’ and ’CLERK’;like(模式匹配)select * from scott.emp where job like ’M%’;select * from scott.emp where job like ’M__’;not like (模式不匹配)select * from scott.emp where job not like ’M%’;select * from scott.emp where job not like ’M__’;Is null (是否为空)select * from scott.emp where sal is null;select * from scott.emp where job is null;is not null(是否为空)select * from scott.emp where sal is not null;select * from scott.emp where job is not null;
like和not like适合字符型字段的查询，%代表任意长度的字符串，_下划线代表一个任意的字符，
like ‘m%’ 代表m开头的任意长度的字符串，like ‘m__’ 代表m开头的长度为3的字符串。
4.2.5 组合条件的查询
（1）在【命令编辑区】输入“select empno,ename,job from scott.emp where job>=’CLERK’ and sal<=2000”，然后单击【执行】按钮，出现如图4.8所示的逻辑与组合查询的结果。
【参见光盘文件】：\第4章\4.2\425-1.sql。
（2）在【命令编辑区】输入“select empno,ename,job from scott.emp where job>=’CLERK’ or sal<=2000”，然后单击【执行】按钮，出现如图4.9所示的逻辑或组合查询的结果。
【参见光盘文件】：\第4章\4.2\425-2.sql。
（3）在【命令编辑区】输入“select empno,ename,job from scott.emp where not job=’CLERK’”，然后单击【执行】按钮，出现如图4.10所示的逻辑非组合查询的结果。
【参见光盘文件】：\第4章\4.2\425-3.sql。
“not job=’CLERK’”等价于“job’CLERK’”。
组合条件中使用的逻辑比较符如表4.2所示。
【参见光盘文件】：\第4章\4.2\table42.sql。
表4.2 逻辑比较符
名称实例and(与)select * from scott.emp where job=’MANAGER’ and sal2000;or (或)select * from scott.emp where job!=’MANAGER’ or sal2000;not(非)select * from scott.emp where not job>=’MANAGER’;4.2.6 排序查询
在【命令编辑区】输入“select empno,ename,job from scott.emp where job<=’CLERK’ order by job asc,sal desc”，然后单击【执行】按钮，出现如图4.11所示的排序查询的结果。
【参见光盘文件】：\第4章\4.2\426.sql。
order by 可以指定查询结果如何排序，形式为字段名 排序关键词；asc代表升序排列，desc代表降序排列，多个排序字段之间通过逗号分割。若有where查询条件，order by要放在where语句后面。
4.2.7 分组查询
分组查询是指将查询结果按照字段分组。
（1）在【命令编辑区】输入“select empno,ename,job,sal from scott.emp group by job,empno,ename,sal having sal<=2000”，然后单击【执行】按钮，出现如图4.12所示的分组查询的结果。
【参见光盘文件】：\第4章\4.2\427-1.sql。
（2）在【命令编辑区】输入“select empno,ename,job,sal from scott.emp where sal<=2000 group by job,empno,ename,sal”，然后单击【执行】按钮，出现如图4.13所示的分组查询的结果。
【参见光盘文件】：\第4章\4.2\427-2.sql。
where检查每条记录是否符合条件，having是检查分组后的各组是否满足条件。having语句只能配合group by语句使用，没有group by时不能使用having，但可以使用where。
4.2.8 字段运算查询
可以利用几种基本的算术运算符来查询数据。
常见的+（加）、-（减）、*（乘）、/（除）4种算术运算都可以用来查询数据。
在【命令编辑区】输入“select empno,ename,sal,mgr,sal+mgr from scott.emp”，然后单击【执行】按钮，出现如图4.14所示的结果。
【参见光盘文件】：\第4章\4.2\428.sql。
利用算术运算符仅仅适合多个数值型字段或字段与数字之间的运算。
4.2.9 变换查询显示
在【命令编辑区】输入“select empno 编号,ename 姓名,job 工作,sal 薪水 from scott.emp”，然后单击【执行】按钮，出现如图4.15所示的结果，可以将默认的字段名以设定的名称显示。
【参见光盘文件】：\第4章\4.2\429.sql。
以上我们学习了对单个数据表的查询语句。将上面这些基本的实例经过组合，就可以完成基本的日常数据查询任务，接下来进一步学习多表查询。
篇4：和表值函数连接引发的性能问题分析
作者：宋V剑 字体：[增加 减小] 类型：转载
最近调优过程中遇到一个问题，就是表值函数作为连接中的一部分时，可能会引起麻烦，本文会简单阐述表值函数是什么，以及为什么使用表值函数进行连接时会引发性能问题
表值函数
SQL Server中提供了类似其他编程语言的函数，而函数的本质通常是一段代码的封装，并返回值，在SQL Server中，函数除了可以返回简单的数据类型之外（Int、Varchar等），还可以返回一个集合，也就是返回一个表。
而根据是否直接返回集合或是定义后再返回集合，表值函数又分为内联用户定义表值函数和用户定义表值函数（下文统称为表值函数，省去“用户定义”四个字）。
内联表值函数
内联表值函数和普通函数并无不同，唯一的区别是返回结果为集合（表），而不是简单数据类型，一个简单的内联表值函数如代码清单1所示（摘自MSDN）。
CREATE FUNCTION Sales.ufn_CustomerNamesInRegion( @Region nvarchar(50) )RETURNS tableASRETURN (SELECT DISTINCT s.Name AS Store, a.CityFROM Sales.Store AS sINNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = s.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressIDINNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceIDWHERE sp.Name = @Region);GO
代码清单1.一个简单的表值函数
用户定义表值函数
而用户定义表值函数，需要在函数开始时定义返回的表结构，然后可以写任何代码进行数据操作，插入到定义的表结构之后进行返回，一个稍微负责的用户定义表值函数示例如代码清单2所示（摘自MSDN）。
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int) RETURNS @retContactInformation TABLE ( -- Columns returned by the function ContactID int PRIMARY KEY NOT NULL, FirstName nvarchar(50) NULL, LastName nvarchar(50) NULL, JobTitle nvarchar(50) NULL, ContactType nvarchar(50) NULL ) AS -- Returns the first name, last name, job title, and contact type for the specified contact. BEGIN DECLARE @FirstName nvarchar(50), @LastName nvarchar(50), @JobTitle nvarchar(50), @ContactType nvarchar(50); -- Get common contact information SELECT @ContactID = BusinessEntityID, @FirstName = FirstName, @LastName = LastName FROM Person.Person WHERE BusinessEntityID = @ContactID; -- Get contact job title SELECT @JobTitle = CASE -- Check for employee WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = ‘EM‘) THEN (SELECT JobTitle FROM HumanResources.Employee AS e WHERE e.BusinessEntityID = @ContactID) -- Check for vendor WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = ‘VC‘) THEN (SELECT ct.Name FROM Person.ContactType AS ct INNER JOIN Person.BusinessEntityContact AS bec ON bec.ContactTypeID = ct.ContactTypeID WHERE bec.PersonID = @ContactID) -- Check for store WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = ‘SC‘) THEN (SELECT ct.Name FROM Person.ContactType AS ct INNER JOIN Person.BusinessEntityContact AS bec ON bec.ContactTypeID = ct.ContactTypeID WHERE bec.PersonID = @ContactID) ELSE NULL END; -- Get contact type SET @ContactType = CASE -- Check for employee WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = ‘EM‘) THEN ‘Employee‘ -- Check for vendor WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = ‘VC‘) THEN ‘Vendor Contact‘ -- Check for store WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = ‘SC‘) THEN ‘Store Contact‘ -- Check for individual consumer WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = ‘IN‘) THEN ‘Consumer‘ -- Check for general contact WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = ‘GC‘) THEN ‘General Contact‘ END; -- Return the information to the caller IF @ContactID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType; END; RETURN; END; GO
代码订单2.表值函数
为什么要用表值函数
看起来表值函数所做的事情和存储过程并无不同，但实际上还是有所差别，
是因为表值函数可以被用于写入其他查询，而存储过程不行。此外，表值函数和Apply操作符联合使用可以极大的简化连接操作。
如果存储过程符合下述条件的其中一个，可以考虑重写为表值函数。
•存储过程逻辑非常简单，仅仅是一个Select语句，不用视图的原因仅仅是由于需要参数。
•存储过程中没有更新操作。
•存储过程中没有动态SQL。
•存储过程中只返回一个结果集。
•存储过程的主要目的是为了产生临时结果集，并将结果集存入临时表以供其他查询调用。
用户定义表值函数的问题
表值函数与内联表值函数不同，内联表值函数在处理的过程中更像是一个视图，这意味着在查询优化阶段，内联表值函数可以参与查询优化器的优化，比如将筛选条件（Where）推到代数树的底部，这意味着可以先Where再Join，从而可以利用索引查找降低IO从而提升性能。
让我们来看一个简单的例子。下面代码示例是一个简单的和表值函数做Join的例子：
首先我们创建表值函数，分别为内联表值函数方式和表值函数方式，如代码清单3所示。
--创建表值行数 CREATE FUNCTION tvf_multi_Test ( ) RETURNS @SaleDetail TABLE ( ProductId INT ) AS BEGIN INSERT INTO @SaleDetail SELECT ProductID FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID RETURN END --创建内联表值函数 CREATE FUNCTION tvf_inline_Test ( ) RETURNS TABLE AS RETURN SELECT ProductID FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
代码清单3.创建两种不同的函数
现在，我们使用相同的查询，对这两个表值函数进行Join，代码如代码清单4所示。
--表值函数做Join SELECT c.personid , Prod.Name , COUNT(*) ‘numer of unit‘ FROM Person.BusinessEntityContact c INNER JOIN dbo.tvf_multi_Test() tst ON c.personid = tst.ProductId INNER JOIN Production.Product prod ON tst.ProductId = prod.ProductID GROUP BY c.personid , Prod.Name --内联表值函数做Join SELECT c.personid , Prod.Name , COUNT(*) ‘numer of unit‘ FROM Person.BusinessEntityContact c INNER JOIN dbo.tvf_inline_Test() tst ON c.personid = tst.ProductId INNER JOIN Production.Product prod ON tst.ProductId = prod.ProductID GROUP BY c.personid , Prod.Name
代码清单4.表值函数和内联表值函数做Join
执行的成本如图1所示。
图1.两种方式的成本
从IO来看，很明显是选择了次优的执行计划，BusinessEntityContact选择了121317次查找，而不是一次扫描。而内联表函数能够正确知道扫描一次的成本远低于一次查找。
那问题的根源是内联表值函数，对于SQL Server来说，和视图是一样的，这意味着内联表值函数可以参与到逻辑执行计划的代数运算（或者是代数树优化）中，这意味着内敛表可以进一步拆分（如图1所示，第二个内联表的查询，执行计划具体知道内敛表中是SalesOrderHeader表和SalesOrderDetail表，由于查询只选择了一列，所以执行计划优化直到可以无需扫描SalesOrderHeader表），对于内联表值函数来说，执行计划可以完整知道所涉及的表上的索引以及相关统计信息等元数据。
另一方面，表值函数，如图1的第一部分所示，表值函数对整个执行计划来说是一个黑箱子，既不知道统计信息，也没有索引。执行计划中不知道表值函数所涉及的表（图1中为#AE4E5168这个临时表，而不是具体的表明），因此对整个执行计划来说该结果集SQL Server会假设返回的结果非常小，当表值函数返回的结果较多时（如本例所示），则会产生比较差的执行计划。
因此综上所述，在表值函数返回结果极小时，对性能可能没有影响，但返回结果如果略多，则一定会影响执行计划的质量。
如何处理
首先，在SQL Server中，我们要找出现存的和表值函数做Join的语句，通过挖掘执行计划，我们可以找出该类语句，使用的代码如代码清单5所示。
WITH XMLNAMESPACES(‘schemas.microsoft.com/sqlserver/2004/07/showplan‘ AS p) SELECT st.text, qp.query_plan FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC ) AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp WHERE qp.query_plan.exist(‘//p:RelOp[contains(@LogicalOp, “Join”)]/*/p:RelOp[(@LogicalOp[.=“Table-valued function”])]‘) = 1
代码清单5.从执行计划缓存中找出和表值函数做Join的查询
结果如图2所示。
图2.执行计划缓存中已经存在的和表值函数做Join的查询
小结
本文阐述了表值函数的概念，表值函数为何会影响性能以及在执行计划缓存中找出和表值函数做Join的查询。对于和表值函数做Apply或表值函数返回的行数非常小的查询，或许并不影响。但对于返回结果较多的表值函数做Join，则可能产生性能问题，因此如果有可能，把表值函数重写为内联表值函数或将表值函数的结果存入临时表再进行Join可提升性能。
参考资料：
www.brentozar.com/blitzcache/tvf-join/
blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx?CommentPosted=true#commentmessage
文档为doc格式
