你的位置:首页 > 数据库

[数据库]学习sql中的排列组合,在园子里搜着看于是。。。


      学习sql中的排列组合,在园子里搜着看,看到篇文章,于是自己(新手)用了最最原始的sql去写出来:

--需求--
--B, C, F, M and S住在一座房子的不同楼层。
--B 不住顶层。C 不住底层。
--F 既不住顶层也不住底层。M 住得比 C 高。
--S 住的楼层和 F 不相邻。
--F 住的楼层和 C 不相邻。

 

create table pailie
(rnam varchar(20) )
select 'B' as rnam into #y
union
select 'C'
union
select 'F'
union
select 'M'
union
select 'S'
insert into pailie
select ALL B.rnam+C.rnam+F.rnam+M.rnam+S.rnam from #y B,#y C,#y F,#y M,#y S


delete from pailie
where rnam like
'%BB%' or rnam like '%B%B' or rnam like '%B%B%' or rnam like 'B%B%' or rnam like 'BB%' or rnam like '%BB' or rnam like 'B%B' or rnam like
'%CC%' or rnam like '%C%C' or rnam like '%C%C%' or rnam like 'C%C%' or rnam like 'CC%' or rnam like '%CC' or rnam like 'C%C' or rnam like
'%FF%' or rnam like '%F%F' or rnam like '%F%F%' or rnam like 'F%F%' or rnam like 'FF%' or rnam like '%FF' or rnam like 'F%F' or rnam like
'%MM%' or rnam like '%M%M' or rnam like '%M%M%' or rnam like 'M%M%' or rnam like 'MM%' or rnam like '%MM' or rnam like 'M%M' or rnam like
'%SS%' or rnam like '%S%S' or rnam like '%S%S%' or rnam like 'S%S%' or rnam like 'SS%' or rnam like '%SS' or rnam like 'S%S'

--删掉所有重复的(一个人不能住两层),列出所有的排列



delete from pailie
where rnam like 'B%' or rnam like '%C'

--B 不住顶层。C 不住底层。


delete from pailie
where rnam like 'F%' or rnam like '%F'
or rnam like '%CM%' or rnam like '%C%M' or rnam like '%C%M%' or rnam like 'C%M%' or rnam like 'CM%' or rnam like '%CM' or rnam like 'C%M'

--F 既不住顶层也不住底层。M 住得比 C 高。


delete from pailie
where rnam like 'SF%' or rnam like '%SF%' or rnam like '%SF'
or rnam like 'FS%' or rnam like '%FS%' or rnam like '%FS'

--S 住的楼层和 F 不相邻。


delete from pailie
where rnam like 'CF%' or rnam like '%CF%' or rnam like '%CF'
or rnam like 'FC%' or rnam like '%FC%' or rnam like '%FC'

--F 住的楼层和 C 不相邻。

结果:

由高到低的楼层!

(原文:http://www.cnblogs.com/boss-he/p/4628305.html#commentform)

 

 

(学习的知识笔记:CAST 和 CONVERT;WITH...AS();union all;@表变量;charindex )