比较难，如何随机排列数字组合！！！！！

viva369 2016-12-29 05:44:19

加精

create table #t(id int)
insert #t select 1
insert #t select 2
insert #t select 3
insert #t select 4
insert #t select 5

例如表内有不固定、不重复的数字，如果将该表数据进行随机，不重复的组合
例如
1 , 2+3+4+5
1,2,3+4+5
1,2,3,4+5
.....
5,1+2+3+4

...全文

6163 68 打赏收藏转发到动态举报

写回复

用AI写文章

68 条回复

切换为时间正序

请发表友善的回复…

发表回复

a13827188163 2017-06-02

打赏
举报

MySQL RAND()函数调用可以在0和1之间产生一个随机数： mysql> SELECT RAND( ), RAND( ), RAND( ); +------------------+-----------------+------------------+ | RAND( ) | RAND( ) | RAND( ) | +------------------+-----------------+------------------+ | 0.45464584925645 | 0.1824410643265 | 0.54826780459682 | +------------------+-----------------+------------------+ 1 row in set (0.00 sec)

a13827188163 2017-06-02

打赏
举报

这个是我工作中用于生成随机手机号后8位数字所使用的sql

a13827188163 2017-06-02

打赏
举报

个人认为随机数字组合应该是


  DECLARE chars_str varchar(20) DEFAULT '0123456789';
	/* 通过随机截取数字拼接成8位随机数 */
		WHILE i < 8 DO
			SET str = concat(str,substring(chars_str , FLOOR(1 + RAND()*10 ),1));
			SET i = i +1;
		END WHILE;

qq_38758583 2017-05-14

打赏
举报

这样真的有用吗

qq_38758583 2017-05-14

打赏
举报

测试

dashuigangxx 2017-04-05

打赏
举报

python stata R都可以很轻松地实现随机数字生成或从已知数据中进行随机数字组合

HNBC996 2017-01-13

打赏
举报

一个个都是大牛，涨见识了

Tiger_Zhao 2017-01-05

打赏
举报

大概 roy_88 的日常都用在开发/维护上。
要从用户含糊不清的描述中归纳/猜测用户需求的工作做得不多。

其实需求分析很多时候就是：用户描述；按最大的可能猜一两个，用精确图表描述后，让用户确认/补充描述。这样的反复中……

Ginnnnnnnn 2017-01-05

打赏
举报

我重新来凑个热闹

DECLARE @Str VARCHAR(500) = '',
		@iCount INT
IF OBJECT_ID('tempdb..#Tmp1') IS NOT NULL
	DROP TABLE #Tmp1
IF OBJECT_ID('tempdb..#Tmp2') IS NOT NULL
	DROP TABLE #Tmp2

;WITH CTE(Code) AS
(
	SELECT 'A' 
	UNION ALL SELECT 'B' 
	UNION ALL SELECT 'C' 
	--UNION ALL SELECT 'D' 
	--UNION ALL SELECT 'E' 
)
SELECT *,		
		ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RNr
		INTO #Tmp1
	FROM CTE
SELECT @iCount = @@ROWCOUNT

;WITH CTE AS
(
	SELECT  CONVERT(VARCHAR(100),Code) AS Code,			
			CONVERT(BIGINT,POWER(2,RNr-1)) AS Flag,
			Rnr,
			1 AS LoopNr
		FROM #Tmp1 
	UNION ALL
	SELECT  CONVERT(VARCHAR(100),a.Code + '+' + b.Code),
			a.Flag | POWER(2,b.RNr-1),
			b.RNr,
			a.LoopNr + 1
		FROM CTE a
			INNER JOIN #Tmp1 b ON POWER(2,b.RNr-1) & a.Flag = 0  AND a.RNr < b.RNr
			WHERE a.LoopNr < @iCount
)
SELECT CTE.Code,CTE.Flag,CTE.LoopNr
	INTO #Tmp2
	FROM CTE

;WITH CTE AS
(
SELECT Code,
		Flag,
		LoopNr
	FROM #Tmp2
UNION ALL
SELECT  CONVERT(VARCHAR(100),a.Code + ',' + b.Code),
		a.Flag | b.Flag,
		a.LoopNr + b.LoopNr
	FROM CTE a
		INNER JOIN #Tmp2 b ON a.Flag & b.Flag = 0 AND a.LoopNr + b.LoopNr <= @iCount AND a.Flag < b.Flag
)
SELECT CTE.Code
	FROM CTE 
		WHERE CTE.LoopNr = @iCount
		  ORDER BY CTE.Code

中国风 2017-01-05

打赏
举报

再写1种效果，生成4条记录,需求不清此贴不回了

e.g.

DECLARE @Counted INT=3,@Num1 INT=0,@i INT=0;

WHILE @i<=@Counted

		SELECT @Num1=@Num1+POWER(2,@i-1),@i=@i+1



;WITH CTE

AS

(

SELECT 1 AS ID 

UNION ALL

SELECT ID+1 FROM CTE WHERE ID<@Counted

),Array2(IDs,Num1,Counted,LatestID1,LatestID2)

