其实是一个二级分类。
我想按这样的规则取出分类数据。
父类下面紧跟着子类。然后是下一个父类。然后紧跟着是这个父类的子类...
单是每个分类有个排序的。表大体结构这样的:
ID 类名 排序 父ID
1 电脑硬件 1 -1
2 键盘 0 1
3 耳机 1 1
4 软件 0 -1
5 office 1 4
6 VS 0 4
我想取出来的数据是这样的:
ID 类名 排序 父ID
1 软件 0 -1
2 VS 0 4
3 office 1 4
4 电脑硬件 1 -1
5 键盘 0 1
6 耳机 1 1
我想按这样的规则取出分类数据。
父类下面紧跟着子类。然后是下一个父类。然后紧跟着是这个父类的子类...
单是每个分类有个排序的。表大体结构这样的:
ID 类名 排序 父ID
1 电脑硬件 1 -1
2 键盘 0 1
3 耳机 1 1
4 软件 0 -1
5 office 1 4
6 VS 0 4
我想取出来的数据是这样的:
ID 类名 排序 父ID
1 软件 0 -1
2 VS 0 4
3 office 1 4
4 电脑硬件 1 -1
5 键盘 0 1
6 耳机 1 1
推荐在Connection上启用MARS属性,这样的话就能够轻松获取级联式的数据了.
if object_id('[tree]') is not null drop table [tree]
create table [tree] (ID int,类名 varchar(8),排序 int,父ID int)
insert into [tree]
select 1,'电脑硬件',1,-1 union all
select 2,'键盘',0,1 union all
select 3,'耳机',1,1 union all
select 4,'软件',0,-1 union all
select 5,'office',1,4 union all
select 6,'VS',0,4
go
;with wsp
as
(
select lev=cast(row_number() over(order by 排序) as varchar),* from tree b where 父ID=-1
union all
select lev=cast(a.lev+ltrim(row_number() over(order by b.排序)) as varchar),b.* from wsp a,tree b where a.id=b.父ID
)
select id=row_number() over(order by lev),类名,排序,父id from wsp order by lev
--结果:
id 类名 排序 父id
-------------------- -------- ----------- -----------
1 软件 0 -1
2 VS 0 4
3 office 1 4
4 电脑硬件 1 -1
5 键盘 0 1
6 耳机 1 1
if OBJECT_ID('tempdb..#') is not null
drop table #;
create table #(id int identity,name nvarchar(10),sort int,pid int);
go
insert into #(name, sort, pid)
select N'电脑硬件',1,-1 union all
select N'键盘',0,1 union all
select N'耳机',1,1 union all
select N'软件',0,-1 union all
select N'office',1,4 union all
select N'VS',0,4;
goselect ROW_NUMBER() over (order by (select sort from #
where id=(case when t.pid=-1 then t.id else t.pid end)),
(case pid when -1 then -1 else sort end)) id,
name,sort,pid from # t;
/*
1 软件 0 -1
2 VS 0 4
3 office 1 4
4 电脑硬件 1 -1
5 键盘 0 1
6 耳机 1 1
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[类名] [nvarchar](10),[排序] [int],[父ID] [int])
INSERT INTO [tb]
SELECT '1','电脑硬件','1','-1' UNION ALL
SELECT '2','键盘','0','1' UNION ALL
SELECT '3','耳机','1','1' UNION ALL
SELECT '4','软件','0','-1' UNION ALL
SELECT '5','office','1','4' UNION ALL
SELECT '6','VS','0','4'
-->SQL查询如下:
;WITH T AS
(
SELECT *,px=cast(row_number() over(order by 排序) AS VARBINARY(MAX)) FROM [tb] WHERE 父ID=-1
UNION ALL
SELECT A.*,PX+cast(row_number() over(order by A.排序) AS VARBINARY) FROM tb A,T WHERE A.父ID=T.ID
)
SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),
类名,排序,父ID
FROM T
ORDER BY PX
/*
ID 类名 排序 父ID
-------------------- ---------- ----------- -----------
1 软件 0 -1
2 VS 0 4
3 office 1 4
4 电脑硬件 1 -1
5 键盘 0 1
6 耳机 1 1(6 行受影响)
*/
--sql2000的解法(用函数)
--> 测试数据: [tree]
if object_id('[tree]') is not null drop table [tree]
create table [tree] (ID int,类名 varchar(8),排序 int,父ID int)
insert into [tree]
select 1,'电脑硬件',1,-1 union all
select 2,'键盘',0,1 union all
select 3,'耳机',1,1 union all
select 4,'软件',0,-1 union all
select 5,'office',1,4 union all
select 6,'VS',0,4
gocreate function get_sort()
returns @t table(id int,类名 varchar(8),排序 int,父ID int,path varchar(20),lev int)
as
begin
declare @lev int
set @lev=0
insert into @t select *,path=排序,@lev from tree where 父ID=-1
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,b.path+(select ltrim(count(1)) from tree where 父ID=a.父ID and 排序<=a.排序),@lev from tree a,@t b
where a.父ID=b.id and b.lev=@lev-1
end
return
end
go--调用函数
select id=(select count(1) from get_sort() where path<=a.path),
类名,排序,父id from get_sort() a order by path
--结果:
id 类名 排序 父id
----------- -------- ----------- -----------
1 软件 0 -1
2 VS 0 4
3 office 1 4
4 电脑硬件 1 -1
5 键盘 0 1
6 耳机 1 1