比如 P表中有两个字段 A,B
A B
2,3,4,5, 3,4,
3,5, 5,6,7,
像这样的情况,都能够匹配出来正确。
意思就是,只要A或者B中有一个(用,分隔开)存在于对方就正确,应该怎么来写SQL语句?
我刚开始这样
select * from P where (CHARINDEX(A,B) > 0) 这种只能匹配 A:5, B:5,6,7,
这种情况当A中有B中不存在的数字时候就不能匹配。
A B
2,3,4,5, 3,4,
3,5, 5,6,7,
像这样的情况,都能够匹配出来正确。
意思就是,只要A或者B中有一个(用,分隔开)存在于对方就正确,应该怎么来写SQL语句?
我刚开始这样
select * from P where (CHARINDEX(A,B) > 0) 这种只能匹配 A:5, B:5,6,7,
这种情况当A中有B中不存在的数字时候就不能匹配。
--梁哥作品
declare @string = 'book,net.job'; --将字符串分解成: col --- book net job --------------------------- declare @t table(col varchar(20)); insert @t select substring(@string,number,charindex(',',@string+',',number)-number) from master.dbo.spt_values where type='p' and substring(','+@string,number,1)=','; --与数据库数据比较 select * from tb as a where exists(select * from @t where charindex(','+col+',',','+a.string+',')>0);
INSERT TBTEST
SELECT '2,3,4,5,' , '3,4,' UNION
SELECT '3,5,' , '5,6,7,' --DROP TABLE TBTESTSELECT DIFFERENCE(','+A,','+B) FROM TBTEST
-----------
4
4(所影响的行数为 2 行)这样也许能行
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( A varchar(20),B varchar(20) )
go
insert tb SELECT
'2,3,4,5,' , '3,4,' UNION ALL SELECT
'3,5,' , '5,6,7,'
go
select * from tb where b in(
select b
from (
select substring(a,number,charindex(',',a+',',number)-number) as a ,b
from tb a ,master.dbo.spt_values
where type='p'
and substring(','+a,number,1)=',' and a is not null
) l where CHARINDEX(a,b)>0)
/*
A B
-------------------- --------------------
2,3,4,5, 3,4,
3,5, 5,6,7,
*/
--梁哥作品,版权没有,翻版不究
declare @string = 'book,net.job';
--将字符串分解成:
--- col
--- book
--- net
--- job
---------------------------
declare @t table(col varchar(20));
insert @t
select substring(@string,number,charindex(',',@string+',',number)-number)
from master.dbo.spt_values
where type='p'
and substring(','+@string,number,1)=','; --与数据库数据比较 select * from tb as a
where exists(select * from @t where charindex(','+col+',',','+a.string+',')>0);
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( A varchar(30),B varchar(20) )
go
insert tb SELECT
'2,3,4,5,' , '3,4,' UNION ALL SELECT
'3,5,' , '5,6,7,' UNION ALL SELECT
'5,7,0,', '2,1,8,' union all select
'1,2,3,', '3,0,9,'
go
select k.*
from tb k join (
select distinct b
from (
select substring(a,number,charindex(',',a+',',number)-number) as a ,b
from tb a ,master.dbo.spt_values
where type='p' AND number BETWEEN 1 AND LEN(A.A)
and substring(','+a.a,number,1)=',' and a is not null
) l where CHARINDEX(a,b)>0 ) l on k.B=l.b
/*
A B
------------------------------ --------------------
2,3,4,5, 3,4,
3,5, 5,6,7,
1,2,3, 3,0,9,
*/
create proc p_getCount(@s1 varchar(4000),@s2 varchar(4000))
as
begin
SET NOCOUNT ON
select top 1000 id=identity(int,1,1) into 序数表 from syscolumns a,syscolumns bcreate table tbl1(id int identity(1,1),keywords nvarchar(1000))
insert into tbl1(keywords) values(@s1)
insert into tbl1(keywords) values(@s2)select keyword=cast(substring(a.keywords,b.id,charindex(',',a.keywords+',',b.id)-b.id) as char(10)),
[count]=count(distinct a.id)
from tbl1 a,序数表 b
where b.id<=len(a.keywords)
and substring(','+a.keywords,b.id,1)=','
group by substring(a.keywords,b.id,charindex(',',a.keywords+',',b.id)-b.id)drop table 序数表,tbl1
end
go
exec p_getCount 'a,b,c,f','a,asd,dcc,f,h'
/*
keyword count
---------- -----------
a 2
asd 1
b 1
c 1
dcc 1
f 2
h 1*/
drop proc p_getCount
select k.*
from tb k join (
select distinct b
from (
select substring(a,number,charindex(',',a+',',number)-number) as a ,b
from tb a ,master.dbo.spt_values
where type='p' AND number BETWEEN 1 AND LEN(A.A)
and substring(','+a.a,number,1)=',' and a is not null
) l where CHARINDEX(a,b)>0 ) l on k.B=l.b用这种方法,到其中一个为动态的字符串怎么处理呢?比如B为动态读取过来的string类型的数据?