我有一个sql表名类别.这是结构.
slno Category Uppercategory 1 Mouse Computer 2 Computer Electronics 3 Electronics END 4 END -
这里鼠标在uppercategory计算机,然后是uppercategory Electronics中的计算机和电子设备是最后一个uppercategory,因为具有uppercategory上层类别的电子设备需要得到上层类别为END的最后一类(电子设备).
我尝试了一些代码,但没有得到结果.这是我的代码.
USE [Database1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[kt_category]
@Dcat AS NVARCHAR(250)
AS
DECLARE @tmp TABLE (cater NVARCHAR(255))
BEGIN
INSERT INTO @tmp
SELECT UPPERCATEGORY FROM CATEGORY where CATEGORY=@Dcat
while( SELECT UPPERCATEGORY FROM CATEGORY )= 'END'
RETURN
END
解决方法
您还可以使用递归查询来实现此目的.
;WITH R
AS (SELECT *,1 AS Level
FROM CATEGORY
WHERE Category = @Dcat
UNION ALL
SELECT C.*,R.Level + 1
FROM CATEGORY C
JOIN R
ON R.Uppercategory = C.Category)
SELECT TOP 1 *
FROM R
ORDER BY Level DESC
你也可以使用
SELECT TOP 1 * FROM R WHERE Uppercategory = 'End' ORDER BY Level
