--------------------SQL Server数据格式化工具------------------- --------------------------------------------------------------- -- DESIGNER :happycell188(喜喜) -- QQ :584738179 -- Development Tool :Microsoft Visual C++ 6.0 C Language -- FUNCTION :CONVERT DATA TO T-SQL --------------------------------------------------------------- -- Microsoft SQL Server 2005 -- Developer Edition on Microsoft Windows XP [版本 5.1.2600] --------------------------------------------------------------- ---------------------------------------------------------------use test go if object_id('test.dbo.tb') is not null drop table tb -- 创建数据表 create table tb ( id int, parentid int, txt char(7) ) go --插入测试数据 insert into tb select 1,0,'湖南省' union all select 2,1,'长沙市' union all select 3,2,'天心区' union all select 4,2,'雨花区' go --代码实现-->创建存储过程create proc proc_test @input int as begin ;with t as (select * from tb where id=@input union all select tb.* from tb inner join t on tb.parentid=t.id) select distinct input=@input, result=stuff((select ','+rtrim(id) from t where id<>@input for xml path('')),1,1,'') from t end go-->测试存储过程 exec proc_test 1 /*测试结果input result --------------------- 1 2,3,4(1 行受影响) */ exec proc_test 2 /*测试结果input result --------------------- 2 3,4(1 行受影响) */
(
select * from tb where id=1
union all
select a.* from tb a join f b on a.parentid=b.id
)
select id from f where id<>1
select id from table where id=1
--数据级联
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
parentid int,
txt char(7)
)
go
--插入测试数据
insert into tb select 1,0,'湖南省'
union all select 2,1,'长沙市'
union all select 3,2,'天心区'
union all select 4,2,'雨花区'
go
--代码实现-->创建存储过程create proc proc_test
@input int
as
begin
;with t as (select * from tb where id=@input
union all select tb.* from tb inner join t on tb.parentid=t.id)
select distinct input=@input,
result=stuff((select ','+rtrim(id) from t where id<>@input for xml path('')),1,1,'')
from t
end
go-->测试存储过程
exec proc_test 1
/*测试结果input result
---------------------
1 2,3,4(1 行受影响)
*/
exec proc_test 2
/*测试结果input result
---------------------
2 3,4(1 行受影响)
*/