在三个表之间连接，其中数据在逗号值行中？ [英] Join between three tables where data is in Comma values row ?

问题描述

Category table 

CatID	Category
1	Wedding
2	Babies/Kids
3	Special Occasion
4	Corporate
5	Commercial
6	Fashion
7	Travel

Subactegory Table

SubCatID	CategoryID	SubCategory
1	1	Candid Wedding 
2	2	Baby First Birthday  
3	3	Birthday 
4	4	Conference 
5	5	Advertisement 
6	6	Model Portfolio 
7	1	Parsi Wedding 
8	1	Buddhist Wedding 
9	1	North Indian Wedding 
10	1	South Indian Wedding 

Main Table 

ScreenName	SubCategoryID
Forever22	2,5,10,12,13,25
war	        4
Rinku	        8,5
abcdefg  	8,5

i want  data category wise  from the 3rd Table 
like form category table 5 is Commercial.
then the output should be 


ScreenName	Category
Forever22	2,5,10,12,13,25
Rinku	8,5
abcdefg	8,5

please help 

推荐答案

首先在sql server中创建一个TABLE VALUED函数如下所述： -

First Create a TABLE VALUED function in the sql server as mentioned below :-

CREATE FUNCTION [dbo].[SplitBySeparator]
(
    @Value	NVARCHAR(MAX),
    @Spliter	NVARCHAR(5)
) 
RETURNS @ReturnValues TABLE
(
    Value		NVARCHAR(MAX)
)
AS 
BEGIN
	-- iterate through the values
	WHILE (CHARINDEX(@Spliter,@Value)>0)
		BEGIN
			INSERT INTO @ReturnValues (value)
			SELECT
				 Value = LTRIM(RTRIM(SUBSTRING(@Value,1,CHARINDEX(@Spliter,@Value)-1)))
				 SET @Value = SUBSTRING(@Value,CHARINDEX(@Spliter,@Value)+LEN(@Spliter),LEN(@Value))
		END
	
	-- insert records into result table
	INSERT INTO @ReturnValues (Value)
	SELECT Value = LTRIM(RTRIM(@Value))
	RETURN
	
END

此函数用于拆分我们作为第二个参数传递的拆分器字符传递的值，然后我们可以像下面所示的示例脚本一样： -



示例： -

This function is used for splitting the value passed by the splitter character we are passing as second parameter and then we can do as sample script written below :-

Example :-

DECLARE @SubCategoryID INT = 5
DECLARE @Test TABLE(
	ScreenName VARCHAR(50),
	SubCategoryIDs VARCHAR(MAX)
)

INSERT INTO @Test
VALUES('Forever22', '2,5,10,12,13,25')

INSERT INTO @Test
VALUES('war', '4')

INSERT INTO @Test
VALUES('Rinku', '8,5')

INSERT INTO @Test
VALUES('abcdefg', '8,5')

SELECT * FROM @Test WHERE @SubCategoryID IN(SELECT * FROm [dbo].[SplitBySeparator](SubCategoryIDs, ','))

这只是我们如何完成您的要求并用临时表解释的一个例子。希望这肯定对你有所帮助。

This is just an example how we can accomplish your requirements and explained with a temporary table. Hope this will definitely of help for you.

SELECT m.ScreenName,m.SubCategoryID
FROM Category c
INNER JOIN Subcategory sc
ON sc.CategoryID = c.CatID
INNER JOIN Main m
ON ',' + m.SubCategoryID + ',' LIKE '%,' + CAST(sc.SubCatID AS varchar(20)) + ',%'

这篇关于在三个表之间连接，其中数据在逗号值行中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！