求一sql语句,请大神们指点一下
有两个数据表:
CREATE TABLE [dbo].[Table_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StrA] [nvarchar](50) NULL,
[StrB] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO Table_A(StrA,StrB)VALUES('A','A')
INSERT INTO Table_A(StrA,StrB)VALUES('B','B')
INSERT INTO Table_A(StrA,StrB)VALUES('C','C')
INSERT INTO Table_A(StrA,StrB)VALUES('D','D')
CREATE TABLE [dbo].[Table_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A] [nvarchar](50) NULL,
[B] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO Table_B(A,B) VALUES('A,B','AB')
INSERT INTO Table_B(A,B) VALUES('A,B,C','ABC')
INSERT INTO Table_B(A,B) VALUES('A,B,D','ABD')
INSERT INTO Table_B(A,B) VALUES('A,B,C,D','ABCD')
我想要的结果是:
select * from Table_A where StrA in (select A from Table_B where B='ABCD')
--显示出Table_A的四条数据。请高手指点。
select * from table_A a ,table_B b where charindex(','+a.StrA+',',','+b.a+',')>0
CREATE TABLE [dbo].[Table_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StrA] [nvarchar](50) NULL,
[StrB] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO Table_A(StrA,StrB)VALUES('A','A')
INSERT INTO Table_A(StrA,StrB)VALUES('B','B')
INSERT INTO Table_A(StrA,StrB)VALUES('C','C')
INSERT INTO Table_A(StrA,StrB)VALUES('D','D')
CREATE TABLE [dbo].[Table_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A] [nvarchar](50) NULL,
[B] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO Table_B(A,B) VALUES('A,B','AB')
INSERT INTO Table_B(A,B) VALUES('A,B,C','ABC')
INSERT INTO Table_B(A,B) VALUES('A,B,D','ABD')
INSERT INTO Table_B(A,B) VALUES('A,B,C,D','ABCD')
--我想要的结果是:
select * from Table_A where CHARINDEX(StrA,(select A from Table_B where B='ABCD'))>0
--显示出Table_A的四条数据。请高手指点。
/*
IDStrAStrB
--------------------------
1AA
2BB
3CC
4DD
*/
CREATE TABLE [dbo].[Table_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StrA] [nvarchar](50) NULL,
[StrB] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO Table_A(StrA,StrB)VALUES('A','A')
INSERT INTO Table_A(StrA,StrB)VALUES('B','B')
INSERT INTO Table_A(StrA,StrB)VALUES('C','C')
INSERT INTO Table_A(StrA,StrB)VALUES('D','D')
CREATE TABLE [dbo].[Table_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A] [nvarchar](50) NULL,
[B] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO Table_B(A,B) VALUES('A,B','AB')
INSERT INTO Table_B(A,B) VALUES('A,B,C','ABC')
INSERT INTO Table_B(A,B) VALUES('A,B,D','ABD')
INSERT INTO Table_B(A,B) VALUES('A,B,C,D','ABCD')
select a.*
from Table_A a
inner join Table_B b
on b.B='ABCD' and
charindex(','+a.strB+',',','+b.a+',')>0
/*
IDStrAStrB
1AA
2BB
3CC
4DD
*/
CREATE TABLE [dbo].[#Table_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StrA] [nvarchar](50) NULL,
[StrB] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO #Table_A(StrA,StrB)VALUES('A','A')
INSERT INTO #Table_A(StrA,StrB)VALUES('B','B')
INSERT INTO #Table_A(StrA,StrB)VALUES('C','C')
INSERT INTO #Table_A(StrA,StrB)VALUES('D','D')
CREATE TABLE [dbo].[#Table_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A] [nvarchar](50) NULL,
[B] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO #Table_B(A,B) VALUES('A,B','AB')
INSERT INTO #Table_B(A,B) VALUES('A,B,C','ABC')
INSERT INTO #Table_B(A,B) VALUES('A,B,D','ABD')
INSERT INTO #Table_B(A,B) VALUES('A,B,C,D','ABCD')
select * from #Table_A where charindex(StrA,(select A from #Table_B where B='ABCD'))>0