我有一个看起来像这样的表:
Names uniqueid
crosby,stills,nash 1
crosby,stills,nash,young 2
crosby 3
stills 4
nash 5
young 6
我将其拆分为:
Names uniqueid name
crosby,stills,nash 1 crosby
crosby,stills,nash 1 stills
crosby,stills,nash 1 nash
crosby,stills,nash,young 2 crosby
crosby,stills,nash,young 2 stills
crosby,stills,nash,young 2 nash
crosby,stills,nash,young 2 young
crosby 3 crosby
stills 4 stills
nash 5 nash
young 6 young
我想要的:
Namecombos count
crosby 3
stills 3
nash 3
young 2
crosby,stills 2
stills,nash 2
nash,young 1
crosby,stills,nash 2
stills,nash,young 1
crosby,stills,nash,young 1
我要做的就是找到名称的独特组合。Crosby,Stills,Nash,Young具有10种独特的组合:克罗斯比静止纳什年轻的克罗斯比,静止剧照,纳什纳什,年轻克罗斯比,剧照,纳什剧照,纳什,年轻,Crosby,剧照,纳什,年轻
订单很重要。
只需要分解成唯一的组合,然后计算多少结果。
希望这是有道理的。
哇,这灾难(并绞尽脑汁(我的大脑。我不是专业人士,所以如果有人想精心做我的工作,请这样做,我会接受您的答案!
,但它可以完成困难的部分,这正在创建独特的组合。计算它们应该是简单的部分。
注意:我从原始的逗号分离值开始,而不是中间表。
CREATE FUNCTION [dbo].[func_Split]
(
@OrigStr varchar(8000),
@Delimiter varchar(100)
)
RETURNS @tblArray TABLE
(
ElementID int IDENTITY(1,1), -- Array index
Element varchar(1000) -- Array element contents
)
AS
BEGIN
-- Local Variable Declarations
-- ---------------------------
DECLARE @Index1 smallint,
@Index2 smallint,
@incr smallint,
@OuterLoop smallint,
@n smallint,
@LastDelim smallint
SET @n = LEN(@OrigStr) - LEN(REPLACE(@OrigStr, ',', ''))
SET @OuterLoop = 1
SET @LastDelim = LEN(@OrigStr) + 1 - CHARINDEX(@Delimiter,REVERSE(@OrigStr))
WHILE @OuterLoop <= @n+1
BEGIN
SET @Index1 = 1
SET @Index2 = @Index1
WHILE @Index2 < LEN(@OrigStr)
BEGIN
SET @Index2 = @Index1
SET @incr = 1
WHILE @incr <= @OuterLoop
BEGIN
SET @Index2 = CHARINDEX(@Delimiter,@OrigStr,@Index2+1)
IF @Index2 = 0 BEGIN SET @Index2 = LEN(@OrigStr) END
SET @incr = @incr + 1
END
IF @Index2 = LEN(@OrigStr)
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(SUBSTRING(@OrigStr,@Index1,@Index2-@Index1+1))))
END
ELSE
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(SUBSTRING(@OrigStr,@Index1,@Index2-@Index1))))
END
SET @Index1 = CHARINDEX(@Delimiter,@OrigStr,@Index1+1)+1
END
SET @OuterLoop = @OuterLoop + 1
END
RETURN
END