急请教一条SQL语句问题
已知条件：一张零售小票有4个品种A、B、C、D
语句的结果：生成一张表，这张表只有一列，表包含的数据行为：A；B；C；D；AB；BC；CD；AC；AD；BC；BD；CD；ABC；ABD；BCD。
尽量少用循环，因为零售小票的品种数不固定，需要动态确定
[解决办法]
create function F_strSpit12(@s varchar(200)) returns @t table(col varchar(5))asbegin insert into @t select substring(@s,number,1) from v_getnumber where number <=len(@s) while not exists(select 1 from @t where col = @s) insert @t -- output inserted.*--inserted.col, inserted.col -- into @tt SELECT T3.COL + T2.COL FROM @t AS T2,@t AS T3 WHERE len(t3.col) = 1 and charindex(T3.COL,T2.COL) =0 and T2.COL > T3.COL returnend godeclare @s varchar(200)set @s='ABCD'select distinct col,len(col) from dbo.F_strSpit12(@s)where len(col) > 1 order by len(col),col/*col ----- -----------AB 2AC 2AD 2BC 2BD 2CD 2ABC 3ABD 3ACD 3BCD 3ABCD 4(11 行受影响)*/
[解决办法]
;WITH t1(name) AS(SELECT 'A' UNION ALLSELECT 'B' UNION ALLSELECT 'C' UNION ALLSELECT 'D' ),t2 AS(SELECT a.name AS name1 ,b.name AS name2 FROM t1 AS a ,t1 AS bWHERE a.name <b.name ),t3 AS(SELECT a.name,b.name1, b.name2 FROM t1 AS a ,t2 AS bWHERE a.name <b.name1 )SELECT name FROM t1 UNION ALLSELECT name1+name2 FROM t2UNION ALLSELECT name+name1+name2 FROM t3/*name----ABCDABACBCADBDCDABCABDACDBCD(14 行受影响)*/
[解决办法]
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--排列组合--DEBUG:exec [dbo].[Arrange] @input='111,222,333'--input:1,2,3--output:--1 --2 --3 --1,2 --1,3 --2,3 --1,2,3CREATE PROCEDURE [dbo].[Arrange] @input VARCHAR(100)AS BEGIN SET NOCOUNT ON IF ( LEN(@input) < 1 ) RETURN DECLARE @tableArrange TABLE ( id INT IDENTITY(1, 1) , arrangeValue VARCHAR(100) , maxid INT , lenOfValue INT ) DECLARE @split VARCHAR(10) SET @split = ',' DECLARE @startIndex INT SET @startIndex = 1 DECLARE @endIndex INT SET @endIndex = CHARINDEX(@split, @input, @startIndex) DECLARE @items VARCHAR(100) WHILE ( @endIndex <> 0 ) BEGIN SET @items = SUBSTRING(@input, @startIndex, @endIndex - @startIndex) IF LEN(@items) > 0 INSERT INTO @tableArrange ( arrangeValue ) VALUES ( @items ) SET @startIndex = @endIndex + 1 SET @endIndex = CHARINDEX(@split, @input, @startIndex) END SET @items = SUBSTRING(@input, @startIndex, LEN(@input) - @startIndex + 1) IF LEN(@items) > 0 INSERT INTO @tableArrange ( arrangeValue ) VALUES ( @items ) UPDATE @tableArrange SET maxid = id , lenOfValue = 1 DECLARE @count INT-- DECLARE @currentlen INT DECLARE @value VARCHAR(100) DECLARE @valueInsert VARCHAR(100) DECLARE @start INT DECLARE @end INT DECLARE @i INT DECLARE @j INT DECLARE @maxid INT DECLARE @lenofvalue INT SELECT @count = MAX(id) FROM @tableArrange SET @currentlen = 1 WHILE ( @currentlen < @count ) BEGIN SELECT @start = MIN(id) , @end = MAX(id) FROM @tableArrange WHERE lenOfValue = @currentlen SET @i = @start WHILE ( @i < @end ) BEGIN SELECT @maxid = maxid , @value = arrangeValue , @lenofvalue = lenofvalue FROM @tableArrange WHERE id = @i SET @j = @maxid + 1 WHILE ( @j <= @count ) BEGIN SELECT @valueInsert = @value + ',' + arrangeValue FROM @tableArrange WHERE id = @j INSERT INTO @tableArrange ( arrangeValue, maxid, lenofvalue ) VALUES ( @valueInsert, @j, @lenofvalue + 1 ) SET @j = @j + 1 END SET @i = @i + 1 END SET @currentlen = @currentlen + 1 END SELECT arrangeValue FROM @tableArrange SET NOCOUNT OFF END