AS

(

SELECT CAST (ID AS VARCHAR(100)),@Num1- POWER(2,ID-1),1,ID,ID FROM CTE 

UNION ALL

SELECT 

	CAST(a.IDs+','+RTRIM(b.ID) AS VARCHAR(100)),a.Num1-POWER(2,ID-1),Counted+1,b.ID,a.LatestID2

FROM Array2 AS a 

	INNER  JOIN CTE AS b ON POWER(2,b.ID-1)&a.Num1>0 AND b.ID>a.LatestID1

UNION ALL

SELECT 

	CAST(a.IDs+'+'+RTRIM(b.ID) AS VARCHAR(100)),a.Num1-POWER(2,ID-1),Counted+1,a.LatestID1,b.ID

FROM Array2 AS a 

	INNER  JOIN CTE AS b ON POWER(2,b.ID-1)&a.Num1>0 AND b.ID<a.LatestID2

)

SELECT  IDs FROM Array2 WHERE Counted=@Counted ORDER BY IDs

/*

IDs

1,2,3

2,3+1

2+1,3

3+2+1

*/

Tiger_Zhao 2017-01-05

打赏
举报

[Quote=引用 55 楼 roy_88 的回复:]同顺序无关，按你的生成规则这一条也应该在结果里 [/Quote]
A,B+C和A,C+B是等价的（相同的组团方式不同的称呼），所以要滤掉一个啊。
你#56不是也把其中一个滤掉了。

中国风 2017-01-05

打赏
举报

换一种方法实现组合，用了按位的方法实现发现同楼上的结果集不一致，昨天贴了此方法删了回复，免造成楼主困惑。

昨天发的方法如下，当用4时组合为24,楼主可用以下方法测测是否正确
e.g.

DECLARE @Counted INT=3,@Num1 INT=0,@i INT=0;

WHILE @i<=@Counted

		SELECT @Num1=@Num1+POWER(2,@i-1),@i=@i+1



;WITH CTE

AS

(

SELECT 1 AS ID 

UNION ALL

SELECT ID+1 FROM CTE WHERE ID<@Counted

),Array2(IDs,Num1,Counted,LatestID)

AS

(

SELECT CAST (ID AS VARCHAR(100)),@Num1- POWER(2,ID-1),1,ID FROM CTE

UNION ALL

SELECT 

	CAST(a.IDs+','+RTRIM(b.ID) AS VARCHAR(100)),a.Num1-POWER(2,ID-1),Counted+1,b.ID

FROM Array2 AS a 

	INNER  JOIN CTE AS b ON POWER(2,b.ID-1)&a.Num1>0 AND b.ID>a.LatestID

UNION ALL

SELECT 

	CAST(a.IDs+'+'+RTRIM(b.ID) AS VARCHAR(100)),a.Num1-POWER(2,ID-1),Counted+1,b.ID

FROM Array2 AS a 

	INNER  JOIN CTE AS b ON POWER(2,b.ID-1)&a.Num1>0 AND b.ID<a.LatestID

)

SELECT  IDs FROM Array2 WHERE Counted=@Counted ORDER BY IDs



/*

IDs

1,2,3

1,3+2

2,3+1

2+1,3

3+1,2

3+2+1

*/

中国风 2017-01-05

打赏
举报

引用 54 楼 Tiger_Zhao 的回复:

等价的问题：有ABC三个玩家，可以任意组团（包括单干），有多少种组团方式？那么其中一种：A单干、B+C组团，怎么不成立了？没限定只能和相邻的人组团啊！

比如：这两条都在你结果集里成立

          0 A,B+C      C      B
          0 A+B,C      C      C

这条点解释不在结果集里，+号在前成立（A+C,B）

A,C+B

同顺序无关，按你的生成规则这一条也应该在结果里

Tiger_Zhao 2017-01-05

打赏
举报

等价的问题：有ABC三个玩家，可以任意组团（包括单干），有多少种组团方式？
那么其中一种：A单干、B+C组团，怎么不成立了？
没限定只能和相邻的人组团啊！

中国风 2017-01-05

打赏
举报

前面理解当作生成字符串去处理去，把符号的前后顺序不同当作不同记录了

用Tiger_Zhao方法 /* 逗号--分组分隔加号--组合分隔 */ 用按位方法处理，这样改用递增，#56递减，算法相同按位的方法比字符串处理效率高 e.g.

