27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT DISTINCT ID,
CASE WHEN EXISTS(SELECT 1 FROM @TB WHERE NAME=NAME1) THEN NOID END AS NAME1,
CASE WHEN EXISTS(SELECT 1 FROM @TB WHERE NAME=NAME2) THEN NOID END AS NAME2,
CASE WHEN EXISTS(SELECT 1 FROM @TB WHERE NAME=NAME3) THEN NOID END AS NAME3
FROM
(SELECT ID,LEFT(NAME,3)NAME1,''AS NAME2,''AS NAME3 FROM @TA WHERE LEN(NAME)=3
UNION ALL
SELECT ID,LEFT(NAME,3)NAME1,SUBSTRING(NAME,5,3)NAME2,''FROM @TA WHERE LEN(NAME)=7
UNION ALL
SELECT ID,LEFT(NAME,3)NAME1,SUBSTRING(NAME,5,3)NAME2,RIGHT(NAME,3) FROM @TA WHERE LEN(NAME)>7)AS T
LEFT JOIN @TB
ON T.NAME1=NAME OR (T.NAME1=NAME AND T.NAME2=NAME) OR (T.NAME1=NAME AND T.NAME2=NAME AND T.NAME3=NAME)
这样做太麻烦了!!!向高手学习!!
CREATE TABLE TA([id] INT, [name] NVARCHAR(11))
INSERT TA
SELECT 1, N'第一类' UNION ALL
SELECT 2, N'第一类,第三类' UNION ALL
SELECT 3, N'第三类,第二类' UNION ALL
SELECT 4, N'第五类,第一类' UNION ALL
SELECT 5, N'第一类,第三类,第四类'
GO
CREATE TABLE TB([name] NVARCHAR(3), [noid] INT)
INSERT TB
SELECT N'第一类', 1001 UNION ALL
SELECT N'第二类', 1002 UNION ALL
SELECT N'第三类', 1003 UNION ALL
SELECT N'第四类', 1004 UNION ALL
SELECT N'第五类', 1005 UNION ALL
SELECT N'第六类', 1006
GO
CREATE FUNCTION getStr(@name NVARCHAR(11))
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @RESULT NVARCHAR(100)
SET @RESULT=''
SELECT @RESULT=@RESULT+','+RTRIM([noid])
FROM TB
WHERE CHARINDEX([name],@name)>0
RETURN STUFF(@RESULT,1,1,'')
END
GO
SELECT id,dbo.getStr([name]) AS [name] FROM TA
DROP TABLE TA,TB
DROP FUNCTION getStr
/*
id name
----------- ----------------------------------------------------------------
1 1001
2 1001,1003
3 1002,1003
4 1001,1005
5 1001,1003,1004
*/
DECLARE @TA TABLE([id] INT, [name] NVARCHAR(7))
INSERT @TA
SELECT 1, N'第一类,第二类' UNION ALL
SELECT 2, N'第一类,第三类' UNION ALL
SELECT 3, N'第三类,第二类' UNION ALL
SELECT 4, N'第五类,第一类'
DECLARE @TB TABLE([name] NVARCHAR(3), [noid] INT)
INSERT @TB
SELECT N'第一类', 1001 UNION ALL
SELECT N'第二类', 1002 UNION ALL
SELECT N'第三类', 1003 UNION ALL
SELECT N'第四类', 1004 UNION ALL
SELECT N'第五类', 1005 UNION ALL
SELECT N'第六类', 1006
SELECT id,name=(SELECT RTRIM([noid]) FROM @TB WHERE [name]=LEFT(T.[name], CHARINDEX(',',T.[name])-1))+','+
(SELECT RTRIM([noid]) FROM @TB WHERE [name]=STUFF(T.[name], 1, CHARINDEX(',',T.[name]), ''))
FROM @TA AS T
/*
id name
----------- -------------------------
1 1001,1002
2 1001,1003
3 1003,1002
4 1005,1001
*/