27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT c,d,aFROM
a, bWHEREcharindex(','+ltrim(b)+',',replace(replace(replace(d,'[',','),'',','),']',','))>
SELECT C,D,A FROM 表1,表2 WHERE 表2.D LIKE '[%'+B+' %]' OR 表2.D LIKE '[% '+B+']'
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-23 16:58:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([a] varchar(3),[b] int)
insert [a]
select '001',1 union all
select '002',2 union all
select '003',3
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([c] varchar(4),[d] varchar(20))
insert [b]
select '我','[1 2]' union all
select '们','[1 2]' union all
select '你们','[2 3]'
--------------开始查询--------------------------
select
c,d,a
from
a, b
where
charindex(','+ltrim(b)+',',replace(replace(replace(d,'[',','),' ',','),']',''))>0
----------------结果----------------------------
/* c d a
---- -------------------- ----
我 [1 2] 001
们 [1 2] 001
你们 [2 3] 002
(3 行受影响)
*/
create table t1(a varchar(10), b int)
insert into t1 values('001' , 1 )
insert into t1 values('002' , 2 )
insert into t1 values('003' , 3 )
create table t2(c varchar(10), d varchar(10))
insert into t2 values('我' , '[1 2]')
insert into t2 values('们' , '[1 2]')
insert into t2 values('你们', '[2 3]')
go
select distinct t2.c , t2.d , t1.a from t2 , t1 where charindex(' ' + cast(t1.b as varchar) + ' ' , ' ' + substring(t2.d,2,len(t2.d)-2) + ' ') > 0
/*
c d a
---------- ---------- ----------
们 [1 2] 001
们 [1 2] 002
你们 [2 3] 002
你们 [2 3] 003
我 [1 2] 001
我 [1 2] 002
(所影响的行数为 6 行)
*/
select distinct t2.c , t2.d , t1.a from t2 , t1 where ' ' + substring(t2.d,2,len(t2.d)-2) + ' ' like '% ' + cast(t1.b as varchar) + ' %'
/*
c d a
---------- ---------- ----------
们 [1 2] 001
们 [1 2] 002
你们 [2 3] 002
你们 [2 3] 003
我 [1 2] 001
我 [1 2] 002
(所影响的行数为 6 行)
*/
drop table t1 , t2
SELECT c,d,a FROM
a, b
WHERE charindex(','+ltrim(b)+',',replace(replace(replace(d,'[',','),' ',','),']',''))>0