6,129
社区成员
发帖
与我相关
我的任务
分享sqlserver的t-sql好像不支持 显式的并行语法？解8皇后引发的疑问
sqlserver的t-sql好像不支持 显式的并行语法？
比如：
select * from tb1
union all
select * from tb2
union all
select * from tb3
如果能先同时并行执行3个select，再把它们的结果合起来，这样 比分别执行3个select，再把它们的结果合起来 要省掉2个select的时间了
这样，可以把解8皇后的一个sql，分拆为8个子sql的union all
子sql就是固定第一行的位置，其它7行才是全排列。。。。。。这样，如果子sql能并发执行，效率也就高了很多：
原来是8个8^7+1次union all，现在是1个8^7+1次union all
但是，好像没有语法指定子sql必须并行执行。。。。。。
实际在sql2k5下试验了一下，union all的子查询确没有被并发执行：
2cpu x 4核 x 超线程=16 （cpu），执行时，cpu总是只有6%（=1/16）的使用量
比如：
select * from tb1
union all
select * from tb2
union all
select * from tb3
如果能先同时并行执行3个select，再把它们的结果合起来，这样 比分别执行3个select，再把它们的结果合起来 要省掉2个select的时间了
这样，可以把解8皇后的一个sql，分拆为8个子sql的union all
子sql就是固定第一行的位置，其它7行才是全排列。。。。。。这样，如果子sql能并发执行，效率也就高了很多：
原来是8个8^7+1次union all，现在是1个8^7+1次union all
但是，好像没有语法指定子sql必须并行执行。。。。。。
实际在sql2k5下试验了一下，union all的子查询确没有被并发执行：
2cpu x 4核 x 超线程=16 （cpu），执行时，cpu总是只有6%（=1/16）的使用量
declare @c table (c1 tinyint,c2 tinyint,c3 tinyint,c4 tinyint,c5 tinyint,c6 tinyint,c7 tinyint,c8 tinyint,c9 tinyint,c10 tinyint,c11 tinyint,c12 tinyint)
--该表做笛卡尔积
declare @t table (p tinyint)
insert into @t
select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
insert @c
select
*
from @t t1
cross join @t t2 cross join @t t3 cross join @t t4 cross join @t t5 cross join @t t6 cross join @t t7 cross join @t t8 cross join @t t9 cross join @t t10 cross join @t t11 cross join @t t12
where t1.p=1
--竖
and t1.p<>t2.p and t1.p<>t3.p and t1.p<>t4.p and t1.p<>t5.p and t1.p<>t6.p and t1.p<>t7.p and t1.p<>t8.p and t1.p<>t9.p and t1.p<>t10.p and t1.p<>t11.p and t1.p<>t12.p
and t2.p<>t3.p and t2.p<>t4.p and t2.p<>t5.p and t2.p<>t6.p and t2.p<>t7.p and t2.p<>t8.p and t2.p<>t9.p and t2.p<>t10.p and t2.p<>t11.p and t2.p<>t12.p
and t3.p<>t4.p and t3.p<>t5.p and t3.p<>t6.p and t3.p<>t7.p and t3.p<>t8.p and t3.p<>t9.p and t3.p<>t10.p and t3.p<>t11.p and t3.p<>t12.p
and t4.p<>t5.p and t4.p<>t6.p and t4.p<>t7.p and t4.p<>t8.p and t4.p<>t9.p and t4.p<>t10.p and t4.p<>t11.p and t4.p<>t12.p
and t5.p<>t6.p and t5.p<>t7.p and t5.p<>t8.p and t5.p<>t9.p and t5.p<>t10.p and t5.p<>t11.p and t5.p<>t12.p
and t6.p<>t7.p and t6.p<>t8.p and t6.p<>t9.p and t6.p<>t10.p and t6.p<>t11.p and t6.p<>t12.p
and t7.p<>t8.p and t7.p<>t9.p and t7.p<>t10.p and t7.p<>t11.p and t7.p<>t12.p
and t8.p<>t9.p and t8.p<>t10.p and t8.p<>t11.p and t8.p<>t12.p
and t9.p<>t10.p and t9.p<>t11.p and t9.p<>t12.p
and t10.p<>t11.p and t10.p<>t12.p
and t11.p<>t12.p
--右斜
and t1.p<>t2.p-1 and t1.p<>t3.p-2 and t1.p<>t4.p-3 and t1.p<>t5.p-4 and t1.p<>t6.p-5 and t1.p<>t7.p-6 and t1.p<>t8.p-7 and t1.p<>t9.p-8 and t1.p<>t10.p-9 and t1.p<>t11.p-10 and t1.p<>t12.p-11
and t2.p<>t3.p-1 and t2.p<>t4.p-2 and t2.p<>t5.p-3 and t2.p<>t6.p-4 and t2.p<>t7.p-5 and t2.p<>t8.p-6 and t2.p<>t9.p-7 and t2.p<>t10.p-8 and t2.p<>t11.p-9 and t2.p<>t12.p-10
and t3.p<>t4.p-1 and t3.p<>t5.p-2 and t3.p<>t6.p-3 and t3.p<>t7.p-4 and t3.p<>t8.p-5 and t3.p<>t9.p-6 and t3.p<>t10.p-7 and t3.p<>t11.p-8 and t3.p<>t12.p-9
and t4.p<>t5.p-1 and t4.p<>t6.p-2 and t4.p<>t7.p-3 and t4.p<>t8.p-4 and t4.p<>t9.p-5 and t4.p<>t10.p-6 and t4.p<>t11.p-7 and t4.p<>t12.p-8
and t5.p<>t6.p-1 and t5.p<>t7.p-2 and t5.p<>t8.p-3 and t5.p<>t9.p-4 and t5.p<>t10.p-5 and t5.p<>t11.p-6 and t5.p<>t12.p-7
and t6.p<>t7.p-1 and t6.p<>t8.p-2 and t6.p<>t9.p-3 and t6.p<>t10.p-4 and t6.p<>t11.p-5 and t6.p<>t12.p-6
and t7.p<>t8.p-1 and t7.p<>t9.p-2 and t7.p<>t10.p-3 and t7.p<>t11.p-4 and t7.p<>t12.p-5
and t8.p<>t9.p-1 and t8.p<>t10.p-2 and t8.p<>t11.p-3 and t8.p<>t12.p-4
and t9.p<>t10.p-1 and t9.p<>t11.p-2 and t9.p<>t12.p-3
and t10.p<>t11.p-1 and t10.p<>t12.p-2
and t11.p<>t12.p-1
--左斜
and t1.p<>t2.p+1 and t1.p<>t3.p+2 and t1.p<>t4.p+3 and t1.p<>t5.p+4 and t1.p<>t6.p+5 and t1.p<>t7.p+6 and t1.p<>t8.p+7 and t1.p<>t9.p+8 and t1.p<>t10.p+9 and t1.p<>t11.p+10 and t1.p<>t12.p+11
and t2.p<>t3.p+1 and t2.p<>t4.p+2 and t2.p<>t5.p+3 and t2.p<>t6.p+4 and t2.p<>t7.p+5 and t2.p<>t8.p+6 and t2.p<>t9.p+7 and t2.p<>t10.p+8 and t2.p<>t11.p+9 and t2.p<>t12.p+10
and t3.p<>t4.p+1 and t3.p<>t5.p+2 and t3.p<>t6.p+3 and t3.p<>t7.p+4 and t3.p<>t8.p+5 and t3.p<>t9.p+6 and t3.p<>t10.p+7 and t3.p<>t11.p+8 and t3.p<>t12.p+9
and t4.p<>t5.p+1 and t4.p<>t6.p+2 and t4.p<>t7.p+3 and t4.p<>t8.p+4 and t4.p<>t9.p+5 and t4.p<>t10.p+6 and t4.p<>t11.p+7 and t4.p<>t12.p+8
and t5.p<>t6.p+1 and t5.p<>t7.p+2 and t5.p<>t8.p+3 and t5.p<>t9.p+4 and t5.p<>t10.p+5 and t5.p<>t11.p+6 and t5.p<>t12.p+7
and t6.p<>t7.p+1 and t6.p<>t8.p+2 and t6.p<>t9.p+3 and t6.p<>t10.p+4 and t6.p<>t11.p+5 and t6.p<>t12.p+6
and t7.p<>t8.p+1 and t7.p<>t9.p+2 and t7.p<>t10.p+3 and t7.p<>t11.p+4 and t7.p<>t12.p+5
and t8.p<>t9.p+1 and t8.p<>t10.p+2 and t8.p<>t11.p+3 and t8.p<>t12.p+4
and t9.p<>t10.p+1 and t9.p<>t11.p+2 and t9.p<>t12.p+3
and t10.p<>t11.p+1 and t10.p<>t12.p+2
and t11.p<>t12.p+1
union all
select
*
from @t t1
cross join @t t2 cross join @t t3 cross join @t t4 cross join @t t5 cross join @t t6 cross join @t t7 cross join @t t8 cross join @t t9 cross join @t t10 cross join @t t11 cross join @t t12
where t1.p=2
--竖
and t1.p<>t2.p and t1.p<>t3.p and t1.p<>t4.p and t1.p<>t5.p and t1.p<>t6.p and t1.p<>t7.p and t1.p<>t8.p and t1.p<>t9.p and t1.p<>t10.p and t1.p<>t11.p and t1.p<>t12.p
and t2.p<>t3.p and t2.p<>t4.p and t2.p<>t5.p and t2.p<>t6.p and t2.p<>t7.p and t2.p<>t8.p and t2.p<>t9.p and t2.p<>t10.p and t2.p<>t11.p and t2.p<>t12.p
and t3.p<>t4.p and t3.p<>t5.p and t3.p<>t6.p and t3.p<>t7.p and t3.p<>t8.p and t3.p<>t9.p and t3.p<>t10.p and t3.p<>t11.p and t3.p<>t12.p
and t4.p<>t5.p and t4.p<>t6.p and t4.p<>t7.p and t4.p<>t8.p and t4.p<>t9.p and t4.p<>t10.p and t4.p<>t11.p and t4.p<>t12.p
and t5.p<>t6.p and t5.p<>t7.p and t5.p<>t8.p and t5.p<>t9.p and t5.p<>t10.p and t5.p<>t11.p and t5.p<>t12.p
and t6.p<>t7.p and t6.p<>t8.p and t6.p<>t9.p and t6.p<>t10.p and t6.p<>t11.p and t6.p<>t12.p
and t7.p<>t8.p and t7.p<>t9.p and t7.p<>t10.p and t7.p<>t11.p and t7.p<>t12.p
and t8.p<>t9.p and t8.p<>t10.p and t8.p<>t11.p and t8.p<>t12.p
and t9.p<>t10.p and t9.p<>t11.p and t9.p<>t12.p
and t10.p<>t11.p and t10.p<>t12.p
and t11.p<>t12.p
--右斜
and t1.p<>t2.p-1 and t1.p<>t3.p-2 and t1.p<>t4.p-3 and t1.p<>t5.p-4 and t1.p<>t6.p-5 and t1.p<>t7.p-6 and t1.p<>t8.p-7 and t1.p<>t9.p-8 and t1.p<>t10.p-9 and t1.p<>t11.p-10 and t1.p<>t12.p-11
and t2.p<>t3.p-1 and t2.p<>t4.p-2 and t2.p<>t5.p-3 and t2.p<>t6.p-4 and t2.p<>t7.p-5 and t2.p<>t8.p-6 and t2.p<>t9.p-7 and t2.p<>t10.p-8 and t2.p<>t11.p-9 and t2.p<>t12.p-10
and t3.p<>t4.p-1 and t3.p<>t5.p-2 and t3.p<>t6.p-3 and t3.p<>t7.p-4 and t3.p<>t8.p-5 and t3.p<>t9.p-6 and t3.p<>t10.p-7 and t3.p<>t11.p-8 and t3.p<>t12.p-9
and t4.p<>t5.p-1 and t4.p<>t6.p-2 and t4.p<>t7.p-3 and t4.p<>t8.p-4 and t4.p<>t9.p-5 and t4.p<>t10.p-6 and t4.p<>t11.p-7 and t4.p<>t12.p-8
and t5.p<>t6.p-1 and t5.p<>t7.p-2 and t5.p<>t8.p-3 and t5.p<>t9.p-4 and t5.p<>t10.p-5 and t5.p<>t11.p-6 and t5.p<>t12.p-7
and t6.p<>t7.p-1 and t6.p<>t8.p-2 and t6.p<>t9.p-3 and t6.p<>t10.p-4 and t6.p<>t11.p-5 and t6.p<>t12.p-6
and t7.p<>t8.p-1 and t7.p<>t9.p-2 and t7.p<>t10.p-3 and t7.p<>t11.p-4 and t7.p<>t12.p-5
and t8.p<>t9.p-1 and t8.p<>t10.p-2 and t8.p<>t11.p-3 and t8.p<>t12.p-4
and t9.p<>t10.p-1 and t9.p<>t11.p-2 and t9.p<>t12.p-3
and t10.p<>t11.p-1 and t10.p<>t12.p-2
and t11.p<>t12.p-1
--左斜
and t1.p<>t2.p+1 and t1.p<>t3.p+2 and t1.p<>t4.p+3 and t1.p<>t5.p+4 and t1.p<>t6.p+5 and t1.p<>t7.p+6 and t1.p<>t8.p+7 and t1.p<>t9.p+8 and t1.p<>t10.p+9 and t1.p<>t11.p+10 and t1.p<>t12.p+11
and t2.p<>t3.p+1 and t2.p<>t4.p+2 and t2.p<>t5.p+3 and t2.p<>t6.p+4 and t2.p<>t7.p+5 and t2.p<>t8.p+6 and t2.p<>t9.p+7 and t2.p<>t10.p+8 and t2.p<>t11.p+9 and t2.p<>t12.p+10
and t3.p<>t4.p+1 and t3.p<>t5.p+2 and t3.p<>t6.p+3 and t3.p<>t7.p+4 and t3.p<>t8.p+5 and t3.p<>t9.p+6 and t3.p<>t10.p+7 and t3.p<>t11.p+8 and t3.p<>t12.p+9
and t4.p<>t5.p+1 and t4.p<>t6.p+2 and t4.p<>t7.p+3 and t4.p<>t8.p+4 and t4.p<>t9.p+5 and t4.p<>t10.p+6 and t4.p<>t11.p+7 and t4.p<>t12.p+8
and t5.p<>t6.p+1 and t5.p<>t7.p+2 and t5.p<>t8.p+3 and t5.p<>t9.p+4 and t5.p<>t10.p+5 and t5.p<>t11.p+6 and t5.p<>t12.p+7
and t6.p<>t7.p+1 and t6.p<>t8.p+2 and t6.p<>t9.p+3 and t6.p<>t10.p+4 and t6.p<>t11.p+5 and t6.p<>t12.p+6
and t7.p<>t8.p+1 and t7.p<>t9.p+2 and t7.p<>t10.p+3 and t7.p<>t11.p+4 and t7.p<>t12.p+5
and t8.p<>t9.p+1 and t8.p<>t10.p+2 and t8.p<>t11.p+3 and t8.p<>t12.p+4
and t9.p<>t10.p+1 and t9.p<>t11.p+2 and t9.p<>t12.p+3
and t10.p<>t11.p+1 and t10.p<>t12.p+2
and t11.p<>t12.p+1
...全文
请发表友善的回复…
发表回复
永生天地 2010-04-28
- 打赏
- 举报
这里有个例子，似乎这个功能只是给客户端应用，提供了快速方便的操作
http://blog.csdn.net/hwy00/archive/2009/11/19/4835361.aspx
http://blog.csdn.net/hwy00/archive/2009/11/19/4835361.aspx
永生天地 2010-04-28
- 打赏
- 举报
Multiple Active Result Set (MARS) 讲了一下这类问题
http://www.microsoft.com/china/technet/prodtechnol/sql/2005/marssql05.mspx
http://www.microsoft.com/china/technet/prodtechnol/sql/2005/marssql05.mspx
永生天地 2010-04-28
- 打赏
- 举报
如果在sql server里可以把不同session的查询结果union就差不多了
haitao 2010-04-28
- 打赏
- 举报
0是使用所有的cpu
我的系统也是这样设置的
但是，这个设置只是上限，不是下限
数据库引擎执行具体的sql时，是否并行，这个无法控制
我观察 2cpu x 4核 x 超线程 的机器，每次执行那个12个union all的sql，却仍然只是1/16的cpu在忙
也就是说 数据库引擎没把12个union all分别交多个cpu来执行
所以想知道有没有语句可以硬性指定一个sql分别多个cpu来执行，除非没那么多cpu或并行的配置设小了
如果人为分几个查询提交，肯定是可以分别由多个cpu来执行的，但是一个sql里如何做到新建连接来提交部分sql呢？
我的系统也是这样设置的
但是，这个设置只是上限，不是下限
数据库引擎执行具体的sql时，是否并行，这个无法控制
我观察 2cpu x 4核 x 超线程 的机器，每次执行那个12个union all的sql，却仍然只是1/16的cpu在忙
也就是说 数据库引擎没把12个union all分别交多个cpu来执行
所以想知道有没有语句可以硬性指定一个sql分别多个cpu来执行，除非没那么多cpu或并行的配置设小了
如果人为分几个查询提交，肯定是可以分别由多个cpu来执行的，但是一个sql里如何做到新建连接来提交部分sql呢？
永生天地 2010-04-28
- 打赏
- 举报
搜了一下，没怎么看懂
http://info.china.alibaba.com/news/detail/v0-d1003728874.html
还有sql2005可以建分区表，不知道分区表查询union是不是就是并行。。没用过
http://info.china.alibaba.com/news/detail/v0-d1003728874.html
还有sql2005可以建分区表，不知道分区表查询union是不是就是并行。。没用过
obuntu 2010-04-28
- 打赏
- 举报
楼主很强大，呵呵。
obuntu 2010-04-28
- 打赏
- 举报
可以指定并行的啊。
可通过内置参数指定，也可以通过hints来指定
内部参数的指定
默认是0，也就是系统自动调整；
hints的语法是
option (maxdop n)
n就是指定的并行数。。
不过这个跟CPU关系很大。
可通过内置参数指定，也可以通过hints来指定
内部参数的指定
sp_configure 'max degree of parallelism'
sp_configure 'cost threshold for parallelism'
默认是0，也就是系统自动调整；
hints的语法是
option (maxdop n)
n就是指定的并行数。。
不过这个跟CPU关系很大。
haitao 2010-04-28
- 打赏
- 举报
星星、钻钻的人士 来说一说sqlserver的自动并行执行sql的能力吧
以前，虽然主流的观点是sqlserver在大型应用上不如db2/oracle，但是自己一直觉得sqlserver已经相当够用了
这次的并行化试验才发现它居然不会利用多核。。。。。。。。当然，任务数少于cpu数的时候才需要
以前，虽然主流的观点是sqlserver在大型应用上不如db2/oracle，但是自己一直觉得sqlserver已经相当够用了
这次的并行化试验才发现它居然不会利用多核。。。。。。。。当然，任务数少于cpu数的时候才需要
haitao 2010-04-27
- 打赏
- 举报
sql是自动循环生成的：
http://topic.csdn.net/u/20100419/11/ce7c3290-7172-4ee7-957b-dcde0aa61d23_2.html
反正就是union all用到的n个子查询
如何让它们并行执行，最后才一次性union all
http://topic.csdn.net/u/20100419/11/ce7c3290-7172-4ee7-957b-dcde0aa61d23_2.html
反正就是union all用到的n个子查询
如何让它们并行执行，最后才一次性union all
--小F-- 2010-04-27
- 打赏
- 举报
眼睛都看花了 很强
haitao 2010-04-27
- 打赏
- 举报
。。。。。。。。。。。。10k的内容限制也太。。。。。。。。
union all
select
*
from @t t1
cross join @t t2 cross join @t t3 cross join @t t4 cross join @t t5 cross join @t t6 cross join @t t7 cross join @t t8 cross join @t t9 cross join @t t10 cross join @t t11 cross join @t t12
where t1.p=12
--竖
and t1.p<>t2.p and t1.p<>t3.p and t1.p<>t4.p and t1.p<>t5.p and t1.p<>t6.p and t1.p<>t7.p and t1.p<>t8.p and t1.p<>t9.p and t1.p<>t10.p and t1.p<>t11.p and t1.p<>t12.p
and t2.p<>t3.p and t2.p<>t4.p and t2.p<>t5.p and t2.p<>t6.p and t2.p<>t7.p and t2.p<>t8.p and t2.p<>t9.p and t2.p<>t10.p and t2.p<>t11.p and t2.p<>t12.p
and t3.p<>t4.p and t3.p<>t5.p and t3.p<>t6.p and t3.p<>t7.p and t3.p<>t8.p and t3.p<>t9.p and t3.p<>t10.p and t3.p<>t11.p and t3.p<>t12.p
and t4.p<>t5.p and t4.p<>t6.p and t4.p<>t7.p and t4.p<>t8.p and t4.p<>t9.p and t4.p<>t10.p and t4.p<>t11.p and t4.p<>t12.p
and t5.p<>t6.p and t5.p<>t7.p and t5.p<>t8.p and t5.p<>t9.p and t5.p<>t10.p and t5.p<>t11.p and t5.p<>t12.p
and t6.p<>t7.p and t6.p<>t8.p and t6.p<>t9.p and t6.p<>t10.p and t6.p<>t11.p and t6.p<>t12.p
and t7.p<>t8.p and t7.p<>t9.p and t7.p<>t10.p and t7.p<>t11.p and t7.p<>t12.p
and t8.p<>t9.p and t8.p<>t10.p and t8.p<>t11.p and t8.p<>t12.p
and t9.p<>t10.p and t9.p<>t11.p and t9.p<>t12.p
and t10.p<>t11.p and t10.p<>t12.p
and t11.p<>t12.p
--右斜
and t1.p<>t2.p-1 and t1.p<>t3.p-2 and t1.p<>t4.p-3 and t1.p<>t5.p-4 and t1.p<>t6.p-5 and t1.p<>t7.p-6 and t1.p<>t8.p-7 and t1.p<>t9.p-8 and t1.p<>t10.p-9 and t1.p<>t11.p-10 and t1.p<>t12.p-11
and t2.p<>t3.p-1 and t2.p<>t4.p-2 and t2.p<>t5.p-3 and t2.p<>t6.p-4 and t2.p<>t7.p-5 and t2.p<>t8.p-6 and t2.p<>t9.p-7 and t2.p<>t10.p-8 and t2.p<>t11.p-9 and t2.p<>t12.p-10
and t3.p<>t4.p-1 and t3.p<>t5.p-2 and t3.p<>t6.p-3 and t3.p<>t7.p-4 and t3.p<>t8.p-5 and t3.p<>t9.p-6 and t3.p<>t10.p-7 and t3.p<>t11.p-8 and t3.p<>t12.p-9
and t4.p<>t5.p-1 and t4.p<>t6.p-2 and t4.p<>t7.p-3 and t4.p<>t8.p-4 and t4.p<>t9.p-5 and t4.p<>t10.p-6 and t4.p<>t11.p-7 and t4.p<>t12.p-8
and t5.p<>t6.p-1 and t5.p<>t7.p-2 and t5.p<>t8.p-3 and t5.p<>t9.p-4 and t5.p<>t10.p-5 and t5.p<>t11.p-6 and t5.p<>t12.p-7
and t6.p<>t7.p-1 and t6.p<>t8.p-2 and t6.p<>t9.p-3 and t6.p<>t10.p-4 and t6.p<>t11.p-5 and t6.p<>t12.p-6
and t7.p<>t8.p-1 and t7.p<>t9.p-2 and t7.p<>t10.p-3 and t7.p<>t11.p-4 and t7.p<>t12.p-5
and t8.p<>t9.p-1 and t8.p<>t10.p-2 and t8.p<>t11.p-3 and t8.p<>t12.p-4
and t9.p<>t10.p-1 and t9.p<>t11.p-2 and t9.p<>t12.p-3
and t10.p<>t11.p-1 and t10.p<>t12.p-2
and t11.p<>t12.p-1
--左斜
and t1.p<>t2.p+1 and t1.p<>t3.p+2 and t1.p<>t4.p+3 and t1.p<>t5.p+4 and t1.p<>t6.p+5 and t1.p<>t7.p+6 and t1.p<>t8.p+7 and t1.p<>t9.p+8 and t1.p<>t10.p+9 and t1.p<>t11.p+10 and t1.p<>t12.p+11
and t2.p<>t3.p+1 and t2.p<>t4.p+2 and t2.p<>t5.p+3 and t2.p<>t6.p+4 and t2.p<>t7.p+5 and t2.p<>t8.p+6 and t2.p<>t9.p+7 and t2.p<>t10.p+8 and t2.p<>t11.p+9 and t2.p<>t12.p+10
and t3.p<>t4.p+1 and t3.p<>t5.p+2 and t3.p<>t6.p+3 and t3.p<>t7.p+4 and t3.p<>t8.p+5 and t3.p<>t9.p+6 and t3.p<>t10.p+7 and t3.p<>t11.p+8 and t3.p<>t12.p+9
and t4.p<>t5.p+1 and t4.p<>t6.p+2 and t4.p<>t7.p+3 and t4.p<>t8.p+4 and t4.p<>t9.p+5 and t4.p<>t10.p+6 and t4.p<>t11.p+7 and t4.p<>t12.p+8
and t5.p<>t6.p+1 and t5.p<>t7.p+2 and t5.p<>t8.p+3 and t5.p<>t9.p+4 and t5.p<>t10.p+5 and t5.p<>t11.p+6 and t5.p<>t12.p+7
and t6.p<>t7.p+1 and t6.p<>t8.p+2 and t6.p<>t9.p+3 and t6.p<>t10.p+4 and t6.p<>t11.p+5 and t6.p<>t12.p+6
and t7.p<>t8.p+1 and t7.p<>t9.p+2 and t7.p<>t10.p+3 and t7.p<>t11.p+4 and t7.p<>t12.p+5
and t8.p<>t9.p+1 and t8.p<>t10.p+2 and t8.p<>t11.p+3 and t8.p<>t12.p+4
and t9.p<>t10.p+1 and t9.p<>t11.p+2 and t9.p<>t12.p+3
and t10.p<>t11.p+1 and t10.p<>t12.p+2
and t11.p<>t12.p+1
--格式化输出：
declare @n int
set @n=12
select ''
+char(13)+REPLACE(space(c1-1),' ','.')+'#'+REPLACE(space(@n-c1),' ','.')
+char(13)+REPLACE(space(c2-1),' ','.')+'#'+REPLACE(space(@n-c2),' ','.')
+char(13)+REPLACE(space(c3-1),' ','.')+'#'+REPLACE(space(@n-c3),' ','.')
+char(13)+REPLACE(space(c4-1),' ','.')+'#'+REPLACE(space(@n-c4),' ','.')
+char(13)+REPLACE(space(c5-1),' ','.')+'#'+REPLACE(space(@n-c5),' ','.')
+char(13)+REPLACE(space(c6-1),' ','.')+'#'+REPLACE(space(@n-c6),' ','.')
+char(13)+REPLACE(space(c7-1),' ','.')+'#'+REPLACE(space(@n-c7),' ','.')
+char(13)+REPLACE(space(c8-1),' ','.')+'#'+REPLACE(space(@n-c8),' ','.')
+char(13)+REPLACE(space(c9-1),' ','.')+'#'+REPLACE(space(@n-c9),' ','.')
+char(13)+REPLACE(space(c10-1),' ','.')+'#'+REPLACE(space(@n-c10),' ','.')
+char(13)+REPLACE(space(c11-1),' ','.')+'#'+REPLACE(space(@n-c11),' ','.')
+char(13)+REPLACE(space(c12-1),' ','.')+'#'+REPLACE(space(@n-c12),' ','.')
+char(13)+REPLACE(space(12),' ','=')
from @c
htl258_Tony 2010-04-27
- 打赏
- 举报
貌似很强。先顶一下
haitao 2010-04-27
- 打赏
- 举报
union all
select
*
from @t t1
cross join @t t2 cross join @t t3 cross join @t t4 cross join @t t5 cross join @t t6 cross join @t t7 cross join @t t8 cross join @t t9 cross join @t t10 cross join @t t11 cross join @t t12
where t1.p=3
--竖
and t1.p<>t2.p and t1.p<>t3.p and t1.p<>t4.p and t1.p<>t5.p and t1.p<>t6.p and t1.p<>t7.p and t1.p<>t8.p and t1.p<>t9.p and t1.p<>t10.p and t1.p<>t11.p and t1.p<>t12.p
and t2.p<>t3.p and t2.p<>t4.p and t2.p<>t5.p and t2.p<>t6.p and t2.p<>t7.p and t2.p<>t8.p and t2.p<>t9.p and t2.p<>t10.p and t2.p<>t11.p and t2.p<>t12.p
and t3.p<>t4.p and t3.p<>t5.p and t3.p<>t6.p and t3.p<>t7.p and t3.p<>t8.p and t3.p<>t9.p and t3.p<>t10.p and t3.p<>t11.p and t3.p<>t12.p
and t4.p<>t5.p and t4.p<>t6.p and t4.p<>t7.p and t4.p<>t8.p and t4.p<>t9.p and t4.p<>t10.p and t4.p<>t11.p and t4.p<>t12.p
and t5.p<>t6.p and t5.p<>t7.p and t5.p<>t8.p and t5.p<>t9.p and t5.p<>t10.p and t5.p<>t11.p and t5.p<>t12.p
and t6.p<>t7.p and t6.p<>t8.p and t6.p<>t9.p and t6.p<>t10.p and t6.p<>t11.p and t6.p<>t12.p
and t7.p<>t8.p and t7.p<>t9.p and t7.p<>t10.p and t7.p<>t11.p and t7.p<>t12.p
and t8.p<>t9.p and t8.p<>t10.p and t8.p<>t11.p and t8.p<>t12.p
and t9.p<>t10.p and t9.p<>t11.p and t9.p<>t12.p
and t10.p<>t11.p and t10.p<>t12.p
and t11.p<>t12.p
--右斜
and t1.p<>t2.p-1 and t1.p<>t3.p-2 and t1.p<>t4.p-3 and t1.p<>t5.p-4 and t1.p<>t6.p-5 and t1.p<>t7.p-6 and t1.p<>t8.p-7 and t1.p<>t9.p-8 and t1.p<>t10.p-9 and t1.p<>t11.p-10 and t1.p<>t12.p-11
and t2.p<>t3.p-1 and t2.p<>t4.p-2 and t2.p<>t5.p-3 and t2.p<>t6.p-4 and t2.p<>t7.p-5 and t2.p<>t8.p-6 and t2.p<>t9.p-7 and t2.p<>t10.p-8 and t2.p<>t11.p-9 and t2.p<>t12.p-10
and t3.p<>t4.p-1 and t3.p<>t5.p-2 and t3.p<>t6.p-3 and t3.p<>t7.p-4 and t3.p<>t8.p-5 and t3.p<>t9.p-6 and t3.p<>t10.p-7 and t3.p<>t11.p-8 and t3.p<>t12.p-9
and t4.p<>t5.p-1 and t4.p<>t6.p-2 and t4.p<>t7.p-3 and t4.p<>t8.p-4 and t4.p<>t9.p-5 and t4.p<>t10.p-6 and t4.p<>t11.p-7 and t4.p<>t12.p-8
and t5.p<>t6.p-1 and t5.p<>t7.p-2 and t5.p<>t8.p-3 and t5.p<>t9.p-4 and t5.p<>t10.p-5 and t5.p<>t11.p-6 and t5.p<>t12.p-7
and t6.p<>t7.p-1 and t6.p<>t8.p-2 and t6.p<>t9.p-3 and t6.p<>t10.p-4 and t6.p<>t11.p-5 and t6.p<>t12.p-6
and t7.p<>t8.p-1 and t7.p<>t9.p-2 and t7.p<>t10.p-3 and t7.p<>t11.p-4 and t7.p<>t12.p-5
and t8.p<>t9.p-1 and t8.p<>t10.p-2 and t8.p<>t11.p-3 and t8.p<>t12.p-4
and t9.p<>t10.p-1 and t9.p<>t11.p-2 and t9.p<>t12.p-3
and t10.p<>t11.p-1 and t10.p<>t12.p-2
and t11.p<>t12.p-1
--左斜
and t1.p<>t2.p+1 and t1.p<>t3.p+2 and t1.p<>t4.p+3 and t1.p<>t5.p+4 and t1.p<>t6.p+5 and t1.p<>t7.p+6 and t1.p<>t8.p+7 and t1.p<>t9.p+8 and t1.p<>t10.p+9 and t1.p<>t11.p+10 and t1.p<>t12.p+11
and t2.p<>t3.p+1 and t2.p<>t4.p+2 and t2.p<>t5.p+3 and t2.p<>t6.p+4 and t2.p<>t7.p+5 and t2.p<>t8.p+6 and t2.p<>t9.p+7 and t2.p<>t10.p+8 and t2.p<>t11.p+9 and t2.p<>t12.p+10
and t3.p<>t4.p+1 and t3.p<>t5.p+2 and t3.p<>t6.p+3 and t3.p<>t7.p+4 and t3.p<>t8.p+5 and t3.p<>t9.p+6 and t3.p<>t10.p+7 and t3.p<>t11.p+8 and t3.p<>t12.p+9
and t4.p<>t5.p+1 and t4.p<>t6.p+2 and t4.p<>t7.p+3 and t4.p<>t8.p+4 and t4.p<>t9.p+5 and t4.p<>t10.p+6 and t4.p<>t11.p+7 and t4.p<>t12.p+8
and t5.p<>t6.p+1 and t5.p<>t7.p+2 and t5.p<>t8.p+3 and t5.p<>t9.p+4 and t5.p<>t10.p+5 and t5.p<>t11.p+6 and t5.p<>t12.p+7
and t6.p<>t7.p+1 and t6.p<>t8.p+2 and t6.p<>t9.p+3 and t6.p<>t10.p+4 and t6.p<>t11.p+5 and t6.p<>t12.p+6
and t7.p<>t8.p+1 and t7.p<>t9.p+2 and t7.p<>t10.p+3 and t7.p<>t11.p+4 and t7.p<>t12.p+5
and t8.p<>t9.p+1 and t8.p<>t10.p+2 and t8.p<>t11.p+3 and t8.p<>t12.p+4
and t9.p<>t10.p+1 and t9.p<>t11.p+2 and t9.p<>t12.p+3
and t10.p<>t11.p+1 and t10.p<>t12.p+2
and t11.p<>t12.p+1
union all
select
*
from @t t1
cross join @t t2 cross join @t t3 cross join @t t4 cross join @t t5 cross join @t t6 cross join @t t7 cross join @t t8 cross join @t t9 cross join @t t10 cross join @t t11 cross join @t t12
where t1.p=4
--竖
and t1.p<>t2.p and t1.p<>t3.p and t1.p<>t4.p and t1.p<>t5.p and t1.p<>t6.p and t1.p<>t7.p and t1.p<>t8.p and t1.p<>t9.p and t1.p<>t10.p and t1.p<>t11.p and t1.p<>t12.p
and t2.p<>t3.p and t2.p<>t4.p and t2.p<>t5.p and t2.p<>t6.p and t2.p<>t7.p and t2.p<>t8.p and t2.p<>t9.p and t2.p<>t10.p and t2.p<>t11.p and t2.p<>t12.p
and t3.p<>t4.p and t3.p<>t5.p and t3.p<>t6.p and t3.p<>t7.p and t3.p<>t8.p and t3.p<>t9.p and t3.p<>t10.p and t3.p<>t11.p and t3.p<>t12.p
and t4.p<>t5.p and t4.p<>t6.p and t4.p<>t7.p and t4.p<>t8.p and t4.p<>t9.p and t4.p<>t10.p and t4.p<>t11.p and t4.p<>t12.p
and t5.p<>t6.p and t5.p<>t7.p and t5.p<>t8.p and t5.p<>t9.p and t5.p<>t10.p and t5.p<>t11.p and t5.p<>t12.p
and t6.p<>t7.p and t6.p<>t8.p and t6.p<>t9.p and t6.p<>t10.p and t6.p<>t11.p and t6.p<>t12.p
and t7.p<>t8.p and t7.p<>t9.p and t7.p<>t10.p and t7.p<>t11.p and t7.p<>t12.p
and t8.p<>t9.p and t8.p<>t10.p and t8.p<>t11.p and t8.p<>t12.p
and t9.p<>t10.p and t9.p<>t11.p and t9.p<>t12.p
and t10.p<>t11.p and t10.p<>t12.p
and t11.p<>t12.p
--右斜
and t1.p<>t2.p-1 and t1.p<>t3.p-2 and t1.p<>t4.p-3 and t1.p<>t5.p-4 and t1.p<>t6.p-5 and t1.p<>t7.p-6 and t1.p<>t8.p-7 and t1.p<>t9.p-8 and t1.p<>t10.p-9 and t1.p<>t11.p-10 and t1.p<>t12.p-11
and t2.p<>t3.p-1 and t2.p<>t4.p-2 and t2.p<>t5.p-3 and t2.p<>t6.p-4 and t2.p<>t7.p-5 and t2.p<>t8.p-6 and t2.p<>t9.p-7 and t2.p<>t10.p-8 and t2.p<>t11.p-9 and t2.p<>t12.p-10
and t3.p<>t4.p-1 and t3.p<>t5.p-2 and t3.p<>t6.p-3 and t3.p<>t7.p-4 and t3.p<>t8.p-5 and t3.p<>t9.p-6 and t3.p<>t10.p-7 and t3.p<>t11.p-8 and t3.p<>t12.p-9
and t4.p<>t5.p-1 and t4.p<>t6.p-2 and t4.p<>t7.p-3 and t4.p<>t8.p-4 and t4.p<>t9.p-5 and t4.p<>t10.p-6 and t4.p<>t11.p-7 and t4.p<>t12.p-8
and t5.p<>t6.p-1 and t5.p<>t7.p-2 and t5.p<>t8.p-3 and t5.p<>t9.p-4 and t5.p<>t10.p-5 and t5.p<>t11.p-6 and t5.p<>t12.p-7
and t6.p<>t7.p-1 and t6.p<>t8.p-2 and t6.p<>t9.p-3 and t6.p<>t10.p-4 and t6.p<>t11.p-5 and t6.p<>t12.p-6
and t7.p<>t8.p-1 and t7.p<>t9.p-2 and t7.p<>t10.p-3 and t7.p<>t11.p-4 and t7.p<>t12.p-5
and t8.p<>t9.p-1 and t8.p<>t10.p-2 and t8.p<>t11.p-3 and t8.p<>t12.p-4
and t9.p<>t10.p-1 and t9.p<>t11.p-2 and t9.p<>t12.p-3
and t10.p<>t11.p-1 and t10.p<>t12.p-2
and t11.p<>t12.p-1
--左斜
and t1.p<>t2.p+1 and t1.p<>t3.p+2 and t1.p<>t4.p+3 and t1.p<>t5.p+4 and t1.p<>t6.p+5 and t1.p<>t7.p+6 and t1.p<>t8.p+7 and t1.p<>t9.p+8 and t1.p<>t10.p+9 and t1.p<>t11.p+10 and t1.p<>t12.p+11
and t2.p<>t3.p+1 and t2.p<>t4.p+2 and t2.p<>t5.p+3 and t2.p<>t6.p+4 and t2.p<>t7.p+5 and t2.p<>t8.p+6 and t2.p<>t9.p+7 and t2.p<>t10.p+8 and t2.p<>t11.p+9 and t2.p<>t12.p+10
and t3.p<>t4.p+1 and t3.p<>t5.p+2 and t3.p<>t6.p+3 and t3.p<>t7.p+4 and t3.p<>t8.p+5 and t3.p<>t9.p+6 and t3.p<>t10.p+7 and t3.p<>t11.p+8 and t3.p<>t12.p+9
and t4.p<>t5.p+1 and t4.p<>t6.p+2 and t4.p<>t7.p+3 and t4.p<>t8.p+4 and t4.p<>t9.p+5 and t4.p<>t10.p+6 and t4.p<>t11.p+7 and t4.p<>t12.p+8
and t5.p<>t6.p+1 and t5.p<>t7.p+2 and t5.p<>t8.p+3 and t5.p<>t9.p+4 and t5.p<>t10.p+5 and t5.p<>t11.p+6 and t5.p<>t12.p+7
and t6.p<>t7.p+1 and t6.p<>t8.p+2 and t6.p<>t9.p+3 and t6.p<>t10.p+4 and t6.p<>t11.p+5 and t6.p<>t12.p+6
and t7.p<>t8.p+1 and t7.p<>t9.p+2 and t7.p<>t10.p+3 and t7.p<>t11.p+4 and t7.p<>t12.p+5
and t8.p<>t9.p+1 and t8.p<>t10.p+2 and t8.p<>t11.p+3 and t8.p<>t12.p+4
and t9.p<>t10.p+1 and t9.p<>t11.p+2 and t9.p<>t12.p+3
and t10.p<>t11.p+1 and t10.p<>t12.p+2
and t11.p<>t12.p+1
