先不说你的功能如何,你的设计思路不太好,这样的表设计是很不合理的.
根据最基本的范式,字段的设计要求,原子性(第一范式),不可在分.
用一个字段的一部分和另外一个字段的一部分比较.没有索引,并且要处理.速度非常的慢.
速度随着数据量的增加成倍数增加(n)
(10000条记录以上)不可行.
表结构可以如下:
a表
id,afield
1,a
1,b
1,c
b表
id,bfield
1,a
1,d
根据最基本的范式,字段的设计要求,原子性(第一范式),不可在分.
用一个字段的一部分和另外一个字段的一部分比较.没有索引,并且要处理.速度非常的慢.
速度随着数据量的增加成倍数增加(n)
(10000条记录以上)不可行.
表结构可以如下:
a表
id,afield
1,a
1,b
1,c
b表
id,bfield
1,a
1,d
解决方案 »
- 在线等!!!! 我有一个.dts文件,我该怎么把它导入到数据库中呀,请知道
- SQL Server 2005 数据库的主数据文件、次要数据文件、日志文件的扩展名必须分别是 .mdf、.ndf 和 .ldf 吗?
- SQL Server 2000截断日志需要断开客户端连接么?
- select ccode,ccname form t1 where 1=2 中的 where 1=2 如何理解
- 虚拟机中的系统怎么访问本机数据库
- 有关sql语句的问题?。。我有点弧度了。。。
- sql语句如何查到 一个字段中 特定字符的出现次数呢?
- 请问软件数据库限制笔数是怎么实现的?
- 这样的情形如何用SQL SERVER2000建库??
- SQL Server 的DTS导出成DBF出现乱码!为什么?如何解决?
- Sql语句问题
- 怎么样返回两个存储过程返回的两个临时表 的和集 ,即 UNION
insert into #T1 values(1,'A+B+C')
insert into #T1 values(2,'B+C')
insert into #T1 values(3,'A+C')
insert into #T1 values(4,'C')
insert into #T1 values(5,'B+A')
gocreate table #T2(id int,vb varchar(30))
insert into #T2 values(1,'A+D+C')
insert into #T2 values(2,'A+W')
insert into #T2 values(3,'D+C')
insert into #T2 values(4,'D+F+C')
insert into #T2 values(5,'C+D')
goSELECT id,replace(va,' ','') va INTO #1 FROM #T1while exists (select 1 from #1 where charindex('+',va)>1)
begin
insert into #1 select id,left(va,charindex('+',va)-1) from #1 where charindex('+',va)>1
update #1 set va=right(va,len(va)-charindex('+',va)) where charindex('+',va)>1
endselect distinct a.* from #T2 a join #1 b on a.id=b.id
where charindex(b.va,a.vb)>0id va
----------- ------------------------------
1 A+B+C
2 B+C
3 A+C
4 C
5 B+A(5 row(s) affected)id vb
----------- ------------------------------
1 A+D+C
2 A+W
3 D+C
4 D+F+C
5 C+D(5 row(s) affected)id vb
----------- ------------------------------
1 A+D+C
3 D+C
4 D+F+C(3 row(s) affected)
from 表B a join(
select distinct bb.id from(
select a.id,afields=substring(afields,b.id,charindex('+',afields+'+',b.id)-b.id)
from 表A a,(
select id=a.id+b.id from(
select id=0 union all 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
)a,(
select id=0 union all select 10 union all select 20 union all select 30 union all select 40
union all select 50 union all select 60 union all select 70 union all select 80 union all select 90) b
) b where substring('+'+afields,b.id,1)='+'
)aa full join(
select a.id,bfields=substring(bfields,b.id,charindex('+',bfields+'+',b.id)-b.id)
from 表B a ,(
select id=a.id+b.id from(
select id=0 union all 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
)a,(
select id=0 union all select 10 union all select 20 union all select 30 union all select 40
union all select 50 union all select 60 union all select 70 union all select 80 union all select 90) b
) b where substring('+'+bfields,b.id,1)='+'
)bb on aa.id=bb.id and aa.afields=bb.bfields
where aa.id is not null
) b on a.id=b.id
declare @t1 table(id int,afields varchar(30))
insert into @t1
select 1,'A+B+C'
union all select 2,'B+C'
union all select 3,'A+C'
union all select 4,'C'
union all select 5,'B+A'declare @t2 table(id int,bfields varchar(30))
insert into @t2
select 1,'A+D+C'
union all select 2,'A+W'
union all select 3,'D+C'
union all select 4,'D+F+C'
union all select 5,'C+D'--查询
select a.*
from @t2 a join(
select distinct bb.id from(
select a.id,afields=substring(afields,b.id,charindex('+',afields+'+',b.id)-b.id)
from @t1 a,(
select id=a.id+b.id from(
select id=0 union all 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
)a,(
select id=0 union all select 10 union all select 20 union all select 30 union all select 40
union all select 50 union all select 60 union all select 70 union all select 80 union all select 90) b
) b where substring('+'+afields,b.id,1)='+'
)aa full join(
select a.id,bfields=substring(bfields,b.id,charindex('+',bfields+'+',b.id)-b.id)
from @t2 a ,(
select id=a.id+b.id from(
select id=0 union all 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
)a,(
select id=0 union all select 10 union all select 20 union all select 30 union all select 40
union all select 50 union all select 60 union all select 70 union all select 80 union all select 90) b
) b where substring('+'+bfields,b.id,1)='+'
)bb on aa.id=bb.id and aa.afields=bb.bfields
where aa.id is not null
) b on a.id=b.id/*--测试结果id bfields
----------- ------------------------------
1 A+D+C
3 D+C
4 D+F+C(所影响的行数为 3 行)
--*/