＋设为首页 ＋添加到收藏夹
列1 列2 列3 列4
p i pi
b ao bao
l an lan
l i li
p an pan
p ao pao
b i bi
l an lan
b an ban
------------加几个数据帮助理解
zh ai zhai
b ing bing
p en pen
.....其它的数据
--------这个是数据库实际存在的，数据有1-2W数据组合，例要求列1 P出现3次，b出现3次,l出现3次,列2 --i出现3次,ao 出现2次,an 出现4次这样的条件来取数据。是在这些1-2W的数据中 随机符合我这个条件，条件是在变的，例要求 列1(声母)w出现3次，sh出现1次,d出现5次,列2 --an出现5次,ao 出现2次,ang 出现2次这样的条件来取数据,也就是说 在去得数据中列1与列2要符合我的 要求 。
/*
处理思想：由外部条件组合成一个结果集，与原结果集联合查询。
说明：外部条件列1和列2条件的行数应该相同。并返回这个行数的结果集。
sql中没有现成的关于一个字符串的全部排列组合的函数，所以由外部程序处理传入
部分变量的定义的长度需要统一和修改。
实际也不知道这个能否满足搂住的要求。
你可以在你的数据上试试，前提是你有的外部程序有个能得出某个字符串的全部排列组合的算法。
*/
--辅助函数：将字符串变为表
CREATE FUNCTION dbo.fnt_SplitString
(
@string varchar(8000), --以分隔符为结尾。
@split_flag char(1)
)
RETURNS @tbl table
( item_id int identity(1,1),
item varchar(100)
)
as
begin
if len(isnull(@split_flag,))=0
select @split_flag=@split_flag
if len(isnull(@string,))=0
select @string=0+@split_flag
if right(@string,1)<>@split_flag
select @string=@string+@split_flag
declare @var_value varchar(100) --分隔出的项值
declare @str_len integer --字符串长度
declare @str_pos integer --‘，’在字符串中的起始位置
select @str_len=len(@string)
select @str_pos=charindex(@split_flag,@string)
while @str_pos >0
begin
select @var_value=substring(@string,1,@str_pos -1)
if @var_value<>
insert into @tbl(item) values(@var_value)
select @string=substring(@string,@str_pos+1,@str_len - @str_pos)
select @str_len=len(@string)
select @str_pos=charindex(@split_flag,@string)
end
return
end
go
--主表数据
create table #t (col1 varchar(10),col2 varchar(10),col3 varchar(50))
insert into #t
select p,i,pi
union all select b,ao,bao
union all select l,an,lan
union all select l,i,li
union all select p,an,pan
union all select p,ao,pao
union all select b,i,bi
union all select l,an,lan
union all select b,an,ban
drop procedure getresult
create procedure getresult(@col1_condition varchar(200),--列1的条件，使用值,数目;....格式传递
@col2_condition varchar(200),--列2的条件，使用值,数目;....格式传递
@pstr varchar(8000)) --列2的全部排列组合，如条件：列2 a出现2次，c出现1次，则应该是a,a,c字符串的所有排列组合。
as
begin
declare @sqlstr varchar(1000)
select @sqlstr=
select @sqlstr=@sqlstr+replicate(union all select +left(item,charindex(_,item)-1)+,0 ,convert(int,right(item,len(item)-charindex(_,item))))
from dbo.fnt_splitstring(@col1_condition,,)
select @sqlstr=insert into #condition(col1value,flag) +stuff(@sqlstr,1,10,)
--辅助表1，获得每笔内容。如P出现两次，则为两条记录。
--这里是：列一：p 出现两次，b出现一次；l 出现一次；列二：i出现1次，ao出现1次，an出现两次。
create table #condition(id int identity(1,1),col1value varchar(10),flag char(1))
exec(@sqlstr)
select @sqlstr=
select @sqlstr=@sqlstr+replicate(union all select +left(item,charindex(_,item)-1)+,1 ,convert(int,right(item,len(item)-charindex(_,item))))
from dbo.fnt_splitstring(@col2_condition,,)
select @sqlstr=insert into #condition(col1value,flag) +stuff(@sqlstr,1,10,)
exec(@sqlstr)
/*
select p,0
union all select p,0
union all select b,0
union all select l,0
union all select i,1
union all select ao,1
union all select an,1
union all select an,1
*/
--外部实现的列2要出现的字母的排列组合。
--declare @pstr varchar(8000)
-------外部排列组合的程序比较多，但是我这里也没有现成的，所以手工列出来几个，以做测试。如谁有sql中的字符串排列组合的算法，可以提供一下。
--select @pstr=i,ao,an,an;i,an,ao,an;i,an,an,ao;ao,i,an,an;ao,an,i,an;ao,an,an,i;an,ao,i,an;an,ao,an,i;an,an,i,ao;an,an,ao,i;
--辅助表2记录所有的排列组合
create table #p(id int identity(1,1),sortstr varchar(100))
insert into #p(sortstr)
select item from dbo.fnt_splitstring(@pstr,;)
--辅助表3将某个排列组合的字符串序列化为行数据。
create table #s(id int identity(1,1),colvalue varchar(20))
--循环找符合的记录
declare @outrowcount int,@i int,@allcount int,@tempcount int
select @outrowcount=count(1) from #condition where flag=0 --要输出的总行数
select @allcount=count(1) from #p
select @i=1
while @i<=@allcount
begin
--获取排序的字符串
declare @str varchar(50)
select @str=sortstr from #p where id=@i
truncate table #s
--转化为表
insert into #s(colvalue)
select item from dbo.fnt_splitstring(@str,,)
--获得结果的行数
select @tempcount=count(1)
from #t a,
(select id,col1value from #condition where flag=0) b,
(select id,colvalue from #s) c
where a.col1=b.col1value and a.col2=c.colvalue and b.id=c.id
if @tempcount=@outrowcount
break
select @i=@i+1
end
if @tempcount=@outrowcount
select a.*
from #t a,
(select id,col1value from #condition where flag=0) b,
(select id,colvalue from #s) c
where a.col1=b.col1value and a.col2=c.colvalue and b.id=c.id
else
print no
drop table #condition
drop table #p
drop table #s
end
--调用测试：
declare @1 varchar(200),@2 varchar(200),@3 varchar(8000)
select @1=p_2,b_1,l_1
select @2=i_1,ao_1,an_2
select @3=i,ao,an,an;i,an,ao,an;i,an,an,ao;ao,i,an,an;ao,an,i,an;ao,an,an,i;an,ao,i,an;an,ao,an,i;an,an,i,ao;an,an,ao,i;
exec getresult @1,@2,@3
--结果
/*
col1 col2 col3
----------------------
l i li
p an pan
p ao pao
b an ban
*/
-- 感谢 rivery 给于的灵感，用以下方法返回所有的组合，大家互相探讨
if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[uf_BitNumber]) and xtype in (NFN, NIF, NTF))
drop function [dbo].[uf_BitNumber]
GO
create function uf_BitNumber (@in bigint)
returns int as
begin
declare @i int, @n bigint
set @i = 0
set @n = @in
while @n >0
begin
if @n % 2 >0
begin
set @i = @i +1
end
set @n = @n /2 -- FLOOR
end
return @i
end
GO
--主表数据
declare @tsrc table (id int IDENTITY(0,1) PRIMARY KEY, c1 varchar(10),c2 varchar(10),c3 varchar(50))
insert into @tsrc (c1,c2,c3)
select p,i ,pi
union all select b,ao,bao
union all select l,an,lan
union all select l,i ,li
union all select p,an,pan
union all select p,ao,pao
union all select b,i ,bi
union all select l,an,lan
union all select b,an,ban
declare @c1str as varchar(400), @c2str as varchar(400), @sqlstr as varchar(400)
set @c1str =p_2,b_1,l_1
set @c2str =i_1,ao_1,an_2
if exists( select * from tempdb..sysobjects where id = OBJECT_ID(tempdb..#tc1) and type=U ) drop table #tc1
if exists( select * from tempdb..sysobjects where id = OBJECT_ID(tempdb..#tc2) and type=U ) drop table #tc2
create table #tc1 ( i varchar(40), n int, h bigint)
create table #tc2 ( i varchar(40), n int, h bigint)
declare @tgrp table (id int IDENTITY(0,1) PRIMARY KEY,id0 int, c1 varchar(10),c2 varchar(10)/*, mcnt int*/, h bigint)
declare @tout table (id int IDENTITY(0,1) PRIMARY KEY,id0 int, c1 varchar(10),c2 varchar(10)/*, mcnt int*/, h bigint)
set @sqlstr = select +replace(replace(@c1str,,, union all select ) ,_,,)
insert into #tc1(i,n) exec ( @sqlstr )
set @sqlstr = select +replace(replace(@c2str,,, union all select ) ,_,,)
insert into #tc2(i,n) exec ( @sqlstr )
insert into @tgrp (c1, c2, id0 )
select c1,c2, id from @tsrc,(
select a.i i1, b.i i2 from #tc1 a, #tc2 b
) x
where x.i1 = c1 and x.i2 = c2
update @tgrp set h=power(2,id)
update #tc1 set h=x.h from (
select c1, sum(h) h from @tgrp group by c1
) x, #tc1 a
where x.c1 = a.i
update #tc2 set h=x.h from (
select c2, sum(h) h from @tgrp group by c2
) x, #tc2 a
where x.c2 = a.i
declare @max as int, @max2 as int, @i as int
select @max= count(*) from @tgrp
set @max2 = power(2, @max )-1
while @max2 >0
begin
if not exists( select * from #tc1 where dbo.uf_BitNumber( h & @max2) <> n )
and not exists( select * from #tc2 where dbo.uf_BitNumber( h & @max2) <> n )
begin
insert into @tout(c1,c2,h, id0 )
select c1,c2, @max2,id0 from @tgrp where @max2 & h >0
end
set @max2 = @max2 -1
end
select count(DISTINCT h) from @tout
select * from @tout order by h, id0
drop table #tc1
drop table #tc2
/*
这个方法使用bit 代表各种组合，返回所有的可能的组合.
限制-- (声母(这里是3) * 韵母(这里是3)) (即@tgrp记录数) < 62(整数位数)
如果需要突破这限制，可以用varchar/ varbinary/ 多个bigint 代替 bigint ,并替换以下方法：
Power(移位), Sum(|或，聚合函数) , &(与), uf_BitNumber(1的计数), @max2
如(varchar(4000))：
set @max2a = REPLICATE(1,@max)
set @max2b = REPLICATE(0,@max)
set @max2 = @max2a
移位: STUFF( @max2b, id,1,1)
或，聚合: bintochar(sum(chartobin()): varchar bintochar(dec(30)), dec(30) chartobin(varchar) ........
与: charAND(varchar,varchar)......
1的计数: len( REPLACE(charAND( h, @max2), 0,)
*/
/*
结果：(7组)
id id0 c1 c2 h
_______________________________________
24 0 p i 75
26 1 b ao 75
27 2 l an 75
25 4 p an 75
23 2 l an 86
20 4 p an 86
21 5 p ao 86
22 6 b i 86
16 0 p i 101
19 2 l an 101
17 5 p ao 101
18 8 b an 101
15 3 l i 166
12 4 p an 166
13 5 p ao 166
14 8 b an 166
8 0 p i 267
10 1 b ao 267
9 4 p an 267
11 7 l an 267
4 4 p an 278
5 5 p ao 278
6 6 b i 278
7 7 l an 278
0 0 p i 293
1 5 p ao 293
3 7 l an 293
2 8 b an 293
*/
有几项你没说明白:
1.这表你应该有个主键.比如ID之类的.否则你组合出来的结果抽那一条没什么意思.
2.随机组合容易.但不存在的组合,你得事先剔除.比如: g k h 和 ian 组合就不存在.是前台去除还是后台处理?
后台处理:
sekect distinct 列1,列2,列3 into NewTable from YourTable
先产生一个可能的组合表.
3.因为以上原因,传入的条件是否有所限制??
4.你的表二万多记录,是所有汉字都存在? 还是其它什么? 表述不够细.
给出真实的表结构和内容.和你如此查询的真实目的与用处. 这样大家也好看看.
