CREATE TABLE [dbo].[MYKind](
[KIND] [nvarchar](50) NOT NULL,
[ParentKind] [nvarchar](50) NULL,
[SortNumber] [int] NULL,
CONSTRAINT [PK_MYKind] PRIMARY KEY CLUSTERED
(
[KIND] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MyGoods](
[ID] [nvarchar](50) NOT NULL,
[Kind] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_MyGoods] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--表 MyGoods [] 的数据
INSERT INTO MyGoods ([ID], [Kind], [Name]) VALUES (N'1', N'无领运动服', N'运动服AA')
INSERT INTO MyGoods ([ID], [Kind], [Name]) VALUES (N'2', N'男鞋', N'男鞋BB')
--表 MYKind [] 的数据
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'男鞋', N'鞋', 2)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'女鞋', N'鞋', 1)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'无领运动服', N'运动服', 1)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'鞋', null, 1)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'衣服', null, 2)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'运动服', N'衣服', 1)我要查询出MyGoods中的商品的类型的最上级，并按他的SortNumber排序
结果为：
鞋 1
衣服 2谢谢PS:前一时间学会用函数的方法我可以取到鞋和衣服，但加多了一个排序就写不出了
（自己SQL太菜）谢谢
IF OBJECT_ID('dbo.f_getroot') IS NOT NULL
DROP FUNCTION dbo.f_getroot;
GOCREATE FUNCTION dbo.f_getroot
(
@kind AS NVARCHAR(100)
)
RETURNS NVARCHAR(100)
AS
BEGIN
SELECT @kind = parentkind
FROM MYKind
WHERE kind = @kind
WHILE @@ROWCOUNT > 0
BEGIN
SELECT @kind = parentkind
FROM MYKind
WHERE kind = @kind
AND parentkind IS NOT NULL
END
RETURN @kind;
END
GO
SELECT dbo.f_getroot(a.kind) AS ROOT,
b.SortNumber
FROM MyGoods a,
MYKind b
WHERE dbo.f_getroot(a.kind) = b.kind
ORDER BY
b.SortNumber
root SortNumber
---------------------------------------------------------------------------------------------------- -----------
鞋 1
衣服 2（所影响的行数为 2 行）
[KIND] [nvarchar](50) NOT NULL,
[ParentKind] [nvarchar](50) NULL,
[SortNumber] [int] NULL,
CONSTRAINT [PK_MYKind] PRIMARY KEY CLUSTERED
(
[KIND] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MyGoods](
[ID] [nvarchar](50) NOT NULL,
[Kind] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_MyGoods] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--表 MyGoods [] 的数据
INSERT INTO MyGoods ([ID], [Kind], [Name]) VALUES (N'1', N'无领运动服', N'运动服AA')
INSERT INTO MyGoods ([ID], [Kind], [Name]) VALUES (N'2', N'男鞋', N'男鞋BB')
--表 MYKind [] 的数据
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'男鞋', N'鞋', 2)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'女鞋', N'鞋', 1)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'无领运动服', N'运动服', 1)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'鞋', null, 1)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'衣服', null, 2)
INSERT INTO MYKind ([KIND], [ParentKind], [SortNumber]) VALUES (N'运动服', N'衣服', 1)
;with t as
(
select * from MYKind t where kind in(select kind from mygoods)
union all
select a.* from mykind a join t b on a.kind=b.ParentKind
)
select Kind,ParentKind from t a
where not exists(select 1 from t where a.ParentKind=Kind)
order by SortNumber
/*
Kind ParentKind
-------------------------------------------------- --------------------------------------------------
鞋 NULL
衣服 NULL(2 行受影响)*/
--SQL2005 CTEWITH SubsCTE
AS
(
SELECT *,
kind AS ROOT,
SortNumber AS rootsort
FROM MYKind
WHERE ParentKind IS NULL
UNION ALL
SELECT C.*,
p.root,
p.rootsort
FROM MYKind AS c
JOIN SubsCTE AS p
ON C.parentkind = P.kind
)
SELECT b.root AS ROOT,
b.SortNumber
FROM MyGoods a,
SubsCTE b
WHERE a.kind = b.kind
ORDER BY
b.SortNumber
(
Department nvarchar(50),--部门名称
ParentDept nvarchar(50),--上级部门
DepartManage nvarchar(30)--部门经理
)
insert into #EnterPrise select '技术部','总经办','Tom'
insert into #EnterPrise select '商务部','总经办','Jeffry'
insert into #EnterPrise select '商务一部','商务部','ViVi'
insert into #EnterPrise select '商务二部','商务部','Peter'
insert into #EnterPrise select '程序组','技术部','GiGi'
insert into #EnterPrise select '设计组','技术部','yoyo'
insert into #EnterPrise select '专项组','程序组','Yue'
insert into #EnterPrise select '总经办','','Boss'
--查询部门经理是Tom的下面的部门名称
;with hgo as
(
select *,0 as rank from #EnterPrise where DepartManage='Tom'
union all
select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department
)
select * from hgo
/*
Department ParentDept DepartManage rank
--------------- -------------------- ----------------------- -----------
技术部 总经办 Tom 0
程序组 技术部 GiGi 1
设计组 技术部 yoyo 1
专项组 程序组 Yue 2
*/
--查询部门经理是GiGi的上级部门名称
;with hgo as
(
select *,0 as rank from #EnterPrise where DepartManage='GiGi'
union all
select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.Department=h1.ParentDept
)
select * from hgo
/*
Department ParentDept DepartManage rank
-------------------- ---------------------- ----------- -----------
程序组 技术部 GiGi 0
技术部 总经办 Tom 1
总经办 Boss 2
*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/01/31/5274571.aspx
;with cte as
(
select k.* from MyGoods m join MYKind k on m.Kind=k.KIND
union all
select k.* from cte join MYKind k on cte.ParentKind=k.KIND
)
select KIND,SortNumber from cte c where ParentKind is null
/*
KIND SortNumber
-------------------------------------------------- -----------
鞋 1
衣服 2*/