递归SQL函数 [英] Recursive SQL Function

问题描述

我有两个表，我需要编写一个递归函数.

Hi,

I have two tables and I need to write a recursive function.

PRODUCT with columns 
PRODUCT_ID varchar(50), PRIMARYKEY and 
PRODUCT_GROUP_ID varchar (200)

PRODUCT_GROUP with columns
PRODUCT_GROUP_ID varchar(50), PRIMARY KEY and 
PRODUCT_UPPER_GROUP_ID varchar (200)

这是PRODUCT表的数据:

Here is data for PRODUCT table:

PRODUCT_ID	PRODUCT_GROUP_ID
SonyTFT3500	SonySiyah

这是PRODUCT_GROUP表的数据:

and here is the data for PRODUCT_GROUP table:

PRODUCT_GROUP_ID	PRODUCT_UPPER_GROUP_ID
Elektronik,		null
Ev Elektroniği,		Elektronik
TV,			Ev Elektroniği
Sony,			TV
SonySiyah,		Sony

除Elektronik之外，所有其他PRODUCT_GROUP_IDs都有一个上层节点.我需要的是给定PRODUCT_ID = ''SonyTFT3500''的函数，该函数返回类似以下内容的内容:

Except Elektronik, all the other PRODUCT_GROUP_IDs have an upper node. What I need is a function given a PRODUCT_ID = ''SonyTFT3500'' which returns something like this:

temp = "Elektronik > Ev Elektroniği > TV > Sony > SonySiyah". 
temp2 = "Elektronik > Ev Elektroniği > TV > Sony > SonySiyah > subnode > subnode > .. > ... > ...."

您可以将PRODUCT_GROUP_ID视为一个父类别，该类别可能具有一个类别，并且类别可能具有一个子类别，并且该子类别具有其自己的子类别，并且无限期地像这样.

如何在Sql Server 2008中编写递归函数，以便获得此字符串temp?

You can think of PRODUCT_GROUP_ID as a parentcategory which might have a category and category might have a subcategory and subcategory has its own subcategory and this goes like this indefinitely.

How do I write a recursive function in Sql Server 2008 so that I get this string temp ?

推荐答案

看看通用表表达式(CTE)
与common_table_expression(Transact-SQL) [ http://www.4guysfromrolla.com/webtech/071906-1.shtml [ ^ ]

完成后，您应该可以轻松解决问题.

问候
Espen Harlinn

Take a look at Common Table Expressions (CTE)
WITH common_table_expression (Transact-SQL)[^]

http://www.4guysfromrolla.com/webtech/071906-1.shtml[^]

When you are done, you should be able to easily solve your problem.

Regards
Espen Harlinn

CREATE FUNCTION [dbo].[GetUpperProductNode]
(
	@PRODUCT_GROUP_ID NVARCHAR(100)
)
RETURNS NVARCHAR(100)
AS
BEGIN
	DECLARE @PRODUCT_UPPER_GROUP_ID NVARCHAR(100)
	SELECT @PRODUCT_UPPER_GROUP_ID = PRODUCT_UPPER_GROUP_ID FROM PRODUCT_GROUP WHERE PRODUCT_GROUP_ID = @PRODUCT_GROUP_ID
	
	RETURN @PRODUCT_UPPER_GROUP_ID

END

DECLARE @ReachedTop BIT
SET @ReachedTop = 0
CREATE TABLE #Result (PRODUCT_UPPER_GROUP_ID NVARCHAR(100))
DECLARE @PRODUCT_UPPER_GROUP_ID NVARCHAR(100)
SET @PRODUCT_UPPER_GROUP_ID = 'SonySiyah'
INSERT #Result SELECT @PRODUCT_UPPER_GROUP_ID
WHILE (@ReachedTop = 0)
BEGIN
    PRINT @PRODUCT_UPPER_GROUP_ID
    SET @PRODUCT_UPPER_GROUP_ID = (SELECT dbo.GetUpperProductNode(@PRODUCT_UPPER_GROUP_ID))
    IF (@PRODUCT_UPPER_GROUP_ID IS NULL)
        SET @ReachedTop = 1
    ELSE
        INSERT #Result SELECT @PRODUCT_UPPER_GROUP_ID
END
SELECT * FROM #Result
DROP TABLE #Result

这篇关于递归SQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！