DECLARE @Counted INT=3;
;WITH CTE
AS
(
SELECT 1 AS ID 
UNION ALL
SELECT ID+1 FROM CTE WHERE ID<@Counted
),Array1(IDs,CHK,ID1,ID2,Counted)
AS
(
SELECT CAST(ID AS VARCHAR(100)),POWER(2,ID-1),ID,ID,1  FROM CTE
UNION ALL
SELECT CAST(a.IDs+','+RTRIM(b.ID) AS VARCHAR(100)),a.CHK+POWER(2,b.ID-1),b.ID,b.ID,a.Counted+1 FROM Array1 AS a INNER JOIN CTE AS b ON POWER(2,b.ID-1)&a.CHK=0 AND b.ID>a.ID1
UNION ALL 
SELECT CAST(a.IDs+'+'+RTRIM(b.ID) AS VARCHAR(100)),a.CHK+POWER(2,b.ID-1),a.ID1,b.ID,a.Counted+1 FROM Array1 AS a INNER JOIN CTE AS b ON POWER(2,b.ID-1)&a.CHK=0 AND b.ID>a.ID2
)
SELECT IDs FROM Array1 WHERE Counted=@Counted ORDER BY IDs

/*
IDs
1,2,3
1,2+3
1+2,3
1+2+3
1+3,2
*/

viva369 2017-01-04

打赏
举报

非常感谢（roy_88、Tiger_Zhao）2位，结果可以用，不过代码我还要花点时间研究下

viva369 2017-01-04

打赏
举报

引用 45 楼 roy_88 的回复:

用#24/#29方法测

@count是分组用的吗？输入@count=2，结果出来12行，结果是对的，不过有些重复，可能我之前没说清楚，例如 1+2,3和2+1,3结果是一样的，顺序无所谓，可以去重掉。谢谢！

viva369 2017-01-04

打赏
举报

引用 37 楼 KanzakiOrange 的回复:

IF OBJECT_ID('tempdb..#t') IS NOT NULL 
	DROP TABLE #t
CREATE table #t(id int)
insert #t select 1
insert #t select 2
insert #t select 3
insert #t select 4
insert #t select 5

IF OBJECT_ID('tempdb..#t1') IS NOT NULL 
	DROP TABLE #t1
IF OBJECT_ID('tempdb..#t2') IS NOT NULL 
	DROP TABLE #t2
	
DECLARE @Str VARCHAR(500)
DECLARE @GroupNr INT	--分组数
DECLARE @T1 AS TABLE (CurrentID INT,IDs VARCHAR(500),Display VARCHAR(500))

SELECT @Str = STUFF((SELECT '+'+RTRIM(a.id) FROM #t a FOR XML PATH('')),1,1,'')

;WITH CTE AS(
SELECT id AS CurrentID,CONVERT(VARCHAR(500),id) AS IDS,CONVERT(VARCHAR(500),REVERSE(STUFF(REVERSE(STUFF(REPLACE('+'+@Str+'+','+'+ RTRIM(id)+'+','+'),1,1,'')),1,1,''))) AS Display FROM #t
UNION ALL
SELECT  b.id,
		CONVERT(VARCHAR(500),a.IDs + ',' + RTRIM(b.id)),
		CONVERT(VARCHAR(500),REVERSE(STUFF(REVERSE(STUFF(REPLACE('+'+a.Display+'+','+'+ RTRIM(b.id)+'+','+'),1,1,'')),1,1,'')))
	FROM CTE a
		INNER JOIN #t b ON a.CurrentID < b.id)
SELECT *
	INTO #t1
	FROM CTE
		WHERE CTE.Display LIKE '%+%'
		   
;WITH CTE AS
(
SELECT STUFF(IDS,CHARINDEX(',',IDS),1,'+') AS IDS,Display, STUFF(IDS,CHARINDEX(',',IDS),1,'+') +','+Display　AS Display1 FROM #t1
	WHERE IDS LIKE '%,%'
UNION ALL
SELECT STUFF(IDS,CHARINDEX(',',IDS),1,'+') AS IDS,Display, STUFF(IDS,CHARINDEX(',',IDS),1,'+') +','+Display　AS Display1 FROM CTE
	WHERE IDS LIKE '%,%'
	)
SELECT CTE.Display1 
UNION ALL
SELECT IDS+','+Display
	FROM #t1
		WHERE CHARINDEX(',',IDS)=0
UNION ALL
SELECT @Str
UNION ALL
SELECT REPLACE(@Str,'+',',')

/*

(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)

(25 行受影响)

(37 行受影响)
Display1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4+5,1+2+3
3+4,1+2+5
3+5,1+2+4
3+4,5,1+2
2+3,1+4+5
2+4,1+3+5
2+5,1+3+4
2+4,5,1+3
2+3,4,1+5
2+3,5,1+4
1+2,3+4+5
1+3,2+4+5
1+4,2+3+5
1+5,2+3+4
1+4,5,2+3
1+3,4,2+5
1+3,5,2+4
1+2,3,4+5
1+2,4,3+5
1+2,5,3+4
1+2+5,3+4
1+2+4,3+5
1+2+3,4+5
1+3+5,2+4
1+3+4,2+5
1+4+5,2+3
2+3+5,1+4
2+3+4,1+5
2+4+5,1+3
3+4+5,1+2
1,2+3+4+5
2,1+3+4+5
3,1+2+4+5
4,1+2+3+5
5,1+2+3+4
1+2+3+4+5
1,2,3,4,5

(37 行受影响)


*/

你的代码运行报错了

中国风 2017-01-04