我想得到,从1~9这9个数字中任意取3个的所有的组合,
例如:
123 456 789
147 258 369
158 234 679
278 359 146
... ...然后把结果插入到表(ID,G1,G2,G3)中,ID是自增字段。请问Sql怎样写才最简洁、有效?先谢谢大家!
例如:
123 456 789
147 258 369
158 234 679
278 359 146
... ...然后把结果插入到表(ID,G1,G2,G3)中,ID是自增字段。请问Sql怎样写才最简洁、有效?先谢谢大家!
insert into tb values('1')
insert into tb values('2')
insert into tb values('3')
insert into tb values('4')
insert into tb values('5')
insert into tb values('6')
insert into tb values('7')
insert into tb values('8')
insert into tb values('9')
go--1第一位到第三位按从小到大
select a.id + b.id + c.id
from tb a, tb b , tb c
where a.id < b.id and b.id < c.id--2第一位到第三位随机
select a.id + b.id + c.id
from tb a, tb b , tb cdrop table tb
@RandMin int, @RandMax int--设置随机值的最小和最大值
SELECT
@RandMin = 100,
@RandMax = 999-- 生成随机数
SELECT TOP 100
RandValue1 = ABS(CHECKSUM(NEWID())) % (1 + @RandMax - @RandMin) + @RandMin,
RandValue2 = ABS(CHECKSUM(NEWID())) % (1 + @RandMax - @RandMin) + @RandMin,
RandValue3 = ABS(CHECKSUM(NEWID())) % (1 + @RandMax - @RandMin) + @RandMin
FROM sys.objects O1, sys.objects O2
create table tb(id varchar(1))
insert into tb values('1')
insert into tb values('2')
insert into tb values('3')
insert into tb values('4')
insert into tb values('5')
insert into tb values('6')
insert into tb values('7')
insert into tb values('8')
insert into tb values('9')
create table result(ID int identity,G1 varchar(3),G2 varchar(3),G3 varchar(3))
go--1第一位到第三位按从小到大select (px-1)/3 + 1 id,
max(case (px-1)%3 when 0 then pid else '' end) G1,
max(case (px-1)%3 when 1 then pid else '' end) G2,
max(case (px-1)%3 when 2 then pid else '' end) G3
from
(
select n.* , px = (select count(1) from
(
select a.id + b.id + c.id pid from tb a, tb b , tb c where a.id < b.id and b.id < c.id
) m
where pid < n.pid) + 1 from
(
select a.id + b.id + c.id pid from tb a, tb b , tb c where a.id < b.id and b.id < c.id
) n
) o
group by (px-1)/3 + 1drop table tb,result/*
id G1 G2 G3
----------- ---- ---- ----
1 123 124 125
2 126 127 128
3 129 134 135
4 136 137 138
5 139 145 146
6 147 148 149
7 156 157 158
8 159 167 168
9 169 178 179
10 189 234 235
11 236 237 238
12 239 245 246
13 247 248 249
14 256 257 258
15 259 267 268
16 269 278 279
17 289 345 346
18 347 348 349
19 356 357 358
20 359 367 368
21 369 378 379
22 389 456 457
23 458 459 467
24 468 469 478
25 479 489 567
26 568 569 578
27 579 589 678
28 679 689 789(所影响的行数为 28 行)
*/
先谢谢你的回答,dawugui!
我想要的结果是,把9个数字分成N组3个组合,
如下四组:
123 456 789
147 258 369
158 234 679
278 359 146
...但,123 124 125 不是一组,因为他们合起来没有9个数字。
insert into tb values('1')
insert into tb values('2')
insert into tb values('3')
insert into tb values('4')
insert into tb values('5')
insert into tb values('6')
insert into tb values('7')
insert into tb values('8')
insert into tb values('9')
goselect a.id + b.id + c.id G1 ,
d.id + e.id + f.id G2 ,
a.id + b.id + c.id G3
from tb a, tb b , tb c , tb d , tb e , tb f , tb g , tb h , tb i
where b.id <> a.id and c.id <> b.id and d.id <> c.id and e.id <> d.id and f.id <> e.id and g.id <> f.id and h.id <> g.id and i.id <> g.iddrop table tb
insert into tb values('1')
insert into tb values('2')
insert into tb values('3')
insert into tb values('4')
insert into tb values('5')
insert into tb values('6')
insert into tb values('7')
insert into tb values('8')
insert into tb values('9')
goselect a.id + b.id + c.id G1 ,
d.id + e.id + f.id G2 ,
g.id + h.id + i.id G3
from tb a, tb b , tb c , tb d , tb e , tb f , tb g , tb h , tb i
where b.id <> a.id and
c.id <> b.id and c.id <> a.id and
d.id <> c.id and c.id <> b.id and c.id <> a.id and
e.id <> d.id and d.id <> c.id and c.id <> b.id and c.id <> a.id and
f.id <> e.id and e.id <> d.id and d.id <> c.id and c.id <> b.id and c.id <> a.id and
g.id <> f.id and f.id <> e.id and e.id <> d.id and d.id <> c.id and c.id <> b.id and c.id <> a.id and
h.id <> g.id and g.id <> f.id and f.id <> e.id and e.id <> d.id and d.id <> c.id and c.id <> b.id and c.id <> a.id and
i.id <> g.id and h.id <> g.id and g.id <> f.id and f.id <> e.id and e.id <> d.id and d.id <> c.id and c.id <> b.id and c.id <> a.iddrop table tb
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#temp'))
drop table #temp
select A.R +' '+ B.R +' '+ C.R + ' ' + D.R [G] into #temp
from TNum A,TNum B,TNum C,TNum D
where A.R<=16 and B.R<=16 and C.R<=16 and D.R<=16
and A.R<B.R and B.R<C.R and C.R<D.Rselect * from #tempselect COUNT(*)--A.G [G1],B.G [G2],C.G [G3],D.G [G4]
from #temp A,#temp B,#temp C,#temp D
where A.G<B.G and B.G<C.G and C.G<D.G
and CHARINDEX(substring(A.G,1,2),B.G)=0 and CHARINDEX(substring(A.G,4,2),B.G)=0 and CHARINDEX(substring(A.G,7,2),B.G)=0 and CHARINDEX(substring(A.G,10,2),B.G)=0
and CHARINDEX(substring(A.G,1,2),C.G)=0 and CHARINDEX(substring(A.G,4,2),C.G)=0 and CHARINDEX(substring(A.G,7,2),C.G)=0 and CHARINDEX(substring(A.G,10,2),C.G)=0
and CHARINDEX(substring(A.G,1,2),D.G)=0 and CHARINDEX(substring(A.G,4,2),D.G)=0 and CHARINDEX(substring(A.G,7,2),D.G)=0 and CHARINDEX(substring(A.G,10,2),D.G)=0
and CHARINDEX(substring(B.G,1,2),C.G)=0 and CHARINDEX(substring(B.G,4,2),C.G)=0 and CHARINDEX(substring(B.G,7,2),C.G)=0 and CHARINDEX(substring(B.G,10,2),C.G)=0
and CHARINDEX(substring(B.G,1,2),D.G)=0 and CHARINDEX(substring(B.G,4,2),D.G)=0 and CHARINDEX(substring(B.G,7,2),D.G)=0 and CHARINDEX(substring(B.G,10,2),D.G)=0
and CHARINDEX(substring(C.G,1,2),D.G)=0 and CHARINDEX(substring(C.G,4,2),D.G)=0 and CHARINDEX(substring(C.G,7,2),D.G)=0 and CHARINDEX(substring(C.G,10,2),D.G)=0
Order by 1能够实现,就是太慢了。。
insert into @tb values('1')
insert into @tb values('2')
insert into @tb values('3')
insert into @tb values('4')
insert into @tb values('5')
insert into @tb values('6')
insert into @tb values('7')
insert into @tb values('8')
insert into @tb values('9')
select a.id + b.id + c.id G1 ,
d.id + e.id + f.id G2 ,
g.id + h.id + i.id G3
from @tb a, @tb b , @tb c , @tb d , @tb e , @tb f , @tb g , @tb h , @tb i
where a.id<>b.id and a.id<>c.id and a.id<>d.id and a.id<>e.id and a.id<>f.id and a.id<>g.id and a.id<>h.id and a.id<>i.id and
b.id<>c.id and b.id<>d.id and b.id<>e.id and b.id<>f.id and b.id<>g.id and b.id<>h.id and b.id<>i.id and
c.id<>d.id and c.id<>e.id and c.id<>f.id and c.id<>g.id and c.id<>h.id and c.id<>i.id and
d.id<>e.id and d.id<>f.id and d.id<>g.id and d.id<>h.id and d.id<>i.id and
e.id<>f.id and e.id<>g.id and e.id<>h.id and e.id<>i.id and
f.id<>g.id and f.id<>h.id and f.id<>i.id and
g.id<>h.id and g.id<>i.id and
h.id<>i.id
--如果你不需要第一位<第二位,第二位小于第三位,去掉最后的条件(a.id < b.id and b.id <c.id) and (d.id < e.id and e.id < f.id) and (g.id <.h.id and h.id < i.id)create table tb(id varchar(1))
insert into tb values('1')
insert into tb values('2')
insert into tb values('3')
insert into tb values('4')
insert into tb values('5')
insert into tb values('6')
insert into tb values('7')
insert into tb values('8')
insert into tb values('9')
goselect a.id + b.id + c.id G1 ,
d.id + e.id + f.id G2 ,
g.id + h.id + i.id G3
from tb a, tb b , tb c , tb d , tb e , tb f , tb g , tb h , tb i
where b.id <> a.id and
c.id <> b.id and c.id <> a.id and
d.id <> c.id and d.id <> b.id and d.id <> a.id and
e.id <> d.id and e.id <> c.id and e.id <> b.id and e.id <> a.id and
f.id <> e.id and f.id <> d.id and f.id <> c.id and f.id <> b.id and f.id <> a.id and
g.id <> f.id and g.id <> e.id and g.id <> d.id and g.id <> c.id and g.id <> b.id and g.id <> a.id and
h.id <> g.id and h.id <> f.id and h.id <> e.id and h.id <> d.id and h.id <> c.id and h.id <> b.id and h.id <> a.id and
i.id <> h.id and i.id <> g.id and i.id <> f.id and i.id <> e.id and i.id <> d.id and i.id <> c.id and i.id <> b.id and i.id <> a.id and
(a.id < b.id and b.id <c.id) and (d.id < e.id and e.id < f.id) and (g.id <.h.id and h.id < i.id)
drop table tb
G1 G2 G3
---- ---- ----
123 789 456
123 689 457
123 679 458
123 678 459
123 589 467
123 579 468
123 578 469
123 569 478
123 568 479
123 567 489
123 489 567
123 479 568
123 478 569
123 469 578
123 468 579
123 467 589
123 459 678
123 458 679
123 457 689
123 456 789
124 789 356
124 689 357
124 679 358
124 678 359
124 589 367
124 579 368
124 578 369
124 569 378
124 568 379
124 567 389
124 389 567
124 379 568
124 378 569
124 369 578
124 368 579
124 367 589
124 359 678
124 358 679
124 357 689
124 356 789
125 789 346
125 689 347
125 679 348
125 678 349
125 489 367
125 479 368
125 478 369
125 469 378
125 468 379
125 467 389
125 389 467
125 379 468
125 378 469
125 369 478
125 368 479
125 367 489
125 349 678
125 348 679
125 347 689
125 346 789
126 789 345
126 589 347
126 579 348
126 578 349
126 489 357
126 479 358
126 478 359
126 459 378
126 458 379
126 457 389
126 389 457
126 379 458
126 378 459
126 359 478
126 358 479
126 357 489
126 349 578
126 348 579
126 347 589
126 345 789
127 689 345
127 589 346
127 569 348
127 568 349
127 489 356
127 469 358
127 468 359
127 459 368
127 458 369
127 456 389
127 389 456
127 369 458
127 368 459
127 359 468
127 358 469
127 356 489
127 349 568
127 348 569
127 346 589
127 345 689
128 679 345
128 579 346
128 569 347
128 567 349
128 479 356
128 469 357
128 467 359
128 459 367
128 457 369
128 456 379
128 379 456
128 369 457
128 367 459
128 359 467
128 357 469
128 356 479
128 349 567
128 347 569
128 346 579
128 345 679
129 678 345
129 578 346
129 568 347
129 567 348
129 478 356
129 468 357
129 467 358
129 458 367
129 457 368
129 456 378
129 378 456
129 368 457
129 367 458
129 358 467
129 357 468
129 356 478
129 348 567
129 347 568
129 346 578
129 345 678
134 789 256
134 689 257
134 679 258
134 678 259
134 589 267
134 579 268
134 578 269
134 569 278
134 568 279
134 567 289
134 289 567
134 279 568
134 278 569
134 269 578
134 268 579
134 267 589
134 259 678
134 258 679
134 257 689
134 256 789
135 789 246
135 689 247
135 679 248
135 678 249
135 489 267
135 479 268
135 478 269
135 469 278
135 468 279
135 467 289
135 289 467
135 279 468
135 278 469
135 269 478
135 268 479
135 267 489
135 249 678
135 248 679
135 247 689
135 246 789
136 789 245
136 589 247
136 579 248
136 578 249
136 489 257
136 479 258
136 478 259
136 459 278
136 458 279
136 457 289
136 289 457
136 279 458
136 278 459
136 259 478
136 258 479
136 257 489
136 249 578
136 248 579
136 247 589
136 245 789
137 689 245
137 589 246
137 569 248
...
(所影响的行数为 1680 行)
*/
---- ---- ----
198 765 432
189 765 432
197 865 432
179 865 432
187 965 432
178 965 432
198 675 432
189 675 432
196 875 432
169 875 432
186 975 432
...
942 135 678
924 135 678
941 235 678
914 235 678
921 435 678
912 435 678
932 145 678
923 145 678
931 245 678
913 245 678
921 345 678
912 345 678(所影响的行数为 362880 行)运行时间 38秒
RETURNS @Temp TABLE (Value NVARCHAR(50),
MaxIndex INT)
AS
BEGIN
DECLARE @IndexTemp TABLE ([Index] INT) DECLARE @INDEX INT SET @INDEX = 0
WHILE (@INDEX < @M)
BEGIN
INSERT @IndexTemp VALUES (@INDEX)
SET @INDEX = @INDEX + 1
END IF (@N = 1)
BEGIN
INSERT @Temp SELECT [Index], [Index] FROM @IndexTemp
END
ELSE
BEGIN
SET @INDEX = @N - 1;
INSERT @Temp SELECT a.Value + ' ' + CONVERT(NVARCHAR(50), b.[Index]), b.[Index] FROM SelectNFromM(@INDEX, @M) AS a, @IndexTemp b WHERE b.[Index] > a.MaxIndex
END RETURN
END
GO
SELECT Value FROM dbo.SelectNFromM (3, 9)drop function SelectNFromM/*
Value
--------------------------------------------------
0 1 2
0 1 3
0 2 3
1 2 3
0 1 4
......
5 7 8
6 7 8(所影响的行数为 84 行)*/
go
declare @n table (n int)
declare @i int set @i=1 while @i < 10 begin insert into @n values (@i) set @i=@i+1 end
-- 全部组合
insert into #fulllist
select
n1=n1.n*100+n2.n*10+n3.n
,n2=n4.n*100+n5.n*10+n6.n
,n3=n7.n*100+n8.n*10+n9.n
from @n n1, @n n2, @n n3, @n n4, @n n5, @n n6, @n n7, @n n8, @n n9
where n1.n!=n2.n and n1.n!=n3.n and n1.n!=n4.n and n1.n!=n5.n and n1.n!=n6.n and n1.n!=n7.n and n1.n!=n8.n and n1.n!=n9.n
and n2.n!=n3.n and n2.n!=n4.n and n2.n!=n5.n and n2.n!=n6.n and n2.n!=n7.n and n2.n!=n8.n and n2.n!=n9.n
and n3.n!=n4.n and n3.n!=n5.n and n3.n!=n6.n and n3.n!=n7.n and n3.n!=n8.n and n3.n!=n9.n
and n4.n!=n5.n and n4.n!=n6.n and n4.n!=n7.n and n4.n!=n8.n and n4.n!=n9.n
and n5.n!=n6.n and n5.n!=n7.n and n5.n!=n8.n and n5.n!=n9.n
and n6.n!=n7.n and n6.n!=n8.n and n6.n!=n9.n
and n7.n!=n8.n and n7.n!=n9.n
and n8.n!=n9.n
go-- 取随机数
declare @i int set @i=0
declare @id int
while @i<10 begin
set @id=(SELECT RAND()*count(1) from #fulllist)
select * from #fulllist where id=@id
set @i=@i+1
end
go
drop table #fulllist
go
n1 n2 n3 id
149 675 238 31000n1 n2 n3 id
947 561 238 359416n1 n2 n3 id
172 968 354 11723n1 n2 n3 id
817 349 652 289912n1 n2 n3 id
749 368 215 262166n1 n2 n3 id
753 248 619 277789n1 n2 n3 id
327 961 458 113292n1 n2 n3 id
382 451 697 110655n1 n2 n3 id
362 785 149 117407n1 n2 n3 id
617 439 258 234946
create table tb(id varchar(1))
insert into tb values('1')
insert into tb values('2')
insert into tb values('3')
insert into tb values('4')
insert into tb values('5')
insert into tb values('6')
insert into tb values('7')
insert into tb values('8')
insert into tb values('9')
go
select '1'+L2.id+L3.id as num1, M1.id+M2.id+M3.id as num2, R1.id+R2.id+R3.id as num3
from tb L2 join tb L3 on L2.id<L3.id
, tb M1 join tb M2 on M1.id<M2.id join tb M3 on M2.id<M3.id
, tb R1 join tb R2 on R1.id<R2.id join tb R3 on R2.id<R3.id
where L2.id>='2' and M1.id>='2' and M2.id>='2' and R2.id>='2'
and M1.id<R1.id
and L2.id not in (M1.id, M2.id, M3.id, R1.id, R2.id, R3.id)
and L3.id not in (M1.id, M2.id, M3.id, R1.id, R2.id, R3.id)
and M1.id not in (M2.id, M3.id, R1.id, R2.id, R3.id)
and M2.id not in (R1.id, R2.id, R3.id)
and M3.id not in (R1.id, R2.id, R3.id)
and R1.id not in (R2.id, R3.id)/*
num1 num2 num3
---- ---- ----
189 234 567
179 234 568
178 234 569
169 234 578
168 234 579
167 234 589
159 234 678
158 234 679
157 234 689
156 234 789
189 235 467
179 235 468
178 235 469
169 235 478
168 235 479
167 235 489
149 235 678
148 235 679
147 235 689
146 235 789
189 236 457
179 236 458
178 236 459
159 236 478
158 236 479
157 236 489
149 236 578
148 236 579
147 236 589
145 236 789
189 237 456
169 237 458
168 237 459
159 237 468
158 237 469
156 237 489
149 237 568
148 237 569
146 237 589
145 237 689
179 238 456
169 238 457
167 238 459
159 238 467
157 238 469
156 238 479
149 238 567
147 238 569
146 238 579
145 238 679
178 239 456
168 239 457
167 239 458
158 239 467
157 239 468
156 239 478
148 239 567
147 239 568
146 239 578
145 239 678
189 245 367
179 245 368
178 245 369
169 245 378
168 245 379
167 245 389
139 245 678
138 245 679
137 245 689
136 245 789
189 246 357
179 246 358
178 246 359
159 246 378
158 246 379
157 246 389
139 246 578
138 246 579
137 246 589
135 246 789
189 247 356
169 247 358
168 247 359
159 247 368
158 247 369
156 247 389
139 247 568
138 247 569
136 247 589
135 247 689
179 248 356
169 248 357
167 248 359
159 248 367
157 248 369
156 248 379
139 248 567
137 248 569
136 248 579
135 248 679
178 249 356
168 249 357
167 249 358
158 249 367
157 249 368
156 249 378
138 249 567
137 249 568
136 249 578
135 249 678
129 345 678
128 345 679
127 345 689
126 345 789
129 346 578
128 346 579
127 346 589
125 346 789
129 347 568
128 347 569
126 347 589
125 347 689
129 348 567
127 348 569
126 348 579
125 348 679
128 349 567
127 349 568
126 349 578
125 349 678
189 256 347
179 256 348
178 256 349
149 256 378
148 256 379
147 256 389
139 256 478
138 256 479
137 256 489
134 256 789
189 257 346
169 257 348
168 257 349
149 257 368
148 257 369
146 257 389
139 257 468
138 257 469
136 257 489
134 257 689
179 258 346
169 258 347
167 258 349
149 258 367
147 258 369
146 258 379
139 258 467
137 258 469
136 258 479
134 258 679
178 259 346
168 259 347
167 259 348
148 259 367
147 259 368
146 259 378
138 259 467
137 259 468
136 259 478
134 259 678
129 356 478
128 356 479
127 356 489
124 356 789
129 357 468
128 357 469
126 357 489
124 357 689
129 358 467
127 358 469
126 358 479
124 358 679
128 359 467
127 359 468
126 359 478
124 359 678
123 456 789
123 457 689
123 458 679
123 459 678
189 267 345
159 267 348
158 267 349
149 267 358
148 267 359
145 267 389
139 267 458
138 267 459
135 267 489
134 267 589
179 268 345
159 268 347
157 268 349
149 268 357
147 268 359
145 268 379
139 268 457
137 268 459
135 268 479
134 268 579
178 269 345
158 269 347
157 269 348
148 269 357
147 269 358
145 269 378
138 269 457
137 269 458
135 269 478
134 269 578
129 367 458
128 367 459
125 367 489
124 367 589
129 368 457
127 368 459
125 368 479
124 368 579
128 369 457
127 369 458
125 369 478
124 369 578
123 467 589
123 468 579
123 469 578
169 278 345
159 278 346
156 278 349
149 278 356
146 278 359
145 278 369
139 278 456
136 278 459
135 278 469
134 278 569
168 279 345
158 279 346
156 279 348
148 279 356
146 279 358
145 279 368
138 279 456
136 279 458
135 279 468
134 279 568
129 378 456
126 378 459
125 378 469
124 378 569
128 379 456
126 379 458
125 379 468
124 379 568
123 478 569
123 479 568
167 289 345
157 289 346
156 289 347
147 289 356
146 289 357
145 289 367
137 289 456
136 289 457
135 289 467
134 289 567
127 389 456
126 389 457
125 389 467
124 389 567
123 489 567(280 row(s) affected)
*/drop table tb
--c (p 10 3) 3
--p 10 3 = 10*9*8 = 720
--c 720 3 = 720*719*718/3! =
select 720*719*718/6
--结果共有:61949040个怎么不慢。
insert into tb values('1')
insert into tb values('2')
insert into tb values('3')
insert into tb values('4')
insert into tb values('5')
insert into tb values('6')
insert into tb values('7')
insert into tb values('8')
insert into tb values('9')select distinct a.id,b.id,c.id from tb a,tb b,tb c where a.id<b.id and c.id>b.id
insert into table(G1,G2,G3) select distinct a.id,b.id,c.id from tb a,tb b,tb c where a.id<b.id and c.id>b.id
drop table tb
insert into tb values('1')
insert into tb values('2')
insert into tb values('3')
insert into tb values('4')
insert into tb values('5')
insert into tb values('6')
insert into tb values('7')
insert into tb values('8')
insert into tb values('9')
go
--靠
--c (p 9 3) 3
--p 9 3 = 9*8*7 = 504
--c 504 3 = 504*503*502/3! =
select 504*503*502/6
--结果共有:21210504 个怎么不慢。
declare @tb table(id char(3))
declare @t table(id1 char(3),id2 char(3),id3 char(3))
insert @tb select a.id+b.id+c.id from tb a,tb b ,tb c where a.id <> b.id and a.id <> c.id and b.id <> c.id
insert @t select a.id,b.id,c.id from @tb a,@tb b ,@tb c where a.id <> b.id and a.id <> c.id and b.id <> c.id
select count(*) from @t
/*
-----------
21210504
*/
go
drop table tb
c (p 9 3) 3
p 9 3 = 9*8*7 = 504
c 504 3 = 504*503*502/3! = 21210504
结果共有:21210504 个怎么不慢。declare @t1 table(id varchar(1))
insert into @t1 values('1')
insert into @t1 values('2')
insert into @t1 values('3')
insert into @t1 values('4')
insert into @t1 values('5')
insert into @t1 values('6')
insert into @t1 values('7')
insert into @t1 values('8')
insert into @t1 values('9')
declare @t2 table(id char(3))
declare @t3 table(id1 char(3),id2 char(3),id3 char(3))
insert @t2 select a.id+b.id+c.id from @t1 a,@t1 b ,@t1 c where a.id <> b.id and a.id <> c.id and b.id <> c.id
select count(*) from @t2
select count(*) from @t2 a,@t2 b,@t2 c where a.id < b.id and b.id < c.id
--下面不敢执行,谁的机器好可以试试
--insert @t3 select * from @t2 a,@t2 b,@t2 c where a.id < b.id and b.id < c.id
/*结果
-----------
504
-----------
21210504
*/
insert into @t values('1')
insert into @t values('2')
insert into @t values('3')
insert into @t values('4')
insert into @t values('5')
insert into @t values('6')
insert into @t values('7')
insert into @t values('8')
insert into @t values('9')
--select a1.id+a2.id+a3.id,a4.id+a5.id+a6.id,a7.id+a8.id+a9.id
select count(*)
from @t a1
join @t a2 on a1.id <> a2.id
join @t a3 on a1.id <> a3.id and a2.id <> a3.id
join @t a4 on a1.id <> a4.id and a2.id <> a4.id and a3.id <> a4.id
join @t a5 on a1.id <> a5.id and a2.id <> a5.id and a3.id <> a5.id and a4.id <> a5.id
join @t a6 on a1.id <> a6.id and a2.id <> a6.id and a3.id <> a6.id and a4.id <> a6.id and a5.id <> a6.id
join @t a7 on a1.id <> a7.id and a2.id <> a7.id and a3.id <> a7.id and a4.id <> a7.id and a5.id <> a7.id and a6.id <> a7.id
join @t a8 on a1.id <> a8.id and a2.id <> a8.id and a3.id <> a8.id and a4.id <> a8.id and a5.id <> a8.id and a6.id <> a8.id and a7.id <> a8.id
join @t a9 on a1.id <> a9.id and a2.id <> a9.id and a3.id <> a9.id and a4.id <> a9.id and a5.id <> a9.id and a6.id <> a9.id and a7.id <> a9.id and a8.id <> a9.id-----------
--362880
insert into tb values(1);
insert into tb values(2);
insert into tb values(3);
insert into tb values(4);
insert into tb values(5);
insert into tb values(6);
insert into tb values(7);
insert into tb values(8);
insert into tb values(9);select distinct a.id||b.id||c.id m,g.id||h.id||i.id n,j.id||k.id||l.id q from tb a,tb b,tb c,tb g,tb h,tb i,tb j,tb k,tb l where
(a.id<b.id and c.id>b.id)
and (g.id<h.id and i.id>h.id)
and (j.id<k.id and l.id>k.id)
and (g.id>a.id and g.id<>b.id and g.id<>c.id)
and (h.id<>a.id and h.id<>b.id and h.id<>c.id)
and (i.id<>a.id and i.id<>b.id and i.id<>c.id)
and (j.id>a.id and j.id<>b.id and j.id<>c.id)
and (k.id<>a.id and k.id<>b.id and k.id<>c.id)
and (l.id<>a.id and l.id<>b.id and l.id<>c.id)
and (j.id>g.id and j.id<>h.id and j.id<>i.id)
and (k.id<>g.id and k.id<>h.id and k.id<>i.id)
and (l.id<>g.id and l.id<>h.id and l.id<>i.id)
drop table tb;
select distinct a.id||b.id||c.id m,g.id||h.id||i.id n,j.id||k.id||l.id q from tb a,tb b,tb c,tb g,tb h,tb i,tb j,tb k,tb l where
(a.id<b.id and c.id>b.id)
and (g.id<h.id and i.id>h.id)
and (j.id<k.id and l.id>k.id)
and (g.id<>a.id and g.id<>b.id and g.id<>c.id)
and (h.id<>a.id and h.id<>b.id and h.id<>c.id)
and (i.id<>a.id and i.id<>b.id and i.id<>c.id)
and (j.id<>a.id and j.id<>b.id and j.id<>c.id)
and (k.id<>a.id and k.id<>b.id and k.id<>c.id)
and (l.id<>a.id and l.id<>b.id and l.id<>c.id)
and (j.id<>g.id and j.id<>h.id and j.id<>i.id)
and (k.id<>g.id and k.id<>h.id and k.id<>i.id)
and (l.id<>g.id and l.id<>h.id and l.id<>i.id)
你看这个就可以了select distinct a.id ¦ ¦b.id ¦ ¦c.id m,g.id ¦ ¦h.id ¦ ¦i.id n,j.id ¦ ¦k.id ¦ ¦l.id q from tb a,tb b,tb c,tb g,tb h,tb i,tb j,tb k,tb l where
(a.id <>b.id and c.id<>b.id and c.id<>a.id)
and (g.id <>h.id and i.id<>h.id and i.id<>g.id)
and (j.id <>k.id and l.id<>k.id and l.id<>j.id)
and (g.id <>a.id and g.id <>b.id and g.id <>c.id)
and (h.id <>a.id and h.id <>b.id and h.id <>c.id)
and (i.id <>a.id and i.id <>b.id and i.id <>c.id)
and (j.id <>a.id and j.id <>b.id and j.id <>c.id)
and (k.id <>a.id and k.id <>b.id and k.id <>c.id)
and (l.id <>a.id and l.id <>b.id and l.id <>c.id)
and (j.id <>g.id and j.id <>h.id and j.id <>i.id)
and (k.id <>g.id and k.id <>h.id and k.id <>i.id)
and (l.id <>g.id and l.id <>h.id and l.id <>i.id)
程序员在深圳JAVA群4247660
程序员在深圳c++群15195967
程序员在深圳.NET群Ⅱ:12203296
程序员在深圳TCP/IP协议栈开发:16956462
程序员在深圳JS & AJAX群:12578377
程序员在深圳英语学习群:23864353
深序员在深圳VB:11055959
程序员在深圳c++Ⅱ17409451
程序员在深圳c++群15195967
程序员在深圳嵌入式开发群37489763
程序员在深圳移动开发群31501597
程序员在深圳创业群33653422不限专业分类:
高级群:17538442
第三群:2650485
第五群:29537639
第四群:28702746
第六群:10590618
第七群:10543585
第八群:12006492
第九群:19063074
第十群:2883885
第十一群:25460595
第十二群:9663807深圳程序员QQ群联盟成立两年多,拥有三十个以上的QQ群,人数达二千多人,有30%以上的成员的经验丰富的老手,包括国内外顶级大公司的成员(如微软、IBM,SUN,华为)、国内著名高校和研究院成员,和有丰富实践经验的高级程序(包括参加过上亿元的项目的架构师),有很热爱技术的成员(包括自己写过嵌入式操作系统),还有少数女程序员。现推介如下QQ群,如有兴趣速速加入:深程高级群I:17538442 深程高级群II:7120862 (深程高级群不欢迎新手,如果在深圳,月薪6K以下的别加入) c++:15195967 .NET:12203296 mobile:31501597嵌入式:37489763 JAVA:4247660
——————————————————————————————————————————
希望大家不要认为群能给你带来什么,这只是一个平台,让同等水平的程序员有个交流的机会或许能得到一点信息或许能带来一点启发。*****************************************************************************
select 3 union select 4 union all select 5 union select 6 union all
select 7 union select 8 union all select 9 select a.a+b.a+c.a as result from table_Pqs a,table_Pqs b,table_Pqs c
where a.a!=b.a and a.a!=c.a and b.a!=c.a
drop table table_Pqs
----------------------------------
result
------
132
142
152
162
172
182
192
123
143
153
163
173
183
193
124
134
154
164
174
184
194
125
135
145
165
175
185
195
126
136
146
156
176
186
196
127
137
147
157
167
187
197
128
138
148
158
168
178
198
129
139
149
159
169
179
189
231
241
251
261
271
281
291
213
243
253
263
273
283
293
214
234
254
264
274
284
294
215
235
245
265
275
285
295
216
236
246
256
276
286
296
217
237
247
257
267
287
297
218
238
248
258
268
278
298
219
239
249
259
269
279
289
321
341
351
361
371
381
391
312
342
352
362
372
382
392
314
324
354
364
374
384
394
315
325
345
365
375
385
395
316
326
346
356
376
386
396
317
327
347
357
367
387
397
318
328
348
358
368
378
398
319
329
349
359
369
379
389
421
431
451
461
471
481
491
412
432
452
462
472
482
492
413
423
453
463
473
483
493
415
425
435
465
475
485
495
416
426
436
456
476
486
496
417
427
437
457
467
487
497
418
428
438
458
468
478
498
419
429
439
459
469
479
489
521
531
541
561
571
581
591
512
532
542
562
572
582
592
513
523
543
563
573
583
593
514
524
534
564
574
584
594
516
526
536
546
576
586
596
517
527
537
547
567
587
597
518
528
538
548
568
578
598
519
529
539
549
569
579
589
621
631
641
651
671
681
691
612
632
642
652
672
682
692
613
623
643
653
673
683
693
614
624
634
654
674
684
694
615
625
635
645
675
685
695
617
627
637
647
657
687
697
618
628
638
648
658
678
698
619
629
639
649
659
679
689
721
731
741
751
761
781
791
712
732
742
752
762
782
792
713
723
743
753
763
783
793
714
724
734
754
764
784
794
715
725
735
745
765
785
795
716
726
736
746
756
786
796
718
728
738
748
758
768
798
719
729
739
749
759
769
789
821
831
841
851
861
871
891
812
832
842
852
862
872
892
813
823
843
853
863
873
893
814
824
834
854
864
874
894
815
825
835
845
865
875
895
816
826
836
846
856
876
896
817
827
837
847
857
867
897
819
829
839
849
859
869
879
921
931
941
951
961
971
981
912
932
942
952
962
972
982
913
923
943
953
963
973
983
914
924
934
954
964
974
984
915
925
935
945
965
975
985
916
926
936
946
956
976
986
917
927
937
947
957
967
987
918
928
938
948
958
968
978(所影响的行数为 504 行)