[转]cross apply,WITH AS, ROW_NUMBER(),OVER(),object_id()

---------------------创建表--------------------------------
if object_id('T_a','U') is not null
drop table T_a
GO

CREATE TABLE T_a(
 id int unique not null,
    [name] varchar(50),
)
GO
if object_id('T_b',N'U') is not null
drop table T_b
GO
create table T_b
(
    id int unique not null,
    [name] varchar(10),
    a_ids varchar(100) null --要在这一列中存放t_a表的ID序列,这样做连第一范式都没有满足，但是有时候考虑性能或设计我们可能会像这么用
)
GO
-----------------------初始化数据 -------------------------
declare @i bigint;
set @i=1;
while @i<1000
begin
 INSERT INTO T_a VALUES(@i,'Victor-'+str(@i));
 set @i=@i+1;
end
--
declare @str nvarchar(500);
set @str='';
INSERT INTO T_b VALUES(1,'B-1','1,2,4,50,12,6,78')
select * from t_a
select * from t_b
-----------------------------------------------------------
GO
---------创建一个表值函数，用来拆分用逗号分割的数字串，返回只有一列数字的表---------------
if object_id('splitIDs','TF') is not null
drop function splitIDs;
GO
create function splitIDs(
    @Ids nvarchar(1000)
)
returns @t_id TABLE (id bigint)
as
begin
    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(',',@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;
end
------------------------------------------------------------------------------------
GO
-------------------测试splitIDs的执行效果 ---------------------------------------
select * from splitIDs('1,2,4,3')
select * from splitIDs('100')
select * from splitIDs(NULL)
GO
----------------使用cross apply获得t_b表中指定行对应的所有t_a表中的记录--------------
select
    aid = t_a.id
    ,aname = t_a.name
    ,bid = t_b.id
from t_b
cross apply splitIDs(a_ids) AS tbl_Ids
INNER JOIN t_a ON (tbl_Ids.id = t_a.id)
where t_b.id = 1
-------------------------------------------------------------------------------------
/*
select *
from t_b
cross apply splitIDs(a_ids) tbl_Ids
INNER JOIN t_a ON tbl_Ids .id = t_a.id
where t_b.id = 1

select * from t_a
select * from t_b
*/

/*
-------------------------------------------------------------
select * from dbo.sysobjects
if exists (
select * from dbo.sysobjects where id = object_id('dbo.a') and OBJECTPROPERTY(id,'IsUserTable') = 1
)
print 'dddd'
-----------------------------------
*/
/*

OBJECT_ID()：返回数据库对象标识号。N是显式的将非unicode字符转成unicode字符，它来自 SQL-92 标准中的 National（Unicode）数据类型，用于扩展和标准化，在这里可以不用，写作object_id(PerPersonData)。

OBJECTPROPERTY()：返回当前数据库中对象的有关信息.BOOL 1表“真”。同样可以写成OBJECTPROPERTY(id,IsUserTable) = 1。

整条语句的意思是判断数据库里有没有存在PerPersonData这样一张表。

*/
--CTE是Common Table Expression的简写，翻译成中文就是通用表表达式，它可以在select，insert或者update中使用。
--为了说明问题，我们先随便建张表，插入几条数据：
--知识点：WITH AS, ROW_NUMBER(),OVER(),object_id()
if object_id('t','U') is not null --用object_id函数判断表是否在数据库中存在很简洁
drop table t;
GO
create table t(c1 int,c2 decimal,c3 int);
GO
INSERT INTO t
SELECT c1 = 1,c2 = 5.0,c3=10
UNION
SELECT c1 = 2,c2 = 5.5,c3=10
UNION
SELECT c1 = 3,c2 = 5.0,c3=20
UNION
SELECT c1 = 4,c2 = 5.5,c3=20
--下面我们使用CTE写一个分页的sql语句
GO
WITH t_cn AS
(   
select c1,c2,c3,rn = ROW_NUMBER()
OVER(ORDER BY c1 DESC)
FROM t WHERE 0 = 0 --可以在此处添加一些条件
)
SELECT c1,c2,c3,rn FROM t_cn WHERE rn between 2 and 5
SELECT totalCn = COUNT(*) FROM t WHERE 0 = 0